4 SQL Joins

How joins connect tables

A join combines rows from separate tables when they satisfy a specified condition. This is useful when related information is stored in different places, such as employee names in one table and department names in another.

Suppose employees.department_id refers to departments.department_id. The employee column is a foreign key, and the department identifier can serve as a primary key. Matching these columns connects each employee to the relevant department.

The ON clause states how rows match. In SQL, JOIN by itself means INNER JOIN. Table aliases can make a query more concise:

The key idea is to make the relationship between tables explicit in the join condition.

Choose which rows to keep

The join type determines what happens when a row has no match. Choose the type according to which rows the result must retain.

• INNER JOIN returns only row pairs that match. Employees without a matching department and departments without employees are left out.

• LEFT JOIN keeps every row from the left table. When no right-table row matches, its columns appear as NULL.

• A right join does the reverse: it preserves every row from the right table and fills left-table columns with NULL when unmatched. Often, reversing the table order and using a left join is clearer.

• FULL OUTER JOIN keeps all rows from both tables. Matching rows are combined; unmatched rows have NULL values for columns from the other table.

• CROSS JOIN produces every possible pair. If one table has $N$ rows and another has $M$ rows, the result has $N \times M$ rows.

For example, a left join can reveal employees without a matching department by filtering for a missing department identifier:

A cross join is appropriate when every combination is intended, such as pairing each product with each available size. Otherwise, the number of resulting rows can grow unexpectedly.

Takeaway: An inner join keeps matches only; outer joins preserve unmatched rows from one or both sides; a cross join produces all combinations.

Follow relationships across tables

Joins can be chained to follow relationships across several tables. For example, an order can refer to a customer, and each order line can refer to a product:

Each ON clause describes one relationship in the chain. Because an order can have multiple order lines, the order details appear on multiple result rows—one for each matching line. This is expected: a join combines matching rows and does not necessarily produce one result row per original record.

Avoid common join pitfalls

A clear join starts with an accurate match condition and a deliberate choice of which unmatched rows to preserve.

• Use explicit join conditions. A missing or incorrect condition can create unintended combinations; use CROSS JOIN only when all combinations are intended.

• Be careful when filtering after an outer join. A WHERE condition on the right table can remove rows whose right-side columns are NULL, making a left join behave like an inner join.

• To keep unmatched left-table rows while restricting which right-table rows may match, put the restriction in ON rather than WHERE.

• Expect repeated values in one-to-many relationships. For example, a department's details repeat for each matching employee.

• Select named columns instead of using SELECT * when practical. This makes results easier to interpret and avoids unnecessary or duplicate-named columns.

When both tables share the matching column name, USING (column_name) can replace an ON condition and returns one copy of that column. ON is often clearer when key names differ.

Takeaway: Check the join condition, the unmatched-row behavior, and the effect of filters before interpreting the result.