Description
TheJOIN
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: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:Example 1
Let’s use the following data to see how the different types ofJOINs
work.
The pets
table that stores pets:
owners
table that stores pets’ owners:
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.
WHERE
clause to filter the output data.
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.
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.
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.
Example 2
More than two tables can be joined subsequently. Let’s use another table calledanimals
: