Menu

PostgreSQL Joins: INNER, LEFT, FULL, CROSS and LATERAL

A join combines rows from two tables on a condition. INNER JOIN keeps matches, LEFT JOIN keeps every row of the first table, FULL JOIN keeps both, CROSS JOIN pairs everything, and LATERAL runs a subquery per row.

This page includes runnable editors. Edit, run, and see output instantly.

A join builds rows from two tables, matched on a condition: SELECT ... FROM a JOIN b ON a.id = b.a_id. JOIN (INNER JOIN) keeps only rows that match, LEFT JOIN keeps every row of the first table, and FULL JOIN keeps every row of both.

Three rows come back. Grace has no orders, and order 13 belongs to a customer that does not exist, so both are left out. The joins below use the same two tables.

Join types

JoinRows returned
INNER JOINPairs that match the condition
LEFT JOINEvery left row; right columns NULL where nothing matches
RIGHT JOINEvery right row; the mirror of LEFT JOIN
FULL JOINEvery row of both sides, NULLs filling the gaps
CROSS JOINEvery left row paired with every right row

LEFT JOIN: keep rows without a match

Grace appears with 0 orders. Count o.id, not *: count(*) would count Grace's row of NULLs as 1.

  • Rows with no match (an anti-join): add WHERE o.id IS NULL to keep only customers without orders. WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) does the same and states the intent.
  • ON vs WHERE: a condition on the right table belongs in ON. LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 20 drops Grace, because her NULL total fails the WHERE. The join has turned into an inner join. Write ON o.customer_id = c.id AND o.total > 20 to keep every customer.

FULL JOIN: rows missing on either side

FULL JOIN finds mismatches in both directions, which helps when comparing two lists:

The result has Grace with no order and order 13 with no customer. A foreign key on orders.customer_id would have prevented the second row.

CROSS JOIN: every combination

CROSS JOIN has no condition. It pairs every row of one side with every row of the other, so 3 sizes and 2 colors give 6 rows:

The two lists here are VALUES lists named like tables. With real tables it reads FROM sizes CROSS JOIN colors, and FROM sizes, colors does the same. A join whose ON condition is always true multiplies rows the same way, a common cause of a query that suddenly returns far too many.

LATERAL: a subquery for each row

A subquery in FROM normally cannot see the other tables in the same FROM. With LATERAL it can, and it is evaluated once per row of the table before it. That solves "top N per group":

Ada gets her two largest orders, Linus his one, and Grace a row of NULLs because of LEFT JOIN ... ON true. CROSS JOIN LATERAL would drop customers with no orders. With an index on orders (customer_id, total), each subquery reads only a few index entries. For one row per group, DISTINCT ON is often shorter.

Frequently Asked Questions

Is JOIN the same as INNER JOIN in PostgreSQL?

Yes. JOIN on its own means INNER JOIN, and LEFT JOIN means LEFT OUTER JOIN; the INNER and OUTER keywords are optional.

Why does my LEFT JOIN return more rows than the left table?

A left row appears once for every matching right row. A customer with three orders appears three times. Aggregate with GROUP BY, or join to a subquery that already has one row per key.

What is the difference between ON and USING?

ON takes any condition. USING (id) is shorthand for equal columns with the same name in both tables, and the output has a single id column instead of two.

Does the order of joins matter for performance in PostgreSQL?

Usually not. The planner reorders inner joins to find a cheap plan when a query has up to join_collapse_limit (8 by default) tables. Outer joins can be reordered only in some cases, because moving them can change the result.

Coddy programming languages illustration

Learn to code with Coddy

Get started