Joins

When one table is not enough

In a relational database, related data is usually stored in different tables: a user in one table, their orders in another, and order items in yet another. A JOIN lets you combine those parts into one result by using the relationship between rows.

The main question in any join is which rows should be considered related and what should happen to rows for which no match exists. Different JOIN types mainly differ in how they answer that second question.

What this section contains

• JOIN basics. We will understand the basic idea of combining tables and the ON condition.
• INNER JOIN. We will keep only rows that have a match on both sides.
• LEFT JOIN. We will keep every row from the left table even when there is no matching row on the right.
• RIGHT JOIN. We will look at the symmetric form that preserves every row from the right table.
• FULL JOIN. We will combine matching rows while preserving unmatched rows from both tables.
• CROSS JOIN. We will examine the Cartesian product — every possible combination of rows from two sets.
• SELF JOIN. We will learn how to join a table to itself using different aliases.
• Multiple JOINs. We will combine data from three or more tables by adding relationships step by step.

At first, it is important to read each join literally: which table is on the left, which is on the right, how rows are matched, and which rows should survive when no matching row exists.