SQL Joins — Visual Guide with Examples

Overview

SQL joins combine rows from two or more tables based on a related column. They’re one of the most-used constructs in relational databases and one of the most common sources of bugs and slow queries: a WHERE in the wrong place quietly turns a LEFT JOIN into an INNER JOIN, a missing index turns a 5 ms lookup into a sequential scan, and a one-to-many join silently duplicates rows and inflates your SUMs.

What follows covers every join type you will meet in production, with a visual walk-through of what each one returns, plus the join algorithms the query planner actually runs, the syntax variants nobody warns you about (USING, NATURAL, LATERAL), and the performance habits that keep joins cheap at scale. Examples use PostgreSQL syntax; differences for MySQL are called out where they matter.

When to Use

• Combining related data from two or more tables (orders + customers, posts + authors)
• Filtering rows by the presence or absence of a match in another table
• Building reports that aggregate data across entities
• Checking referential integrity or hunting down orphaned records

When not to use a join: if you only need to know whether a matching row exists, not its columns, a semi-join with EXISTS is clearer and often faster. If you’re joining a table to itself repeatedly to walk a hierarchy, a recursive CTE is usually the better tool. For a deeper look at the optimization side, see the complete guide to SQL query optimization.

How the Database Executes a Join

JOIN is a logical operation; what the database physically runs is one of three algorithms. Knowing which one your query gets, and why, is the difference between guessing and tuning.

• Nested Loop — for each row of the outer input, look up matches in the inner input. Cheap when the outer side is small and the inner join key is indexed; brutal when it degenerates into outer_rows × inner_rows comparisons.
• Hash Join — build a hash table over the smaller input, then scan the larger input and probe. Only works for equality conditions; this is the workhorse for big analytical joins.
• Merge Join — both inputs sorted on the join key, merged like a zipper. Shows up when the data is already sorted (index scans) or the result must stay ordered.

You don’t choose the algorithm directly; the planner does, from table statistics. Your job is to feed it good inputs: indexes on join keys, fresh statistics, and predicates that shrink the inputs early. Hash joins also depend on work_mem: when the hash table doesn’t fit in memory it spills to disk in batches, which is the usual explanation when a big join suddenly gets 10× slower. The EXPLAIN section below shows how to verify what the planner picked.

INNER JOIN — Only Matching Rows

Returns rows where there’s a match in both tables. JOIN alone means INNER JOIN; write the full keyword anyway; it documents intent. An INNER JOIN can never introduce NULLs; every output column comes from a real match, which makes it the safest default mental model for joins.

SELECT o.order_id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Orders                    Customers              Result
┌────┬──────────┐        ┌────┬────────┐       ┌────┬────────┬───────┐
│ id │ customer │        │ id │ name   │       │ id │ name   │ total │
├────┼──────────┤        ├────┼────────┤       ├────┼────────┼───────┤
│ 1  │    101   │───────▶│101 │ Alice  │──────▶│ 1  │ Alice  │ 250   │
│ 2  │    102   │──┐     │102 │ Bob    │       │ 2  │ Bob    │ 100   │
│ 3  │    103   │  └────▶│103 │ Carol  │──────▶│ 3  │ Carol  │ 500   │
└────┴──────────┘        └────┴────────┘       └────┴────────┴───────┘
                                                      (no row for 104)

Watch out: on a one-to-many relationship the result has more rows than either input: one per matching pair. A customer with 10 orders produces 10 rows. That multiplication is correct, but it’s also why naive COUNT(*) after a join often surprises people.

LEFT JOIN — All from Left, Matching from Right

Returns every row from the left table, plus matching rows from the right. Non-matching right-side columns come back as NULL.

SELECT c.name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Customers                 Orders                 Result
┌────┬────────┐          ┌────┬──────────┐      ┌────┬────────┬───────┐
│ id │ name   │          │ id │ customer │      │ id │ name   │ total │
├────┼────────┤          ├────┼──────────┤      ├────┼────────┼───────┤
│101 │ Alice  │─────────▶│ 1  │    101   │─────▶│101 │ Alice  │ 250   │
│102 │ Bob    │─────────▶│ 2  │    102   │─────▶│102 │ Bob    │ 100   │
│103 │ Carol  │─────────▶│ 3  │    103   │─────▶│103 │ Carol  │ 500   │
│104 │ Dave   │───✕──────│    │          │─────▶│104 │ Dave   │ NULL  │
└────┴────────┘          └────┴──────────┘      └────┴────────┴───────┘

The NULL rows are the feature: filtering on them is the classic way to find customers without orders:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_id IS NULL;

One subtlety that bites everyone once: a predicate on the right table belongs in ON, not WHERE. ON c.id = o.customer_id AND o.total > 100 keeps every customer and just nulls out cheap orders; WHERE o.total > 100 discards Dave entirely; you’ve written an INNER JOIN with extra steps.

RIGHT JOIN — All from Right, Matching from Left

The mirror of LEFT JOIN: every row from the right table, matches from the left. It exists for completeness; in practice, swap the table order and write LEFT JOIN. a LEFT JOIN b and b RIGHT JOIN a produce identical results, and the first form reads left-to-right the way people scan queries.

FULL OUTER JOIN — All Rows from Both

Returns every row from both tables, matched where possible, NULL-padded where not. It’s the join for reconciliation work: “show me everything that exists on either side.”

SELECT c.name, o.order_id, o.total
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

Result
┌─────┬────────┬───────┐
│ id  │ name   │ total │
├─────┼────────┼───────┤
│ 101 │ Alice  │ 250   │
│ 102 │ Bob    │ 100   │
│ 103 │ Carol  │ 500   │
│ 104 │ Dave   │ NULL  │  ◀── customer with no order
│  5  │ NULL   │ 75    │  ◀── orphaned order (no customer)
└─────┴────────┴───────┘

MySQL doesn’t implement FULL OUTER JOIN. Emulate it with a UNION of a LEFT JOIN and an anti-join:

SELECT c.name, o.order_id, o.total
FROM customers c LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.order_id, o.total
FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL;

CROSS JOIN — Cartesian Product

Every row of the first table combined with every row of the second. Result size is rows_a × rows_b, which is why it shows up by accident far more often than on purpose.

-- 3 colors × 4 sizes = 12 rows
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;

Legitimate uses: generating a dimensional grid (all size/color SKUs, all date × store combinations for a report scaffold), or joining a single-row result. Accidental use: the old comma-join syntax FROM a, b is a CROSS JOIN if you forget the WHERE, one reason explicit JOIN ... ON syntax won.

SELF JOIN — Joining a Table to Itself

A table can appear twice in FROM under different aliases. The canonical use is hierarchy stored as a parent reference: employees and managers:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Employees
┌────┬────────┬───────────┐
│ id │ name   │ manager_id│
├────┼────────┼───────────┤
│ 1  │ Alice  │ NULL      │  ◀── CEO
│ 2  │ Bob    │ 1         │
│ 3  │ Carol  │ 1         │
│ 4  │ Dave   │ 2         │
└────┴────────┴───────────┘

Result
┌─────────┬─────────┐
│employee │ manager │
├─────────┼─────────┤
│ Alice   │ NULL    │
│ Bob     │ Alice   │
│ Carol   │ Alice   │
│ Dave    │ Bob     │
└─────────┴─────────┘

LEFT (not INNER) so the CEO, who has no manager, still appears. Self joins also work for “rows in the same group” comparisons, like finding customers who placed orders on the same day.

Semi-Joins and Anti-Joins — EXISTS vs JOIN

Two questions sound like joins but are really existence checks, and SQL has dedicated constructs for them.

A semi-join asks “does a match exist?”; it returns each left row at most once and stops looking at the first hit. EXISTS is the readable way to write it:

-- Customers that have at least one order (no duplicates, no matter how many orders)
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

