docs/mindsdb_sql/sql/api/join-on.mdx
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.
Here is the syntax:
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.
Nested JOINs
MindsDB provides you with two categories of JOINs. One is the JOIN statement which combines the data table with the model table 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:
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;
Let's use the following data to see how the different types of JOINs work.
The pets table that stores pets:
+------+-------+
|pet_id|name |
+------+-------+
|1 |Moon |
|2 |Ripley |
|3 |Bonkers|
|4 |Star |
|5 |Luna |
|6 |Lake |
+------+-------+
And the owners table that stores pets' owners:
+--------+-------+------+
|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 JOINThe 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.
SELECT *
FROM files.owners o
[INNER] JOIN files.pets p
ON o.pet_id = p.pet_id;
On execution, we get:
+--------+-------+------+------+-------+
|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.
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:
+--------+-------+------+------+-------+
|owner_id|name |pet_id|pet_id|name |
+--------+-------+------+------+-------+
|1 |Amy |4 |4 |Star |
|2 |Bob |1 |1 |Moon |
+--------+-------+------+------+-------+
LEFT JOINThe 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.
SELECT *
FROM files.owners o
LEFT JOIN files.pets p
ON o.pet_id = p.pet_id;
On execution, we get:
+--------+-------+------+------+-------+
|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 JOINThe 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.
SELECT *
FROM files.owners o
RIGHT JOIN files.pets p
ON o.pet_id = p.pet_id;
On execution, we get:
+--------+-------+------+------+-------+
|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 JOINThe 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.
SELECT *
FROM files.owners o
FULL [OUTER] JOIN files.pets p
ON o.pet_id = p.pet_id;
On execution, we get:
+--------+------+------+------+-------+---------+
|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 |
+--------+------+------+------+-------+---------+
More than two tables can be joined subsequently.
Let's use another table called animals:
+---------+-------+
|animal_id|name |
+---------+-------+
|1 |Dog |
|2 |Cat |
|3 |Hamster|
|4 |Fish |
+---------+-------+
Now we can join all three tables.
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:
+--------+-------+------+------+-------+---------+---------+-------+
|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 |
+--------+-------+------+------+-------+---------+---------+-------+