> ## Documentation Index
> Fetch the complete documentation index at: https://docs.mindsdb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Join Tables On

## Description

The `JOIN` statement combines two or more tables based `ON` a specified column(s). It functions as a standard `JOIN` in SQL while offering the added capability of **combining data from multiple data sources**, allowing users to join data from one or more data sources seamlessly.

## Syntax

Here is the syntax:

```sql theme={null}
SELECT t1.column_name, t2.column_name, t3.column_name
FROM datasource1.table1 [AS] t1
JOIN datasource2.table2 [AS] t2
ON t1.column_name = t2.column_name
JOIN datasource3.table3 [AS] t3
ON t1.column_name = t3.column_name;
```

This query joins data from three different datasources - `datasource1`, `datasource2`, and `datasource3` - allowing users to execute federated queries accross multiple data sources.

<Tip>
  **Nested `JOINs`**

  MindsDB provides you with two categories of `JOINs`. One is [the `JOIN` statement which combines the data table with the model table](/mindsdb_sql/sql/api/join) in order to fetch bulk predictions. Another is the regular `JOIN` used throughout SQL, which requires the `ON` clause.

  You can nest these types of `JOINs` as follows:

  ```sql theme={null}
  SELECT * FROM (
      SELECT *
      FROM project_name.model_table AS m
      JOIN datasource_name.data_table AS d;
  ) AS t1
  JOIN (
      SELECT *
      FROM project_name.model_table AS m
      JOIN datasource_name.data_table AS d;
  ) AS t2
  ON t1.column_name = t2.column_name;
  ```
</Tip>

## Example 1

Let's use the following data to see how the different types of `JOINs` work.

The `pets` table that stores pets:

```sql theme={null}
+------+-------+
|pet_id|name   |
+------+-------+
|1     |Moon   |
|2     |Ripley |
|3     |Bonkers|
|4     |Star   |
|5     |Luna   |
|6     |Lake   |
+------+-------+
```

And the `owners` table that stores pets' owners:

```sql theme={null}
+--------+-------+------+
|owner_id|name   |pet_id|
+--------+-------+------+
|1       |Amy    |4     |
|2       |Bob    |1     |
|3       |Harry  |5     |
|4       |Julia  |2     |
|5       |Larry  |3     |
|6       |Henry  |0     |
+--------+-------+------+
```

### `JOIN` or `INNER JOIN`

The `JOIN` or `INNER JOIN` command joins the rows of the `owners` and `pets` tables wherever there is a match. For example, a pet named Lake does not have an owner, so it'll be left out.

```sql theme={null}
SELECT *
FROM files.owners o
[INNER] JOIN files.pets p
ON o.pet_id = p.pet_id;
```

On execution, we get:

```sql theme={null}
+--------+-------+------+------+-------+
|owner_id|name   |pet_id|pet_id|name   |
+--------+-------+------+------+-------+
|1       |Amy    |4     |4     |Star   |
|2       |Bob    |1     |1     |Moon   |
|3       |Harry  |5     |5     |Luna   |
|4       |Julia  |2     |2     |Ripley |
|5       |Larry  |3     |3     |Bonkers|
+--------+-------+------+------+-------+
```

As in standard SQL, you can use the `WHERE` clause to filter the output data.

```sql theme={null}
SELECT *
FROM files.owners o
[INNER] JOIN files.pets p
ON o.pet_id = p.pet_id
WHERE o.name = 'Amy'
OR o.name = 'Bob';
```

On execution, we get:

```sql theme={null}
+--------+-------+------+------+-------+
|owner_id|name   |pet_id|pet_id|name   |
+--------+-------+------+------+-------+
|1       |Amy    |4     |4     |Star   |
|2       |Bob    |1     |1     |Moon   |
+--------+-------+------+------+-------+
```

### `LEFT JOIN`

The `LEFT JOIN` command joins the rows of two tables such that all rows from the left table, even the ones with no match, show up. Here, the left table is the `owners` table.

