Skip to content

JOIN Statement

Description

The JOIN clause is used to combine rows from the database table and the model table on a related column. This can be very helpful to get bulk predictions. The basic syntax for joining from the data table and model is:

Syntax

SELECT t.[column_name], p.[column_name] ...
FROM [integration_name].[table] AS t
JOIN mindsdb.[predictor_name] AS p

On execution:

+-----------------+-----------------+
| t.[column_name] | p.[column_name] |
+-----------------+-----------------+
| t.[value]       | p.value         |
+-----------------+-----------------+

Where:

Description
[integration_name].[table] Name of the table te be used as input for the prediction
mindsdb.[predictor_name] Name of the model to be used to predict
p.value prediction value

Example

The following SQL statement joins the home_rentals data with the home_rentals_model predicted price:

SELECT t.rental_price as real_price, 
       m.rental_price as predicted_price,
       t.number_of_rooms,  t.number_of_bathrooms, t.sqft, t.location, t.days_on_market 
FROM example_db.demo_data.home_rentals as t 
JOIN mindsdb.home_rentals_model as m 
LIMIT 100
+------------+-----------------+-----------------+---------------------+------+----------+----------------+
| real_price | predicted_price | number_of_rooms | number_of_bathrooms | sqft | location | days_on_market |
+------------+-----------------+-----------------+---------------------+------+----------+----------------+
| 3901       | 3886            | 2               | 1                   | 917  | great    | 13             |
| 2042       | 2007            | 0               | 1                   | 194  | great    | 10             |
| 1871       | 1865            | 1               | 1                   | 543  | poor     | 18             |
| 3026       | 3020            | 2               | 1                   | 503  | good     | 10             |
| 4774       | 4748            | 3               | 2                   | 1066 | good     | 13             |
| 4382       | 4388            | 3               | 2                   | 816  | poor     | 25             |
| 2269       | 2272            | 0               | 1                   | 461  | great    | 6              |
| 2284       | 2272            | 1               | 1                   | 333  | great    | 6              |
| 5420       | 5437            | 3               | 2                   | 1124 | great    | 9              |
| 5016       | 4998            | 3               | 2                   | 1204 | good     | 7              |
| 1421       | 1427            | 0               | 1                   | 538  | poor     | 43             |
| 3476       | 3466            | 2               | 1                   | 890  | good     | 6              |
| 5271       | 5255            | 3               | 2                   | 975  | great    | 6              |
| 3001       | 2993            | 2               | 1                   | 564  | good     | 13             |
| 4682       | 4692            | 3               | 2                   | 953  | good     | 10             |
| 1783       | 1738            | 1               | 1                   | 493  | poor     | 24             |
| 1548       | 1543            | 1               | 1                   | 601  | poor     | 47             |
| 1492       | 1491            | 0               | 1                   | 191  | good     | 12             |
| 2431       | 2419            | 0               | 1                   | 511  | great    | 1              |
| 4237       | 4257            | 3               | 2                   | 916  | poor     | 36             |
+------------+-----------------+-----------------+---------------------+------+----------+----------------+

Example Time Series

Having a time series predictor trained via:

CREATE PREDICTOR mindsdb.house_sales_model
FROM example_db
  (SELECT * FROM demo_data.house_sales)
PREDICT MA
ORDER BY saledate
GROUP BY bedrooms, type
-- as the target is quarterly, we will look back two years to forecast the next one
WINDOW 8
HORIZON 4;  

You can query it and get the forecast predictions like:

SELECT m.saledate as date,
    m.ma as forecast
FROM mindsdb.house_sales_model as m 
JOIN example_db.demo_data.house_sales as t
WHERE t.saledate > LATEST AND t.type = 'house'
LIMIT 4;