JOIN
The optional JOIN
clause can be placed in the FROM
part of a SQL SELECT
statement (read more about the FROM
clause). In this article we'll explain how it works.
You need a JOIN
clause whenever you want your SELECT
statement to retrieve rows from multiple tables at the same time - it tells the database exactly how it should match those tables together.
There are several different types of JOIN
clause, common ones are
RIGHT JOIN
INNER JOIN
OUTER JOIN
CROSS JOIN
In this article, we'll look at some examples to see how they work.
In this article the code snippets are written in the Google BigQuery Standard SQL syntax.
A basic LEFT JOIN
statement looks like
This query will return
- all columns from
table_1
andtable_2
- all rows from
table_1
- all rows from
table_2
wherecolumn_2
matches the columncolumn_1
fromtable_1
This is why it's called a LEFT
join - the table on the left of the join clause keeps all of its rows, even if there isn't a matching row in the table on the right.
If there is no matching row in the table on the right, the columns from the right will be filled with NULL
s.
strings | numbers | strings_1 | numbers_1 |
---|---|---|---|
a | 1 | a | 4 |
b | 2 | b | 5 |
c | 3 | null | null |
RIGHT JOIN
A basic RIGHT JOIN
statement looks like
This query will return
- all columns from
table_1
andtable_2
- all rows from
table_2
- all rows from
table_1
wherecolumn_1
matches the columncolumn_2
fromtable_2
This is why it's called a RIGHT
join - the table on the right of the join clause keeps all of its rows, even if there isn't a matching row in the table on the left.
If there is no matching row in the table on the left, the columns from the left will be filled with NULL
s.
strings | numbers | strings_1 | numbers_1 |
---|---|---|---|
a | 1 | a | 4 |
b | 2 | b | 5 |
null | null | c | 6 |
INNER JOIN
A basic INNER JOIN
statement looks like
This query will return
- all columns from
table_1
andtable_2
- all rows from both tables where the values in
column_1
andcolumn_2
both match and exist
This is why it's called an INNER
join - only the rows which match from both tables will be included in the result.
Note in the following that the values 'c' and 'd' don't appear in the output, as those values don't exist in both tables.
strings | numbers | strings_1 | numbers_1 |
---|---|---|---|
a | 1 | a | 4 |
b | 2 | b | 5 |
FULL OUTER JOIN
A basic FULL OUTER JOIN
statement looks like
This query will return
- all columns from
table_1
andtable_2
- all rows from both tables
This is why it's called an OUTER
join - all rows will be included in the result, even if they don't exist in one of the tables.
If there is no matching row in one of the tables, the missing columns will be filled with NULL
s.
Note in the following that the values 'c' and 'd' appear in the output, even though they only exist in one table each.
strings | numbers | strings_1 | numbers_1 |
---|---|---|---|
a | 1 | a | 4 |
b | 2 | b | 5 |
c | 3 | null | null |
null | null | d | 6 |
CROSS JOIN
A basic CROSS JOIN
statement looks like
This query will return
- all columns from
table_1
andtable_2
- all possible combinations of rows from both tables
This is why it's called a CROSS
join - the number of rows returned is the product of the number of rows of the two tables. You should be careful when using CROSS JOIN
s as they can return really massive tables, and are often not what you require.
Note in the following that the number of rows in the output is 2 x 3 = 6.
strings | numbers | strings_1 | numbers_1 |
---|---|---|---|
a | 1 | a | 4 |
a | 1 | b | 5 |
a | 1 | c | 6 |
b | 2 | a | 4 |
b | 2 | b | 5 |
b | 2 | c | 6 |