The INNER JOIN equivalent requires DISTINCT to de-duplicate, which costs a sort or hash aggregate. Planners in PostgreSQL and MySQL often rewrite EXISTS into a semi-join internally; check EXPLAIN if you’re curious.

An anti-join asks “does no match exist?”: orphaned records, churned customers, unsold products. Two correct spellings:

-- Preferred: NOT EXISTS (NULL-safe)
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Equivalent: LEFT JOIN + IS NULL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

The spelling to avoid is NOT IN on a nullable column. NOT IN (subquery) uses three-valued logic: if the subquery returns even one NULL, every comparison becomes UNKNOWN and the query returns zero rows, silently. NOT EXISTS has no such trap, which is why most teams standardize on it.

JOIN Syntax Variants — USING, NATURAL, Multi-Column, LATERAL

Beyond ON, SQL has a few shorthand and specialized forms worth knowing, and one worth avoiding.

USING (column) — when both sides share the same column name, USING removes the repetition and collapses the two join columns into one in the output:

SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c USING (customer_id);

NATURAL JOIN — joins on every column the two tables happen to share a name with, including ones you didn’t intend (created_at, updated_at, status). Adding a same-named column later silently changes the query’s meaning. It reads nicely in tutorials; in production schemas it’s a maintenance hazard; write the join keys explicitly.

Multi-column joins — composite keys just use AND:

SELECT *
FROM orders o
JOIN order_items oi
  ON o.order_id = oi.order_id
  AND o.store_id = oi.store_id;

LATERAL (PostgreSQL) — lets the right side of a join reference columns from the left, turning a subquery in FROM into a per-row correlated computation. The classic use is “top N per group”: the 3 most recent orders per customer.

SELECT c.name, recent.order_id, recent.total
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.total
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.created_at DESC
    LIMIT 3
) AS recent ON true;

Without LATERAL, that pattern needs a window function over the whole table and a filter, which is also a valid approach (see SQL window functions) but scans every order row before ranking.

Joining More Than Two Tables

Joins chain left to right, and each JOIN ... ON attaches one more table to the running result:

SELECT c.name, o.order_id, p.name AS product
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id;

A few things worth knowing once queries grow past two tables:

• The planner reorders joins freely. It doesn’t execute them in the order you wrote; it costs every plausible order and picks the cheapest. Write them in the order that reads best to a human; the optimizer handles the rest. In PostgreSQL, join_collapse_limit (default 8) caps how far it explores; queries past that may keep the written order, one more reason to write a sensible order anyway.
• Mixing INNER and LEFT has a direction. Once a LEFT JOIN’s right-side columns feed an INNER JOIN, the NULL rows get discarded and the “left” guarantee is gone. If everything after orders must stay optional, all the joins after it need to be LEFT too.
• Intermediate row counts are the real cost. A query that starts from the smallest filtered input usually wins. Filtering early (WHERE on the driving table) shrinks every step that follows.

JOINs with Aggregation — GROUP BY and HAVING

Joins feed aggregations; aggregations expose join bugs. A report like “total spend per customer” joins and groups in one pass:

SELECT
    c.name,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total), 0) AS total_spent,
    MAX(o.created_at) AS last_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;

Three details matter here:

• LEFT JOIN keeps customers with zero orders; COALESCE turns their NULL sum into 0.
• COUNT(o.order_id) counts non-NULL values, so zero-order customers correctly get 0. COUNT(*) would give them 1, a classic off-by-one.
• HAVING filters after grouping, WHERE filters before. To find customers with more than 3 orders:

SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING COUNT(o.order_id) > 3
ORDER BY order_count DESC;

And a warning that deserves its own sentence: when you join two one-to-many tables (say, orders and shipments per customer), row counts multiply against each other and every SUM is inflated. Aggregate each fact table first, then join the aggregates, or use COUNT(DISTINCT ...) and separate SUM subqueries. This is the single most common wrong-number bug in production reporting.

Performance Optimization

Joins are where query performance lives or dies. These are the levers, roughly in order of impact.

Index the Join Columns

The join key on the “many” side needs an index, or every join becomes a scan. Primary keys are already indexed; foreign keys usually aren’t; PostgreSQL doesn’t index them automatically:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

If a query only needs columns covered by an index, a covering index lets the database skip the table entirely (index-only scan):

CREATE INDEX idx_orders_customer_total ON orders(customer_id, total);

For the broader indexing strategy, see indexing strategies.

Do Not Join on Calculated Values

Wrapping a join column in a function defeats the index:

-- Slow: UPPER prevents index use on both sides
SELECT * FROM orders o
JOIN customers c ON UPPER(o.customer_email) = UPPER(c.email);

-- Fast: join on the indexed column directly
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id;

If you must join on a normalized value, either store it normalized (computed/generated column with its own index) or create an expression index, but fixing the schema is usually the better answer.

Filter Before Joining — and Know Where the Filter Goes

Smaller inputs mean cheaper joins. With an INNER JOIN, filtering in ON or WHERE is equivalent: the planner treats them the same. With a LEFT JOIN it isn’t equivalent: ON conditions decide what counts as a match, WHERE conditions filter the final result (and a WHERE on the right table silently undoes the LEFT). Put join logic in ON, result filtering in WHERE, and the query says what it means.

-- Customers, with only their big orders attached (Dave survives with NULLs)
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.total > 100;

-- Customers who have at least one big order (Dave is gone)
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.total > 100;

Keep Statistics Fresh

The planner picks nested loop vs. hash join from row-count estimates. After a bulk load or big delete, stale statistics cause the classic “it chose a nested loop over 2 million rows” plan. Run ANALYZE (autovacuum normally handles it, but bulk operations outrun it):

ANALYZE orders;

Avoid OR Across Joined Tables

OR between conditions on different tables defeats most join strategies. Splitting into UNION lets each branch use its own indexes:

SELECT c.name FROM customers c
JOIN orders o ON c.id = o.customer_id WHERE o.total > 500
UNION
SELECT c.name FROM customers c
JOIN returns r ON c.id = r.customer_id WHERE r.amount > 500;

Verify with EXPLAIN

Don’t guess; read the plan. EXPLAIN ANALYZE shows which join algorithm ran and how far off the row estimates were:

EXPLAIN ANALYZE
SELECT c.name, o.total
FROM customers c
JOIN orders o ON c.id = o.customer_id;

What to look for: Nested Loop over a huge row estimate (add the missing index or refresh stats), Seq Scan feeding a Hash Join where an index should exist, and estimated rows wildly different from actual rows (run ANALYZE).

Real-World Scenario: E-commerce Reporting

A realistic multi-table report ties everything above together. Five tables: customers, orders, order_items, products, categories — and four questions the business actually asks:

