SQL Joins Explained: INNER, LEFT, FULL, CROSS, Self & LATERAL

Q: Are JOIN and WHERE-based joins the same?

FROM a, b WHERE a.id = b.a_id (implicit join) and FROM a JOIN b ON a.id = b.a_id produce the same result for inner joins, and planners treat them identically. Explicit JOIN syntax is preferred: it separates join conditions from filters and is required for outer joins.

Q: How do I find rows in one table but not another?

Use an anti join: WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id), or LEFT JOIN b ON … WHERE b.id IS NULL. Avoid NOT IN with subqueries that might contain NULLs.

SQL Joins

Relational databases store related data in separate tables: customers in one, orders in another, order items in a third. Joins combine them at query time by matching rows on related columns. They're the core of SQL: nearly every real query joins something, and misunderstanding them causes the classic bugs, from missing rows (the wrong join type) to double-counted revenue (row fan-out) to accidentally turning an outer join into an inner one.

This page covers every join type with examples, the ON-vs-WHERE subtlety for outer joins, semi and anti joins with EXISTS, LATERAL for "top N per group", and how databases execute joins efficiently.

TL;DR

INNER JOIN: only rows with a match on both sides.
LEFT JOIN: all rows from the left table, with matching right rows or NULLs.
FULL OUTER JOIN: all rows from both, matched where possible. CROSS JOIN: every combination.
With outer joins, conditions on the optional table belong in ON, not WHERE, or the join silently becomes inner.
Use EXISTS / NOT EXISTS for "has any" / "has none" checks (semi and anti joins) instead of joining and deduplicating.
Joining one-to-many relationships multiplies rows. Aggregate before joining, or count carefully.

Quick Example

Core Concepts

Join Types

USING (customer_id) is shorthand when both columns share a name. Avoid NATURAL JOIN, which silently joins on every same-named column and breaks when schemas change.

ON vs WHERE With Outer Joins

The ON clause decides which rows match; WHERE filters the result. For outer joins this difference matters:

Rule of thumb: filters on the preserved (left) table go in WHERE; filters on the optional (right) table go in ON, unless you intend to exclude unmatched rows.

Semi Joins and Anti Joins

Semi join: "rows in A that have at least one match in B." Use EXISTS or IN. It never duplicates A's rows.
Anti join: "rows in A with no match in B." Use NOT EXISTS, or LEFT JOIN … WHERE b.id IS NULL.

Avoid NOT IN (subquery) when the subquery can return NULL: x NOT IN (1, NULL) is never true, so the query returns nothing. See NULL handling.

Fan-Out: Why Totals Get Doubled

Joining a table to a one-to-many relationship produces one row per child. Joining two independent one-to-many relationships multiplies them:

Fix it by aggregating each child table separately (in subqueries or CTEs) and then joining the aggregates, or by using EXISTS when you only need to filter.

LATERAL Joins

A LATERAL subquery can reference columns from tables earlier in the FROM clause and runs per row. It's the clean solution for top-N per group, calling set-returning functions per row, or reusing computed expressions. SQL Server and Oracle use CROSS APPLY / OUTER APPLY. Window functions are an alternative for top-N.

How Databases Execute Joins

The query planner picks an algorithm per join:

EXPLAIN ANALYZE shows which one was chosen and how many rows flowed through each step. Poor row estimates (stale statistics) lead to bad choices, like a nested loop over millions of rows. See query optimization.

Best Practices

Index Foreign Keys

Join columns on the "many" side (orders.customer_id) usually need indexes. PostgreSQL doesn't create them automatically for foreign keys. See database indexing.

Alias Tables and Qualify Columns

Use short, meaningful aliases (c, o, oi) and prefix every column (o.total). Unqualified columns become ambiguous, or silently wrong, when another joined table adds a column with the same name.

Prefer EXISTS for Filtering

When you only need to know whether related rows exist, EXISTS is clearer and avoids duplicate rows and DISTINCT workarounds. Planners optimize it into efficient semi joins.

Aggregate Before Joining Many-to-Many Paths

Pre-aggregate child tables to one row per parent before joining them together. That keeps counts and sums correct and often makes queries faster.

Common Mistakes

Using DISTINCT to Hide Fan-Out

Forgetting the Join Condition

FROM customers c, orders o without a WHERE condition is an accidental cross join: 10,000 customers × 1,000,000 orders = 10 billion rows. Use explicit JOIN … ON syntax so a missing condition is obvious.

COUNT(*) With LEFT JOIN

COUNT(*) counts the NULL-extended row for customers without orders, giving 1 instead of 0. Count a column from the optional table: COUNT(o.id).

FAQ

What's 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 fills the right table's columns with NULL where there's no match. Use LEFT JOIN when the left rows must appear regardless, as in "all customers, and their orders if any".

Are JOIN and WHERE-based joins the same?

FROM a, b WHERE a.id = b.a_id (implicit join) and FROM a JOIN b ON a.id = b.a_id produce the same result for inner joins, and planners treat them identically. Explicit JOIN syntax is preferred: it separates join conditions from filters and is required for outer joins.

Do joins make queries slow?

Not inherently. Relational databases are built for joins. Slow joins usually come from missing indexes on join columns, poor row estimates, joining far more rows than needed (filter early), or row fan-out producing huge intermediate results. Check EXPLAIN ANALYZE.

How do I find rows in one table but not another?

Use an anti join: WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id), or LEFT JOIN b ON … WHERE b.id IS NULL. Avoid NOT IN with subqueries that might contain NULLs.