Databases

A Visual Guide to SQL Joins: Types, Examples, and Use Cases

A visual, row-by-row guide to INNER, OUTER, CROSS, SELF, SEMI, ANTI, and LATERAL joins, plus NULL behavior, query pitfalls, and performance basics.

·
15 min read
·
AI-assisted

AI tooling disclosure

Generative AI materially contributed to this article.

Human review confirmed by the author.

A Visual Guide to SQL Joins: Types, Examples, and Use Cases

Inner, outer, cross, self, semi, anti, and lateral joins, drawn as row-matching diagrams so you can see exactly what each one returns.

Most join tutorials reach for the same picture: two overlapping circles. Venn diagrams are tidy, but they describe sets of values, and joins operate on rows. A circle can’t show why a customer with two orders appears twice, why a row with a NULL key vanishes, or why a careless join multiplies your row count.

This guide uses a different picture. Each diagram draws the actual rows from two tables, connects the ones that match, and colors every row by what happens to it. Once you can see the rows, every join type, from INNER to LATERAL, turns out to be a small variation on one idea.

The Sample Data

Two tables, built with deliberate quirks. Asha has two orders, Chitra and Dev have none, and order 104 points to a customer who doesn’t exist.

customers

customer_id

name

1

Asha

2

Ben

3

Chitra

4

Dev

orders

order_id

customer_id

amount

101

1

250

102

1

80

103

2

120

104

5

60

To follow along, run this in most SQL engines (PostgreSQL, MySQL, SQL Server, SQLite, and similar):

CREATE TABLE customers (customer_id INT, name VARCHAR(20));
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chitra'), (4, 'Dev');

CREATE TABLE orders (order_id INT, customer_id INT, amount INT);
INSERT INTO orders VALUES (101, 1, 250), (102, 1, 80), (103, 2, 120), (104, 5, 60);

Here are the same tables drawn as rows. A line joins two rows when customer_id matches. Rows drawn in red have no partner on the other side:

flowchart LR
    subgraph CUST["customers"]
        C1["1 Asha"]
        C2["2 Ben"]
        C3["3 Chitra"]
        C4["4 Dev"]
    end
    subgraph ORD["orders"]
        O1["order 101<br/>customer 1"]
        O2["order 102<br/>customer 1"]
        O3["order 103<br/>customer 2"]
        O4["order 104<br/>customer 5"]
    end
    C1 --- O1
    C1 --- O2
    C2 --- O3
    C4 ~~~ O4

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f
    classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888
    classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d
    class C3,C4,O4 lonely
    style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

Keep this picture in mind. Every diagram below reuses it, and only the colors change.

One note on the result tables: they are sorted for readability. Without an ORDER BY, SQL guarantees no particular row order.

The One Loop Behind Every Join

Whatever its name, every join follows the same logic. The engine compares candidate pairs of rows using the ON condition, and then decides what to do with rows that never found a partner:

flowchart TD
    A["Compare each candidate pair<br/>of rows using the ON condition"] --> B{"ON is TRUE?"}
    B -->|Yes| C["Emit the combined row"]
    B -->|"False or NULL"| D["No match for this pair"]
    D --> E{"Row never matched anything<br/>and the join type preserves<br/>its side?"}
    E -->|Yes| F["Emit the row once,<br/>padded with NULLs"]
    E -->|No| G["Drop the row"]

Only the last decision changes between INNER, LEFT, RIGHT, and FULL joins: which sides are preserved. Keep that diamond in mind as we go through them.

From here on, the join diagrams use three colors:

  • Green: the row appears in the result.

  • Amber: the row appears in the result, padded with NULLs because it has no partner.

  • Grey: the row does not appear in the result.


1. INNER JOIN: Only Matched Rows

An inner join keeps a row only when it found a partner on the other side. JOIN on its own means INNER JOIN.

SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;
flowchart LR
    subgraph CUST["customers"]
        C1["1 Asha"]
        C2["2 Ben"]
        C3["3 Chitra"]
        C4["4 Dev"]
    end
    subgraph ORD["orders"]
        O1["order 101<br/>customer 1"]
        O2["order 102<br/>customer 1"]
        O3["order 103<br/>customer 2"]
        O4["order 104<br/>customer 5"]
    end
    C1 --- O1
    C1 --- O2
    C2 --- O3
    C4 ~~~ O4

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f
    classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888
    classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d
    class C1,C2,O1,O2,O3 kept
    class C3,C4,O4 dropped
    style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

name

order_id

amount

Asha

101

250

Asha

102

80

Ben

103

120

Two things to notice. Asha appears twice because a join returns one row per matching pair, not one row per customer. And Chitra, Dev, and order 104 are gone, because none of them had a partner.

Use an inner join when you only care about complete records, such as orders that belong to known customers.

2. LEFT JOIN: Keep Everything on the Left

A left join keeps every row from the left table. Where there is no match, the right-side columns are filled with NULL.

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;
flowchart LR
    subgraph CUST["customers"]
        C1["1 Asha"]
        C2["2 Ben"]
        C3["3 Chitra"]
        C4["4 Dev"]
    end
    subgraph ORD["orders"]
        O1["order 101<br/>customer 1"]
        O2["order 102<br/>customer 1"]
        O3["order 103<br/>customer 2"]
        O4["order 104<br/>customer 5"]
    end
    C1 --- O1
    C1 --- O2
    C2 --- O3
    C4 ~~~ O4

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f
    classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888
    classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d
    class C1,C2,O1,O2,O3 kept
    class C3,C4 padded
    class O4 dropped
    style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

name

order_id

amount

Asha

101

250

Asha

102

80

Ben

103

120

Chitra

NULL

NULL

Dev

NULL

NULL

Chitra and Dev survive, shown in amber, with NULL where the order columns would be. Order 104 is dropped, since it belongs to the right side and the right side isn’t preserved.

The left join is the workhorse of analytics because it lets you start from a base table (customers, users, dates) and attach optional context without losing anyone. A handy sanity check: the result can never have fewer rows than the left table.

3. RIGHT JOIN: The Mirror Image

A right join is a left join with the roles reversed. These two queries return exactly the same rows:

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id;

SELECT c.name, o.order_id, o.amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
flowchart LR
    subgraph CUST["customers"]
        C1["1 Asha"]
        C2["2 Ben"]
        C3["3 Chitra"]
        C4["4 Dev"]
    end
    subgraph ORD["orders"]
        O1["order 101<br/>customer 1"]
        O2["order 102<br/>customer 1"]
        O3["order 103<br/>customer 2"]
        O4["order 104<br/>customer 5"]
    end
    C1 --- O1
    C1 --- O2
    C2 --- O3
    C4 ~~~ O4

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f
    classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888
    classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d
    class C1,C2,O1,O2,O3 kept
    class O4 padded
    class C3,C4 dropped
    style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

name

order_id

amount

Asha

101

250

Asha

102

80

Ben

103

120

NULL

104

60

Now it is the orders that are all preserved, so order 104 appears with a NULL name, while Chitra and Dev drop out. Many teams standardize on LEFT JOIN and reorder the tables when needed, because "start with the base table and keep adding to it" is easier to read than a mix of directions.

4. FULL OUTER JOIN: Keep Everything

A full outer join preserves both sides. Matched rows are combined, and unmatched rows from either side appear with NULLs.

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;
flowchart LR
    subgraph CUST["customers"]
        C1["1 Asha"]
        C2["2 Ben"]
        C3["3 Chitra"]
        C4["4 Dev"]
    end
    subgraph ORD["orders"]
        O1["order 101<br/>customer 1"]
        O2["order 102<br/>customer 1"]
        O3["order 103<br/>customer 2"]
        O4["order 104<br/>customer 5"]
    end
    C1 --- O1
    C1 --- O2
    C2 --- O3
    C4 ~~~ O4

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f
    classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888
    classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d
    class C1,C2,O1,O2,O3 kept
    class C3,C4,O4 padded
    style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

name

order_id

amount

Asha

101

250

Asha

102

80

Ben

103

120

Chitra

NULL

NULL

Dev

NULL

NULL

NULL

104

60

The classic use is reconciliation: comparing two systems and finding what exists in only one of them. Any row where one side’s key is NULL is a discrepancy.

MySQL has no FULL OUTER JOIN. You can emulate it by combining a left and a right join. Use UNION here, not UNION ALL, so the matched rows that both queries return are not counted twice:

SELECT c.name, o.order_id, o.amount
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION
SELECT c.name, o.order_id, o.amount
FROM customers c RIGHT JOIN orders o ON o.customer_id = c.customer_id;

One caveat: UNION also removes rows that are genuinely identical, which matters if your data can contain true duplicates.


5. Anti-Joins: Rows With No Match

An anti-join answers "which rows have no partner?" Standard SQL has no ANTI JOIN keyword, so you write it one of two ways. (Spark SQL and Databricks are the exception: they offer LEFT ANTI JOIN and LEFT SEMI JOIN directly.) The first is a left join that keeps only the unmatched rows:

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

The second uses NOT EXISTS, which says what you mean more directly:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
flowchart LR
    subgraph CUST["customers"]
        C1["1 Asha"]
        C2["2 Ben"]
        C3["3 Chitra"]
        C4["4 Dev"]
    end
    subgraph ORD["orders"]
        O1["order 101<br/>customer 1"]
        O2["order 102<br/>customer 1"]
        O3["order 103<br/>customer 2"]
        O4["order 104<br/>customer 5"]
    end
    C1 --- O1
    C1 --- O2
    C2 --- O3
    C4 ~~~ O4

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f
    classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888
    classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d
    class C3,C4 kept
    class C1,C2,O1,O2,O3,O4 dropped
    style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

Both queries return the same result:

name

Chitra

Dev

In the first form, the IS NULL test must be on a column that can never be NULL in a real match, such as the right table’s key. Avoid NOT IN (subquery) for this job: a single NULL in the subquery makes it return no rows at all.

Swap the tables and you get the other direction. This one finds orphans, orders with no customer, and it is a staple data quality check:

SELECT o.order_id, o.amount
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);
flowchart LR
    subgraph CUST["customers"]
        C1["1 Asha"]
        C2["2 Ben"]
        C3["3 Chitra"]
        C4["4 Dev"]
    end
    subgraph ORD["orders"]
        O1["order 101<br/>customer 1"]
        O2["order 102<br/>customer 1"]
        O3["order 103<br/>customer 2"]
        O4["order 104<br/>customer 5"]
    end
    C1 --- O1
    C1 --- O2
    C2 --- O3
    C4 ~~~ O4

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f
    classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888
    classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d
    class O4 kept
    class C1,C2,O1,O2,O3,C3,C4 dropped
    style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

order_id

amount

104

60

6. Semi-Joins: Is There a Match?

A semi-join keeps rows from one side only if a match exists, and it never pulls in columns from the other side. It is written with EXISTS:

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
flowchart LR
    subgraph CUST["customers"]
        C1["1 Asha"]
        C2["2 Ben"]
        C3["3 Chitra"]
        C4["4 Dev"]
    end
    subgraph ORD["orders"]
        O1["order 101<br/>customer 1"]
        O2["order 102<br/>customer 1"]
        O3["order 103<br/>customer 2"]
        O4["order 104<br/>customer 5"]
    end
    C1 --- O1
    C1 --- O2
    C2 --- O3
    C4 ~~~ O4

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f
    classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888
    classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d
    class C1,C2 kept
    class C3,C4,O1,O2,O3,O4 dropped
    style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

name

Asha

Ben

Compare this with the inner join from earlier, which returned Asha twice. A semi-join returns each customer at most once, no matter how many orders match, because the orders are only used to answer a yes-or-no question. That makes it the right tool for "customers who have ordered" when you don’t need order details.


7. CROSS JOIN: Every Combination

A cross join has no ON condition. It pairs every row on the left with every row on the right, so the result has m × n rows. Given two sizes and two colors:

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
flowchart LR
    subgraph SZ["sizes"]
        S1["S"]
        S2["M"]
    end
    subgraph CL["colors"]
        K1["Red"]
        K2["Blue"]
    end
    S1 --- K1
    S1 --- K2
    S2 --- K1
    S2 --- K2

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    class S1,S2,K1,K2 kept
    style SZ fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style CL fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

