SQL Joins

INNER, LEFT, RIGHT, FULL, CROSS and self joins: which rows each keeps, where NULLs appear, and the traps that change the row count.

Overview

A join combines rows from two tables into one row whenever a condition, written after ON, is true. Nearly every real query touches more than one table, so interviewers use joins to see whether you can predict exactly which rows come back.

The two questions to ask about every join are: which rows survive when there is no match, and how many rows come back when there are several matches.

The sample tables

The data has four employees and three departments. Fay has no department, and Legal has no employees. Those two rows are what make the join types differ. All the SQL on this page runs on PostgreSQL.

What each join keeps

Every outer join starts as an inner join. Then it adds back the rows that found no partner, with NULL in the other table's columns.

  • INNER JOIN — only pairs where ON is true — 3: Ana, Ben, Dee
  • LEFT JOIN — every left row, matched or not — 4: adds Fay with a NULL department
  • RIGHT JOIN — every right row, matched or not — 4: adds Legal with a NULL employee
  • FULL JOIN — every row from both sides — 5: adds Fay and Legal
  • CROSS JOIN — every pair, no condition — 12: 4 times 3

A RIGHT JOIN is a LEFT JOIN with the tables swapped, and most people write the left form.

  • Ana — Eng
  • Ben — Eng
  • Dee — Sales
  • Ana — Eng
  • Ben — Eng
  • Dee — Sales
  • Fay — NULL
  • NULL — Legal

Fay never matches, even in the inner join, because NULL = 10 is not true. A NULL key never equals anything, including another NULL.

INNER JOIN, LEFT JOIN and FULL JOIN need a condition (ON, USING or NATURAL); in PostgreSQL, JOIN with no condition is a syntax error. Only CROSS JOIN has none. USING (col) is shorthand for equal columns with the same name, and it shows the column once.

Self join

A self join is a table joined to itself under two aliases. It is how you compare a row with another row of the same table, such as an employee with their manager.

  • Fay — 160000 — Ana — 150000

Ana has no manager, so the inner join drops her. Use LEFT JOIN if the question wants everyone, with a blank manager for the top of the tree.

ON versus WHERE in an outer join

In an inner join it makes no difference whether a condition sits in ON or in WHERE. In an outer join it does. The PostgreSQL manual puts it simply: a condition in ON is applied before the join, and a condition in WHERE after it.

  • ON ... AND d.city = 'NYC' — every employee stays; non-NYC departments become NULL — 4
  • WHERE d.city = 'NYC' — NULL city is not 'NYC', so padded rows are removed — 2
  • Ana — Eng
  • Ben — Eng
  • Dee — NULL
  • Fay — NULL
  • Ana — Eng
  • Ben — Eng

The second query is really an inner join. A WHERE test on the right table of a LEFT JOIN quietly undoes it, unless the test is IS NULL.

Finding rows with no match

'Departments with no employees' and 'customers who never ordered' are the same question: an anti-join. There are three common ways to write it, and one of them has a trap.

  • LEFT JOIN ... WHERE e.id IS NULL — Legal — test a column that is never NULL in a real match, such as the key
  • NOT EXISTS (SELECT 1 ...) — Legal — clear and NULL-safe; the usual best answer
  • NOT IN (SELECT dept_id ...) — no rows — Fay's NULL dept_id makes every test unknown
  • Legal

This returns no rows. Legal's id 30 is not equal to 10 or 20, but it is not known to differ from NULL, so NOT IN returns unknown and WHERE drops the row.

Joins multiply rows

When one left row matches k right rows, it appears k times. That is correct for listing, but it breaks sums of left-table columns. Eng has two employees, so its budget is counted twice.

  • 1200
  • 700

The same care applies to counts after a LEFT JOIN. COUNT(*) counts the padded row, so an empty department shows 1. COUNT(e.id) skips the NULL and shows 0.

  • Eng — 2
  • Sales — 1
  • Legal — 0

How the database runs a join

You write what to join; the planner chooses how. PostgreSQL has three methods, and EXPLAIN shows which one it picked.

  • Nested loop — for each left row, scan the right side — one side is small, or the right side has an index on the join key
  • Hash join — load the right side into a hash table, then probe it with each left row — large inputs joined on equality
  • Merge join — sort both sides on the key, then walk them in step — both sides are already sorted, for example by an index; equality joins

Hash and merge joins work only for equality conditions. A join on a range, such as a.t BETWEEN b.start AND b.stop, leaves the planner a nested loop. An index on the foreign key column (here employees.dept_id) is usually what makes a join fast.

What you will be able to do

  • Say which rows each join type keeps, and predict the row count.
  • Explain why a WHERE test can turn a LEFT JOIN into an inner join.
  • Write an anti-join three ways and explain the NOT IN trap.
  • Spot fan-out before it inflates a sum or a count.

When to use it

  • Combining rows from related tables through a key (INNER JOIN).
  • Keeping every row of one table even when it has no match (LEFT JOIN).
  • Keeping unmatched rows from both sides, for example to compare two lists (FULL JOIN).
  • Comparing rows inside one table, such as employee and manager (self join).
  • Building every combination, such as all users times all dates (CROSS JOIN).
  • Finding rows with no partner (anti-join with NOT EXISTS or LEFT JOIN ... IS NULL).

Interview questions

  • What is the difference between a condition in ON and in WHERE for a LEFT JOIN?
  • How do you find records that exist in one table but not in another?
  • What join algorithms does a database use, and when does it pick each?

Sign up free to read the full topic

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio