---
title: Join Models with Tables
sidebarTitle: Join Models with Tables
---

## 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.

```sql
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 ...]
[ON d1.input_data = m1.expected_argument];
```

Where:

| Name                                | Description                                                                   |
| ----------------------------------- | ----------------------------------------------------------------------------- |
| `integration_name.table_name_1`     | Name of the data source table used as input for making predictions.           |
| `integration_name.table_name_2`     | Optionally, you can join arbitrary number of data source tables.              |
| `project_name.model_name_1`         | Name of the model table used to make predictions.                             |
| `project_name.model_name_2`         | Optionally, you can join arbitrary number of models.                          |

### Mapping input data to model arguments

If the input data contains a column named `question` and the model requires an argument named `input`, you can map these columns, as explained below.

We have a model that expects to receive `input`:

```sql
CREATE MODEL model_name
PREDICT answer
USING
       engine = 'openai',
       prompt_template = 'provide answers to an input from a user: {{input}}';
```

We have an input data table that has the following columns:

```sql
+----+-------------------------------------------+
| id | question                                  |
+----+-------------------------------------------+
| 1  | How many planets are in the solar system? |
| 2  | How many stars are in the solar system?   |
+----+-------------------------------------------+
```

Now if you want to get answers to these questions using the model, you need to join the input data table with the model and map the `question` column onto the `input` argument.

```sql
SELECT *
FROM input_table AS d
JOIN model_name AS m
ON d.question = m.input;
```

## Example 1

Let's join the `home_rentals` table with the `home_rentals_model` model using this statement:

```sql
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:

```sql
+------------+-----------------+-----------------+---------------------+------+----------+----------------+
| 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:

```sql
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:

```sql
+----------+------------------+
|date      |forecast          |
+----------+------------------+
|2019-12-31|517506.31349071994|
|2019-12-31|627822.6592658638 |
|2019-12-31|953426.9545788583 |
|2019-12-31|767252.4205039773 |
+----------+------------------+
```

<Tip>
Follow [this doc page](/generative-ai-tables#working-with-generative-ai-tables) to see examples of joining multiple data table with multiple models.
</Tip>
