Joins

The JOIN
clause combines rows from two or more tables by joining them together with other results based on common column values specified using an ON
condition.
In order to efficiently store data, we often spread related information across multiple tables. Connecting or joining these tables to find interesting data is a common task that a data analyst, data scientist, or data engineer will often encounter.
INNER JOIN
INNER JOIN
is the default JOIN
and it will only return results matching the condition specified by ON
.
The following only returns rows from the books
and authors
tables on the condition that books.author_id
and authors.id
columns match:
SELECT *FROM booksINNER JOIN authorsON books.author_id = authors.id;
OUTER JOIN
An OUTER JOIN
will combine rows from different tables even if the join condition is not met. These commands include the following:
LEFT JOIN
, which combines matches with all rows from the left-side table.RIGHT JOIN
, which combines matches with all rows from the right-side table.FULL OUTER JOIN
, which combines matches with all rows from the left- and right-side tables.
UNION
The UNION
clause is used to combine results that appear from multiple SELECT
statements and filter duplicates.
SELECT nameFROM first_namesUNIONSELECT nameFROM last_names;
For example, given a first_names
table with a column name containing rows of data “James” and “Hermione”, and a last_names
table with a column name containing rows of data “James”, “Hermione” and “Cassidy”, the result of this query would contain three names:
name |
---|
Cassidy |
James |
Hermione |
All contributors
- haroon26atcc2 total contributions
- BrandonDusch580 total contributions
- christian.dinh2481 total contributions
- Anonymous contributorAnonymous contributor3077 total contributions
- haroon26atcc
- BrandonDusch
- christian.dinh
- Anonymous contributor
Looking to contribute?
- Learn more about how to get involved.
- Edit this page on GitHub to fix an error or make an improvement.
- Submit feedback to let us know how we can improve Docs.