size

color

M

Blue

M

Red

S

Blue

S

Red

Cross joins are useful for generating combinations, such as every product in every store, or a calendar of dates paired with every account so that days with no activity still show up as zeros.

They are also dangerous when they happen by accident. The old comma syntax, FROM customers, orders, with the WHERE condition forgotten, silently becomes a cross join: our 4 customers and 4 orders would produce 16 rows. On two large tables, the result can be catastrophic. Write CROSS JOIN when you mean a cross join, and always write the ON condition otherwise. Some engines, such as PostgreSQL and SQL Server, reject a plain JOIN with no ON, but MySQL, SQLite, and Databricks accept it and treat it as a cross join, so you can’t count on a syntax error to catch the mistake.

8. Self Joins: A Table Meets Itself

A self join joins a table to itself under two aliases, so each row can be related to another row of the same table. The classic example is an employees table where each row stores its manager’s id:

emp_id

name

manager_id

1

Maya

NULL

2

Raj

1

3

Sana

1

4

Tom

2

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
flowchart BT
    M1["1 Maya<br/>manager_id NULL"]
    M2["2 Raj<br/>manager_id 1"] -->|"reports to"| M1
    M3["3 Sana<br/>manager_id 1"] -->|"reports to"| M1
    M4["4 Tom<br/>manager_id 2"] -->|"reports to"| M2

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    class M1,M2,M3,M4 kept

employee

manager

Maya

NULL

Raj

Maya

Sana

Maya

Tom

Raj

The alias e plays the employee and m plays the manager, even though both are the same table. We used a LEFT JOIN so that Maya, who has no manager, still appears. An inner join would have dropped her. Self joins also handle one level of a hierarchy at a time, such as a category and its parent (walking a whole tree takes a recursive CTE), and they answer questions like "which pairs of rows in this table share a property?"

9. NATURAL JOIN and USING: The Shorthands

Two shortcuts exist for joins on identically named columns. USING names the column explicitly:

SELECT *
FROM customers
JOIN orders USING (customer_id);

customer_id

name

order_id

amount

1

Asha

101

250

1

Asha

102

80

2

Ben

103

120

Besides saving typing, USING merges the join column, so customer_id appears once in SELECT * instead of twice.

NATURAL JOIN goes further and joins on every column the two tables share, with no condition written at all:

SELECT * FROM customers NATURAL JOIN orders;

It returns the same rows today, which is exactly why it is risky. If someone later adds an updated_at column to both tables, the join condition silently changes to include it, and the results change with no code edit. Treat NATURAL JOIN as a curiosity and prefer USING or an explicit ON. If the code must also run on SQL Server, use ON, because SQL Server supports neither shorthand.

10. Non-Equi Joins: Matching on Ranges

Every join so far matched on equality. The ON condition can be any expression, including <, >, or a range. This is a non-equi join. Suppose we want to label each order with a discount tier based on its amount:

tier

min_amount

max_amount

Bronze

0

100

Silver

100

200

Gold

200

1000

SELECT o.order_id, o.amount, t.tier
FROM orders o
JOIN tiers t
  ON o.amount >= t.min_amount
 AND o.amount <  t.max_amount;
flowchart LR
    subgraph ORD["orders"]
        A1["101: amount 250"]
        A2["102: amount 80"]
        A3["103: amount 120"]
        A4["104: amount 60"]
    end
    subgraph TIER["tiers"]
        T1["Bronze: 0 up to 100"]
        T2["Silver: 100 up to 200"]
        T3["Gold: 200 up to 1000"]
    end
    A1 --- T3
    A2 --- T1
    A3 --- T2
    A4 --- T1

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    class A1,A2,A3,A4,T1,T2,T3 kept
    style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style TIER fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

order_id

amount

tier

101

250

Gold

102

80

Bronze

103

120

Silver

104

60

Bronze

Notice the half-open range: >= on the lower bound and < on the upper bound. With BETWEEN, which is inclusive on both ends, an amount of exactly 100 would match both Bronze and Silver and the order would be duplicated. The same half-open pattern is how you join facts to historical dimension rows, such as slowly changing dimensions.

Non-equi joins are also where performance suffers, because most engines can’t use their fastest strategy (the hash join, covered below) for them, though some add specialized range-join optimizations. On large tables, keep the range side small or pre-filter aggressively.

11. LATERAL Joins: A Subquery for Every Row

An ordinary derived table can’t see the other tables in the FROM clause. A lateral join can: the subquery is conceptually re-run for each row on the left, and it may reference that row’s columns. This unlocks the classic "top N per group" problem, such as the latest order for each customer:

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN LATERAL (
  SELECT order_id, amount
  FROM orders
  WHERE orders.customer_id = c.customer_id
  ORDER BY order_id DESC   -- highest order_id = latest order
  LIMIT 1
) o ON TRUE;
flowchart LR
    A["customers row<br/>Asha, id 1"] -->|"c.customer_id passed in"| B["Subquery runs for this row<br/>orders of customer 1<br/>newest first, LIMIT 1"]
    B --> C["Returns order 102"]
    D["customers row<br/>Chitra, id 3"] -->|"c.customer_id passed in"| E["Subquery runs for this row<br/>orders of customer 3"]
    E --> F["Returns nothing,<br/>LEFT JOIN pads with NULL"]

Syntax varies by engine. PostgreSQL, MySQL 8.0.14 and later, and Oracle 12c and later support LATERAL. SQL Server uses CROSS APPLY and OUTER APPLY, which Oracle 12c and later also accepts. If your engine has neither, a window function gives the same answer:

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN (
  SELECT order_id, customer_id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id DESC) AS rn
  FROM orders
) o ON o.customer_id = c.customer_id AND o.rn = 1;

Either way, each customer gets at most one order, and customers with no orders still appear:

name

order_id

amount

Asha

102

80

Ben

103

120

Chitra

NULL

NULL

Dev

NULL

NULL


12. How the Database Executes a Join

So far we’ve covered what a join returns. How the engine computes it is a separate question, and it explains many join performance problems. There are three main strategies, and the optimizer picks one for you.

Nested loop. For each row on one side, look for matches on the other side. This is cheap when one side is small and the other has an index on the join key, and it is the fallback for non-equi joins.

Hash join. Build an in-memory hash table from the smaller input, then stream the larger input through it, probing for matches:

flowchart LR
    S["Smaller input<br/>customers"] -->|"1. Build"| H["Hash table<br/>keyed on customer_id"]
    L["Larger input<br/>orders"] -->|"2. Probe one row at a time"| H
    H --> R["Matching pairs<br/>are emitted"]

This is the usual choice for large inputs joined on equality, with no useful index.

Merge join. Walk two inputs side by side after sorting both on the join key. It shines when the data is already sorted, for example through an index or a clustered layout.

You can see which one the optimizer chose by running EXPLAIN, where, in PostgreSQL, they show up as Nested Loop, Hash Join, and Merge Join (other engines use their own names). The choice follows roughly this logic:

flowchart TD
    Q{"What does the join<br/>condition look like?"}
    Q -->|"Range or inequality"| N1["Nested loop"]
    Q -->|"Equality, one small side<br/>and an index on the other"| N2["Nested loop with index lookups"]
    Q -->|"Equality, both inputs<br/>already sorted"| M["Merge join"]
    Q -->|"Equality, large inputs"| H["Hash join"]

Three Join Pitfalls

Most join bugs come from just three places.

Pitfall 1: The WHERE trap on outer joins

You write a LEFT JOIN to keep every customer, then add a filter on the right table in WHERE. The padded rows have NULL in that column, NULL > 100 is unknown, and WHERE discards them. Your left join has turned into an inner join:

flowchart LR
    A["LEFT JOIN result<br/>Chitra and Dev padded with NULL"] --> B["WHERE o.amount > 100"]
    B --> C["NULL > 100 is unknown<br/>padded rows are removed"]
    C --> D["Behaves like an INNER JOIN"]
-- Looks like a LEFT JOIN, behaves like an INNER JOIN
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 100;

name

amount

Asha

250

Ben

120

Moving the condition into ON filters the orders before unmatched customers are padded, so everyone survives:

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.amount > 100;

name

amount

Asha

250

Ben

120

Chitra

NULL

Dev

NULL

The rule of thumb: conditions on the preserved table (the left side of a left join) go in WHERE, and conditions on the optional table go in ON. For inner joins the two placements are equivalent.

Pitfall 2: NULL keys never match

NULL = NULL evaluates to unknown, not true, so a row whose join key is NULL matches nothing. In an inner join the row vanishes. In a left join, a NULL-keyed row on the left side comes through unmatched, while one on the right side is simply dropped. If you really want NULL to match NULL, use a null-safe comparison:

ON a.customer_id IS NOT DISTINCT FROM b.customer_id   -- standard SQL: PostgreSQL, Snowflake, BigQuery, others
-- MySQL spelling: ON a.customer_id <=> b.customer_id

Pitfall 3: Many-to-many row explosion

When the join key isn’t unique on either side, rows multiply. Two matching rows on the left and three on the right produce 2 × 3 = 6 result rows, and any sum computed afterward is inflated:

flowchart LR
    subgraph LA["table a, key = 1"]
        X1["a1"]
        X2["a2"]
    end
    subgraph LB["table b, key = 1"]
        Y1["b1"]
        Y2["b2"]
        Y3["b3"]
    end
    X1 --- Y1
    X1 --- Y2
    X1 --- Y3
    X2 --- Y1
    X2 --- Y2
    X2 --- Y3

    classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b
    class X1,X2,Y1,Y2,Y3 kept
    style LA fill:none,stroke:#9ca3af,stroke-dasharray: 4 3
    style LB fill:none,stroke:#9ca3af,stroke-dasharray: 4 3

Six lines means six rows. Before trusting any join, check that the key is unique on the side you expect to have one row per key:

SELECT customer_id, COUNT(*)
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

If that query returns rows, joining to customers will multiply your data. Also compare row counts before and after the join. A left join that returns more rows than the left table, when you expected one row per left row, is the signature of this bug.


Choosing the Right Join

When you’re unsure, start from what you need in the output:

flowchart TD
    Q{"What do you need<br/>from the result?"}
    Q -->|"Only rows that match"| I["INNER JOIN"]
    Q -->|"Everything from one side,<br/>matches optional"| L["LEFT JOIN"]
    Q -->|"Everything from both sides"| F["FULL OUTER JOIN"]
    Q -->|"Rows with no match"| A["Anti-join:<br/>NOT EXISTS"]
    Q -->|"A yes or no about a match"| S["Semi-join:<br/>EXISTS"]
    Q -->|"Every combination"| C["CROSS JOIN"]
    Q -->|"Rows related to other rows<br/>in the same table"| SJ["Self join"]
    Q -->|"Matching on a range"| N["Non-equi join"]
    Q -->|"A subquery per row,<br/>like top N per group"| LAT["LATERAL or APPLY"]

And here is how the main ones treat the rows that don’t match, plus whether they can multiply rows:

Join

Left-only rows

Right-only rows

Can repeat a row?

INNER

Dropped

Dropped

Yes

LEFT

Kept, NULL-padded

Dropped

Yes

RIGHT

Dropped

Kept, NULL-padded

Yes

FULL OUTER

Kept, NULL-padded

Kept, NULL-padded

Yes

Left anti-join

The only rows returned

Not returned

No

Semi-join

Dropped

Not returned

No

CROSS

Not applicable

Not applicable

Yes, m × n rows

The Takeaway

Joins are not magic. They are one simple loop: pair up rows, test the condition, and decide the fate of the lonely ones. The next time a join returns a surprising number of rows, don’t guess. Sketch the two tables, draw a line between every pair that matches, and color the rows by what happens to them. The bug is usually visible within a minute: a row with no partner, a key that isn’t unique, or a filter in the wrong clause.

Comments(0)

Join the discussion by logging into your account.

No comments yet. Be the first to comment!

Sanju Singh

Passionate developer sharing knowledge about modern web technologies and best practices.

Subscribe to Sanju Singh's Newsletter

Direct email dispatches when new stories are published. Zero algorithms.