SQL joins: what each one returns

Which rows survive

Take two tables, users and orders, where some users have no orders and some orders have a user_id that matches nothing. Each join type answers differently about those unmatched rows, and the unmatched columns come back as NULL.

Two practical notes from the entries. JOIN with no prefix is INNER JOIN - the word is optional, which is why so much code has an implicit inner join in it. And RIGHT JOIN is rarely worth using: it is the mirror of LEFT JOIN, and most teams rewrite it as a LEFT by swapping the table order, so that every join in the codebase reads in the same direction.

SELECT columns
FROM table1
INNER JOIN table2 ON table1.col = table2.col;

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.col = table2.col;

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.col = table2.col;

The ON versus WHERE trap

This is the single most expensive mistake in this section, because it produces a plausible-looking result rather than an error.

A LEFT JOIN keeps unmatched left rows with NULLs in the right-hand columns. If you then filter on a right-hand column in WHERE, every one of those NULL rows fails the test and disappears - and you have silently written an inner join with extra words. The fix is to put the right-table condition in the ON clause, where it restricts what counts as a match instead of filtering the result.

The rule of thumb: a condition on the left table belongs in WHERE; a condition on the right table of an outer join belongs in ON. The sheet carries this as its own entry because the two versions of the query differ by one keyword and by every unmatched row.

SELECT columns
FROM table1
LEFT JOIN table2 ON t1.col = t2.col AND t2.filter = value;

NULL is not a value, and does not compare like one

Outer joins manufacture NULLs, which is why the NULL rules bite hardest in join queries. NULL means unknown, so any comparison involving it evaluates to unknown, and WHERE treats unknown as false. That includes =, <>, <, >, IN and NOT IN.

The practical consequences: WHERE col = NULL matches nothing, ever. WHERE col <> 'x' silently drops rows where col is NULL. And NOT IN with a subquery that can return a NULL returns no rows at all - the most surprising of the three, and the reason NOT EXISTS is usually preferred.

The only operators that work are IS NULL, IS NOT NULL and IS DISTINCT FROM.

-- WRONG:
WHERE column = NULL
WHERE column <> NULL

-- CORRECT:
WHERE column IS NULL
WHERE column IS NOT NULL

The joins that are mostly about readability

Four entries in the section are less about semantics than about keeping a query legible as it grows.

USING (col) is shorthand for ON a.col = b.col when the column has the same name on both sides, and it collapses the duplicate column in the output. It is not supported in SQL Server, which is worth knowing before you standardise on it.

NATURAL JOIN goes further and joins on every shared column name - and the sheet recommends against it in production, for a reason worth repeating: adding a column called status to both tables silently changes what the query joins on. An explicit ON or USING cannot do that to you.

A self join is a table joined to itself with two aliases, which is how you query hierarchies - an org chart, threaded comments, a category tree. And multiple joins chain as far as you need, with one caveat from the entry: query planners get noticeably worse past about eight tables, so a many-table join is the place to look when a query is slow for no obvious reason.

CROSS JOIN has no condition at all and returns every pairing, so the row count is the product of the two tables. That is occasionally what you want - generating a calendar grid, filling a sparse report - and otherwise it is what a forgotten ON clause looks like.

SELECT columns
FROM table1
NATURAL JOIN table2;

SELECT a.col, b.col
FROM table a
JOIN table b ON a.related_col = b.id;

Performance: the two things to check first

Joins are where missing indexes show up, and the sheet's Common Mistakes section names the specific gap.

PostgreSQL does not create an index on a foreign-key column automatically - only on primary keys. MySQL's InnoDB does. So on Postgres, a schema full of correct foreign keys can still join badly, and the fix is an explicit CREATE INDEX on each FK column. The entry adds a second consequence that is easy to miss: without that index, deleting a parent row is also slow, because the database has to scan the child table looking for references.

The other one is the N+1 pattern, which is a join problem that never reaches the database as a join. The application loops over N rows and issues one query per row instead of a single join, and the symptom is a page whose load time grows linearly with the number of records. The entry calls it the most common performance killer in database-backed applications, and the fix is one query with a join, or the ORM's eager-loading option.

• JOIN alone means INNER JOIN.
• Rewrite RIGHT JOIN as LEFT JOIN with the tables swapped, so every join reads the same way.
• Filter the right table of an outer join in ON, not WHERE.
• = NULL never matches; use IS NULL.
• MySQL has no native FULL OUTER JOIN - UNION a LEFT and a RIGHT join instead.
• Index your foreign keys on PostgreSQL; InnoDB already did it for you.

-- After creating a foreign key, add an index:
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id)
);
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- BAD: one query per user (in application code)
-- for each user:
--   SELECT * FROM orders WHERE user_id = ?

-- GOOD: one query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Questions people ask

Where this comes from

Every code block above is rendered from this site’s own entry data at build time, with a link to the entry it came from, and every count in the prose is derived from the same file. How this cheat sheet is organized explains the structure and says what the corpus does not cover.