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

Copy to clipboard
Copy to clipboard

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.

CROSS JOIN

A CROSS JOIN clause combines each row from one table with each row from another in the result set. This result is also known as a Cartesian product. The following query returns every combination of shirt_color and pants_color:

SELECT shirts.shirt_color,
   pants.pants_color
FROM shirts
CROSS JOIN pants;

Copy to clipboard
Copy to clipboard

For example, here’s the shirts table:

And the pants table:

The result of the query would contain every combination of the two tables:

UNION

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;

Copy to clipboard
Copy to clipboard

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: