JOIN clause combines rows from two or more tables by joining them together with other results based on common column values specified using an
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
The following only returns rows from the
authors tables on the condition that
authors.id columns match:
SELECT *FROM booksINNER JOIN authorsON books.author_id = authors.id;
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 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: