SQL Joins Explained Visually — INNER, LEFT, RIGHT, FULL, and the Ones You've Never Used

Why Joins Trip Up Everyone

Most developers can recite “INNER JOIN returns matching rows.” Then they hit a query that returns 4 million rows instead of 40,000, a LEFT JOIN that silently drops records, or a filter in the wrong clause that turns their outer join into an inner join. Joins are simple to describe and easy to get subtly wrong.

The trick is to stop memorizing Venn diagrams and start thinking in terms of which rows survive and what happens to the columns of the table that didn’t match. Once that model clicks, every join — even the exotic ones — becomes obvious.

Everything below runs on PostgreSQL. Where MySQL differs, I’ll call it out.

---

The Sample Schema

Two tables, deliberately small so you can trace every row by hand.

CREATE TABLE customers (
    id      INT PRIMARY KEY,
    name    TEXT NOT NULL,
    country TEXT
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT,          -- intentionally nullable and NOT a FK for demo
    amount      NUMERIC(10,2),
    status      TEXT
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Alice',   'US'),
    (2, 'Bob',     'US'),
    (3, 'Carlos',  'MX'),
    (4, 'Diana',   'CA');   -- Diana has no orders

INSERT INTO orders (id, customer_id, amount, status) VALUES
    (100, 1,    250.00, 'paid'),
    (101, 1,     90.00, 'paid'),
    (102, 2,    120.00, 'refunded'),
    (103, 3,     60.00, 'paid'),
    (104, NULL, 500.00, 'paid');   -- an orphan order, no customer_id

Note two deliberate edge cases: Diana (id 4) has no orders, and order 104 has a NULL customer_id. These are what separate the joins from each other.

---

INNER JOIN — Only the Matches

An INNER JOIN keeps a row only when the join condition is true on both sides. No match, no row.

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;

 name   | order_id | amount
--------+----------+--------
 Alice  |      100 | 250.00
 Alice  |      101 |  90.00
 Bob    |      102 | 120.00
 Carlos |      103 |  60.00

Diana disappears (no orders). Order 104 disappears (its customer_id is NULL, and NULL = anything is never true). This is the most common source of “where did my rows go?” — an inner join silently drops non-matching rows on both sides.

The INNER keyword is optional; JOIN alone means INNER JOIN. I recommend writing it explicitly so intent is unambiguous.

---

LEFT JOIN — Keep Everything on the Left

A LEFT OUTER JOIN (the OUTER is optional) returns every row from the left table, matched with rows from the right table where the condition holds. When there’s no match, the right-side columns come back as NULL.

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;

 name   | order_id | amount
--------+----------+--------
 Alice  |      100 | 250.00
 Alice  |      101 |  90.00
 Bob    |      102 | 120.00
 Carlos |      103 |  60.00
 Diana  |     NULL |   NULL

Diana is back, padded with NULLs. This is exactly what you want for questions like “list all customers and their order totals, including customers with zero orders.”

The classic anti-join: finding rows with no match

A LEFT JOIN plus an IS NULL filter is the idiomatic way to find “rows on the left with nothing on the right”:

-- Customers who have never placed an order
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

 id | name
----+-------
  4 | Diana

This works because the only way o.id can be NULL here is when no matching order existed. It’s often faster than NOT IN and — critically — it doesn’t break on NULLs the way NOT IN does (more on that later).

---

The #1 LEFT JOIN Bug: Filtering in WHERE vs ON

This is the mistake that turns your outer join into an inner join without you noticing.

Say you want all customers plus their paid orders, including customers with no paid orders. The intuitive-but-wrong version:

-- WRONG: this quietly drops Diana
SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

 name   | order_id | status
--------+----------+--------
 Alice  |      100 | paid
 Alice  |      101 | paid
 Carlos |      103 | paid

Diana and Bob are gone. Why? The LEFT JOIN first produces Diana’s row with o.status = NULL. Then the WHERE o.status = 'paid' runs after the join and evaluates NULL = 'paid' → not true → row discarded. The WHERE clause effectively cancelled the outer join.

The fix: put the condition on the right table inside the ON clause, so it becomes part of the match, not a post-join filter:

-- CORRECT: condition on the right table goes in ON
SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.id
      AND o.status = 'paid'
ORDER BY c.name;

 name   | order_id | status
--------+----------+--------
 Alice  |      100 | paid
 Alice  |      101 | paid
 Bob    |     NULL | NULL
 Carlos |      103 | paid
 Diana  |     NULL | NULL

Rule of thumb: conditions on the outer (right) table belong in ON; conditions on the preserved (left) table belong in WHERE. Filtering the left table in WHERE is fine and expected.

---

RIGHT JOIN — Keep Everything on the Right

A RIGHT OUTER JOIN is the mirror of LEFT JOIN: every row from the right table survives, left-side columns are NULL when unmatched.

SELECT c.name, o.id AS order_id, o.customer_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;

 name   | order_id | customer_id
--------+----------+-------------
 Alice  |      100 |           1
 Alice  |      101 |           1
 Bob    |      102 |           2
 Carlos |      103 |           3
 NULL   |      104 |        NULL

Now order 104 (the orphan with customer_id = NULL) shows up, with name = NULL because no customer matched.

In practice, RIGHT JOIN is rare. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping table order, and most teams standardize on LEFT JOIN for readability — you read top-to-bottom, and the “preserved” table is the first one you see. Use whichever keeps the query legible, but consistency matters more than the choice.

---

FULL OUTER JOIN — Keep Everything on Both Sides

A FULL OUTER JOIN returns matched rows, plus unmatched left rows (right = NULL), plus unmatched right rows (left = NULL). It’s the union of LEFT and RIGHT.

SELECT c.name, o.id AS order_id, o.customer_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name NULLS LAST, o.id;

 name   | order_id | customer_id
--------+----------+-------------
 Alice  |      100 |           1
 Alice  |      101 |           1
 Bob    |      102 |           2
 Carlos |      103 |           3
 Diana  |     NULL |        NULL
 NULL   |      104 |        NULL

Both Diana (unmatched customer) and order 104 (unmatched order) appear. FULL OUTER JOIN shines in reconciliation queries — comparing two data sources and finding rows that exist in one but not the other.

Finding rows that don’t match on either side

-- Customers with no orders OR orders with no customer
SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL OR o.id IS NULL;

 name  | order_id
-------+----------
 Diana |     NULL
 NULL  |      104

MySQL note: MySQL historically has no FULL OUTER JOIN. Emulate it with LEFT JOIN UNION RIGHT JOIN:

-- MySQL FULL OUTER JOIN emulation
SELECT c.name, o.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.id FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

UNION (not UNION ALL) deduplicates the overlapping matched rows.

---

CROSS JOIN — Every Combination

A CROSS JOIN produces the Cartesian product: every row of the left paired with every row of the right. No ON clause. With 4 customers and 5 orders you get 20 rows.

SELECT c.name, o.id AS order_id
FROM customers c
CROSS JOIN orders o;   -- 4 x 5 = 20 rows

That sounds useless until you need to generate combinations. A common real use: build a dense calendar/category grid so reports have a row for every (day, category) pair, even zero-activity ones.

-- Generate every (customer, month) pair for a report skeleton
SELECT c.name, m.month
FROM customers c
CROSS JOIN generate_series(
    DATE '2026-01-01', DATE '2026-03-01', INTERVAL '1 month'
) AS m(month);

Beware the accidental cross join: forget the ON condition (or write FROM a, b with no WHERE) and you get a Cartesian explosion. That’s the classic “why is my query returning millions of rows” bug.

---

SELF JOIN — A Table Joined to Itself

There’s no SELF JOIN keyword; it’s just a regular join where a table appears twice under different aliases. Useful for hierarchical or relational-within-a-table data.

CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       TEXT,
    manager_id INT
);

INSERT INTO employees VALUES
    (1, 'Grace', NULL),   -- CEO, no manager
    (2, 'Heidi', 1),
    (3, 'Ivan',  1),
    (4, 'Judy',  2);

-- Pair each employee with their manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

 employee | manager
----------+---------
 Grace    | NULL
 Heidi    | Grace
 Ivan     | Grace
 Judy     | Heidi

Using LEFT JOIN (not INNER) keeps Grace, the CEO with a NULL manager_id, in the result.

---

LATERAL JOIN — The One You’ve Never Used

LATERAL lets the right side of a join reference columns from the left side. It’s a per-row correlated subquery you can join to — perfect for “top N per group” without window-function gymnastics.

Get the two most recent paid orders per customer:

SELECT c.name, recent.id AS order_id, recent.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.id, o.amount
    FROM orders o
    WHERE o.customer_id = c.id      -- references the outer row
      AND o.status = 'paid'
    ORDER BY o.id DESC
    LIMIT 2
) AS recent;

Use LEFT JOIN LATERAL (...) ON true instead of CROSS JOIN LATERAL if you also want customers with zero matching orders to appear. LATERAL is invaluable when the inner query needs LIMIT, aggregation, or a function call parameterized by the outer row.

MySQL note: MySQL 8.0.14+ supports LATERAL with the same semantics.

---

Multi-Table Joins and Join Order

Real queries chain several joins. Each join operates on the result of everything to its left:

SELECT c.name, o.id AS order_id, oi.product_name, oi.qty
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id   = o.id
WHERE c.country = 'US';

Two things to internalize:

1. A single INNER JOIN anywhere in the chain can drop rows produced by earlier LEFT JOINs. If you LEFT JOIN orders then INNER JOIN order_items, customers without items vanish. Keep the chain consistently outer if you need to preserve rows.
2. The optimizer is free to reorder joins for INNER JOINs — the written order doesn’t dictate execution order. Trust EXPLAIN, not the text order, to understand performance.

---

Watch the Row Count: Fan-Out

Joining on a one-to-many relationship multiplies rows. If a customer has 3 orders and you then join a table with 4 items per order, one customer can balloon into 12 rows. This breaks naive aggregates:

-- WRONG: amount is duplicated per item, inflating the sum
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id   = o.id
GROUP BY c.name;

Because each order row repeats once per item, SUM(o.amount) double-counts. The fix is to aggregate before joining, or aggregate distinct order amounts:

-- Aggregate orders first, then join
SELECT c.name, ord.total
FROM customers c
JOIN (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) ord ON ord.customer_id = c.id;

Whenever a join can fan out, stop and ask what the correct grain of your result should be.

---

USING and NATURAL JOIN — Shortcuts With Sharp Edges

When both tables share an identically named join column, USING shortens the syntax and merges the column into one:

-- Requires both tables to have a column literally named customer_id
SELECT name, amount
FROM customers
JOIN orders USING (customer_id);  -- only if customers.customer_id exists

NATURAL JOIN goes further and joins on all identically named columns automatically:

SELECT * FROM customers NATURAL JOIN orders;

Avoid NATURAL JOIN in production. If someone later adds a column with a coincidentally shared name (like created_at), the join condition silently changes and your results quietly break. Be explicit with ON.

---

Quick Reference

INNER JOIN — rows that match on both sides. Drops non-matches everywhere.

LEFT JOIN — all left rows; right is NULL when unmatched. Filter the right table in ON, not WHERE.

RIGHT JOIN — all right rows. Rewrite as LEFT for readability.

FULL OUTER JOIN — everything from both sides. Great for reconciliation. Emulate with UNION in MySQL.

CROSS JOIN — Cartesian product. Deliberate for grids; accidental for disasters.

SELF JOIN — a table joined to itself via aliases. Hierarchies and pairs.

LATERAL JOIN — right side references the left row. Top-N-per-group and correlated subqueries you can join to.

Master the mental model — which rows survive, and what fills the gaps — and every join stops being a memorization exercise.