-- Query 1: Top 10 customers by total spend
SELECT c.id, c.email, SUM(oi.line_total) AS total_spent,
       COUNT(DISTINCT o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed'
  AND o.created_at >= '2026-01-01'
GROUP BY c.id, c.email
ORDER BY total_spent DESC
LIMIT 10;

-- Query 2: Sales by category (current month)
SELECT cat.name AS category,
       SUM(oi.line_total) AS revenue,
       SUM(oi.quantity) AS units_sold,
       COUNT(DISTINCT o.id) AS order_count
FROM categories cat
INNER JOIN products p ON p.category_id = cat.id
INNER JOIN order_items oi ON oi.product_id = p.id
INNER JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'completed'
  AND o.created_at >= DATE_TRUNC('month', NOW())
GROUP BY cat.name
ORDER BY revenue DESC;

-- Query 3: Customers with no orders in 90 days (churn candidates)
SELECT c.id, c.email, MAX(o.created_at) AS last_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.email
HAVING MAX(o.created_at) < NOW() - INTERVAL '90 days'
   OR MAX(o.created_at) IS NULL
ORDER BY last_order DESC NULLS LAST;

-- Query 4: Products never purchased (anti-join)
SELECT p.sku, p.name, p.price
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL
  AND p.is_active = true
ORDER BY p.created_at DESC;

-- Indexes that make these cheap:
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status_date ON orders(status, created_at);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_products_category ON products(category_id);

Note the pattern across all four: join on indexed keys, filter on status and date before the planner has to carry those rows through the join, and use LEFT JOIN ... IS NULL (or NOT EXISTS) for the “never happened” questions instead of NOT IN.

Best Practices

• Write INNER JOIN explicitly. Bare JOIN works, but the full keyword removes ambiguity for whoever reads the query next.
• Alias every table, qualify every column. o.customer_id, not customer_id; multi-table queries break the moment two tables share a column name, and they always eventually do.
• Index the foreign keys yourself. PostgreSQL won’t do it for you; MySQL does it only when the constraint is declared. Every unindexed FK is a nested loop waiting to happen at scale.
• Put join predicates in ON, result predicates in WHERE. Especially with LEFT JOIN, where moving one line changes the result.
• Use EXISTS/NOT EXISTS for existence checks. Clearer than JOIN + DISTINCT, and safe where NOT IN isn’t.
• Aggregate first, join second. When two fact tables both fan out, join their GROUP BY results, not their raw rows.
• Say no to NATURAL JOIN. Explicit join keys survive schema changes; implicit ones don’t.

Common Mistakes

• Implicit comma joins. FROM orders, customers without a WHERE is a CROSS JOIN. With a WHERE it’s an INNER JOIN wearing a disguise; either way, write the explicit syntax.
• Filtering a LEFT JOIN’s right table in WHERE. WHERE o.col ... discards the NULL rows the LEFT JOIN exists to keep. Move the predicate to ON or accept that you wanted INNER JOIN.
• NOT IN against a nullable column. One NULL in the subquery result and the whole query returns zero rows, no error, no warning. Use NOT EXISTS.
• Trusting COUNT(*) and SUM after a fan-out join. One-to-many joins multiply rows; aggregate before joining or count distinct keys.
• N+1 queries from application code. Fetching customers, then querying orders in a loop, is a distributed nested loop with network latency per iteration. One join, or eager loading in the ORM, wins every time.
• Joining on unindexed or mismatched-type columns. bigint joined to varchar, or columns with different collations, can silently disable index use. Match types and collations, index the keys.
• NATURAL JOIN and bare USING on unstable schemas. Anything that infers join keys by name breaks the day a same-named column appears.

Troubleshooting

• Query returns more rows than the left table has. Expected on a one-to-many join: count the pairs, not the left rows. If the count is also wrong, you’re probably joining two many-side tables at once; aggregate each side first.
• NOT IN returns zero rows for no visible reason. There’s a NULL in the subquery result. Rewrite as NOT EXISTS (or add WHERE col IS NOT NULL inside the subquery) and the rows come back.
• Join was fast in dev, slow in production. Dev had 100 rows; production has millions and a missing FK index. EXPLAIN will show a nested loop or sequential scan; index the join key.
• Plan uses nested loops where a hash join should be. Row estimates are off. Run ANALYZE on both tables; if it persists, the predicate is skewing estimates; a CREATE STATISTICS object or a rewritten predicate usually fixes it.
• LEFT JOIN behaves like INNER JOIN. Somewhere downstream (WHERE, a second join, a NOT NULL requirement) the NULL rows are being discarded. Check for right-table columns referenced in WHERE.
• Deadlocks under concurrent writes touching joined tables. Transactions should lock tables in a consistent order; the join itself isn’t the problem; how the surrounding writes lock the tables is.

Further Reading

• PostgreSQL Tutorial — Joins — official walkthrough of join types
• PostgreSQL Queries — Table Expressions — JOIN, USING, LATERAL semantics
• MySQL 8.4 — JOIN Clause — including the FULL OUTER JOIN gap
• PostgreSQL — EXPLAIN — reading join plans
• On this site: SQL CTE guide for recursive hierarchies, and database normalization for the schema side of join-heavy designs.