```sql theme={null}
SELECT *
FROM files.owners o
LEFT JOIN files.pets p
ON o.pet_id = p.pet_id;
```

On execution, we get:

```sql theme={null}
+--------+-------+------+------+-------+
|owner_id|name   |pet_id|pet_id|name   |
+--------+-------+------+------+-------+
|1       |Amy    |4     |4     |Star   |
|2       |Bob    |1     |1     |Moon   |
|3       |Harry  |5     |5     |Luna   |
|4       |Julia  |2     |2     |Ripley |
|5       |Larry  |3     |3     |Bonkers|
|6       |Henry  |0     |[NULL]|[NULL] |
+--------+-------+------+------+-------+
```

### `RIGHT JOIN`

The `RIGHT JOIN` command joins the rows of two tables such that all rows from the right table, even the ones with no match, show up. Here, the right table is the `pets` table.

```sql theme={null}
SELECT *
FROM files.owners o
RIGHT JOIN files.pets p
ON o.pet_id = p.pet_id;
```

On execution, we get:

```sql theme={null}
+--------+-------+------+------+-------+
|owner_id|name   |pet_id|pet_id|name   |
+--------+-------+------+------+-------+
|2       |Bob    |1     |1     |Moon   |
|4       |Julia  |2     |2     |Ripley |
|5       |Larry  |3     |3     |Bonkers|
|1       |Amy    |4     |4     |Star   |
|3       |Harry  |5     |5     |Luna   |
|[NULL]  |[NULL] |[NULL]|6     |Lake   |
+--------+-------+------+------+-------+
```

### `FULL JOIN` or `FULL OUTER JOIN`

The `FULL [OUTER] JOIN` command joins the rows of two tables such that all rows from both tables, even the ones with no match, show up.

```sql theme={null}
SELECT *
FROM files.owners o
FULL [OUTER] JOIN files.pets p
ON o.pet_id = p.pet_id;
```

On execution, we get:

```sql theme={null}
+--------+------+------+------+-------+---------+
|owner_id|name  |pet_id|pet_id|name   |animal_id|
+--------+------+------+------+-------+---------+
|1       |Amy   |4     |4     |Star   |2        |
|2       |Bob   |1     |1     |Moon   |1        |
|3       |Harry |5     |5     |Luna   |2        |
|4       |Julia |2     |2     |Ripley |1        |
|5       |Larry |3     |3     |Bonkers|3        |
|6       |Henry |0     |[NULL]|[NULL] |[NULL]   |
|[NULL]  |[NULL]|[NULL]|6     |Lake   |4        |
+--------+------+------+------+-------+---------+
```

## Example 2

More than two tables can be joined subsequently.

Let's use another table called `animals`:

```sql theme={null}
+---------+-------+
|animal_id|name   |
+---------+-------+
|1        |Dog    |
|2        |Cat    |
|3        |Hamster|
|4        |Fish   |
+---------+-------+
```

Now we can join all three tables.

```sql theme={null}
SELECT *
FROM files.owners o
RIGHT JOIN files.pets p ON o.pet_id = p.pet_id
JOIN files.animals a ON p.animal_id = a.animal_id;
```

On execution, we get:

```sql theme={null}
+--------+-------+------+------+-------+---------+---------+-------+
|owner_id|name   |pet_id|pet_id|name   |animal_id|animal_id|name   |
+--------+-------+------+------+-------+---------+---------+-------+
|2       |Bob    |1     |1     |Moon   |1        |1        |Dog    |
|4       |Julia  |2     |2     |Ripley |1        |1        |Dog    |
|5       |Larry  |3     |3     |Bonkers|3        |3        |Hamster|
|1       |Amy    |4     |4     |Star   |2        |2        |Cat    |
|3       |Harry  |5     |5     |Luna   |2        |2        |Cat    |
|[NULL]  |[NULL] |[NULL]|6     |Lake   |4        |4        |Fish   |
+--------+-------+------+------+-------+---------+---------+-------+
```