​
Description

The JOIN clause combines rows from the database table and the model table on a column defined in its implementation. It is used to make batch predictions, as shown in the examples.

​
Syntax

Here is the syntax that lets you join multiple data tables with multiple models to get all predictions at once. 
SELECT d1.column_name,
       d2.column_name,
       m1.column_name,
       m2.column_name,
       ...
FROM integration_name.table_name_1 [AS] d1
[JOIN integration_name.table_name_2 [AS] d2 ON ...]
[JOIN ...]
JOIN project_name.model_name_1 [AS] m1
[JOIN project_name.model_name_2 [AS] m2]
[JOIN ...];
Where:
NameDescription
integration_name.table_name_1Name of the data source table used as input for making predictions.
integration_name.table_name_2Optionally, you can join arbitrary number of data source tables.
project_name.model_name_1Name of the model table used to make predictions.
project_name.model_name_2Optionally, you can join arbitrary number of models.

​
Example 1

Let’s join the home_rentals table with the home_rentals_model model using this statement: 
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 20;
On execution, we get: 
+------------+-----------------+-----------------+---------------------+------+----------+----------------+
| 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 2

Let’s query a time series model using this statement: 
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;
On execution, we get: 
+----------+------------------+
|date      |forecast          |
+----------+------------------+
|2019-12-31|517506.31349071994|
|2019-12-31|627822.6592658638 |
|2019-12-31|953426.9545788583 |
|2019-12-31|767252.4205039773 |
+----------+------------------+
Follow this doc page to see examples of joining multiple data table with multiple models.