
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 3Keep 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 3name | 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 3name | 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 3name | 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 3name | 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 3Both 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 3order_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 3name |
|---|
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 3size | 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 keptemployee | 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 3order_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_idPitfall 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 3Six 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!