ITInsightHub

SQL joins cheat sheet

A join combines rows from two tables based on a matching condition, almost always a foreign key. The four types differ in what happens to rows that have no match: INNER keeps only matched rows, LEFT keeps all rows from the left table, RIGHT all from the right, and FULL all from both.

The examples join an employees table to a departments table on employees.department_id = departments.id. Left and right are symmetric — A LEFT JOIN B is B RIGHT JOIN A — which is why LEFT JOIN is the one you will use 90% of the time.

Published

The four join types

INNER JOIN — only rows that match in both tables
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
LEFT JOIN — every employee, even those with no department
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- d.name will be NULL for employees with no matching department
What each type returns
JoinKeeps
INNER JOINonly rows where the ON condition matches in both tables
LEFT JOINall left-table rows, plus matched right rows (NULLs where no match)
RIGHT JOINall right-table rows, plus matched left rows (NULLs where no match)
FULL OUTER JOINall rows from both tables (NULLs on either side where no match)
CROSS JOINthe Cartesian product — every row paired with every row (no ON clause)

Filtering the joined result

Filter in ON vs WHERE — the difference matters for outer joins
-- WHERE filters AFTER the join: this turns a LEFT JOIN into an INNER JOIN
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Engineering';

-- Filter in the ON clause to keep unmatched left rows
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id AND d.name = 'Engineering';
Putting a right-table condition in WHERE eliminates the NULL rows an outer join was created to keep, silently converting LEFT JOIN into INNER JOIN. If you want to keep all left rows and only restrict which right rows join, the condition belongs in the ON clause.

Self-joins and combining result sets

A self-join — an employee and their manager (same table, twice)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

UNION and friends — stacking query results vertically

UNION
combines two SELECTs and removes duplicate rows
UNION ALL
combines two SELECTs and keeps every row (faster — no dedupe)
INTERSECT
rows present in both queries
EXCEPT
rows in the first query but not the second (MINUS in Oracle)

Subqueries and EXISTS

Three subquery patterns
-- Scalar subquery (returns one value)
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.employee_id = e.id) AS orders
FROM employees e;

-- IN with a subquery
SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name = 'Engineering');

-- EXISTS: does any row match?
SELECT name FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.id);
EXISTS (SELECT 1 ...) stops as soon as it finds one matching row, so it is usually the fastest way to express "are there any related rows?". Use IN when comparing against a list of values, and EXISTS when the subquery only needs to prove a match exists.

References

FAQ

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table and matches from the right, filling unmatched columns with NULL. A LEFT JOIN includes rows that an INNER JOIN drops, which is why you reach for it whenever you want 'all of A, with B where available'.

What is the difference between ON and WHERE in a join?

ON defines which rows pair up during the join; WHERE filters the result afterwards. For an INNER JOIN they are often interchangeable, but for a LEFT/RIGHT/FULL join the difference is critical: a condition in WHERE drops unmatched rows, while the same condition in ON keeps them.

What is the difference between UNION and UNION ALL?

UNION combines two result sets and removes duplicate rows; UNION ALL keeps every row. UNION ALL is faster because it skips the deduplication pass, so use it unless you specifically need duplicates removed. Both require the two SELECTs to have the same number of columns.

What is a self-join?

A self-join joins a table to itself, using aliases to treat the same table as two separate ones. The classic case is an employees table with a manager_id column: join employees to employees to pair each person with their manager.