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 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 books
INNER JOIN authors
  ON books.author_id = authors.id;

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.

The UNION clause is used to combine results that appear from multiple SELECT statements and filter duplicates.

SELECT name
FROM first_names
UNION
SELECT name
FROM 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: