Joins & Set Operations

Almost every real database splits its data across tables. Normalization keeps customers in one place and their orders in another so nothing is stored twice, and a join is how you put the pieces back together when you read. A join takes two tables in and gives one table out, which means its result can feed another join, a filter or an aggregate. Joins make a result wider by adding columns, while the set operators UNION, INTERSECT and EXCEPT make it longer by adding or removing rows.

The trouble is that a join can go wrong without any error. A forgotten condition returns billions of rows. A WHERE placed on the wrong table quietly turns a LEFT JOIN into an inner join. A one-to-many link multiplies a total, and a NULL key matches nothing, not even another NULL. In each case the query runs, the numbers look plausible, and they are wrong. This chapter is built around those cases, so you learn to expect them before they reach a report.

We use one small schema throughout: six employees and four departments, with one employee who belongs to no department and one department, 'Legal', that has nobody in it. Those two unmatched rows are the test case for every join type. By the end you will be able to pick the right join for a question, predict how many rows it returns, handle NULL correctly, read a query plan to see which algorithm the engine chose, and choose between a join and a set operator.

A join is a binary operator: it takes exactly two relations (tables, views, or any query result) and produces exactly one relation. Each row of the output is a combination of one row from the left input and one row from the right input, and it only exists if that pair satisfies a predicate. Joining three tables is never one big operation. It is two binary joins, one after the other.

The theory behind this comes from relational algebra, which has three operators you need for joins. Everything a join does can be described with them, and the table below maps each one to the SQL you already know.

Put them together and you get the definition of the most common join: INNER JOIN = σ_predicate(A × B). Nothing more. First imagine every possible pair, then discard the pairs that fail the predicate. Real engines never build the full cross product, because they use smarter algorithms, but the result is always exactly what that definition would give. The example below does it the slow, literal way and then lets SQL do it, so you can see both agree.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript("""
CREATE TABLE customers (id INT PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INT PRIMARY KEY,
                     customer_id INT REFERENCES customers(id),
                     amount INT);
INSERT INTO customers VALUES (1,'Asha'),(2,'Ben'),(3,'Chen');
INSERT INTO orders VALUES (10,1,50),(11,1,20),(12,2,70);
""")

cross = db.execute(
    'SELECT c.id, o.customer_id FROM customers c CROSS JOIN orders o'
).fetchall()
print('cross product rows:', len(cross))

kept = [pair for pair in cross if pair[0] == pair[1]]
print('after filter:', len(kept))

for row in db.execute("""
    SELECT c.name, o.order_id, o.amount
    FROM customers c JOIN orders o ON o.customer_id = c.id
    ORDER BY o.order_id"""):
    print(row)

3 customers × 3 orders gives 9 pairs; the predicate keeps 3

If joins are just filtered cross products, why not keep everything in one big table? Because of normalization: the practice of splitting data so each fact is stored once. Imagine a single flat orders table that repeats the customer's name and city on every row. If Asha moves, you must update every one of her order rows, and missing one leaves the data contradicting itself.

After the split, Asha's city lives in exactly one row, so there is one place to change it. The price is that a question like 'which city did order 11 ship to?' now spans two tables. Normalization splits data on write; a join puts it back together on read. That trade is the reason the join exists at all.

The second big idea is that every join result is itself a table. It has columns, rows, and no memory of how it was made. That closure property is why you can use a join result as the input to another join, wrap it in a subquery, or name it in a CTE (a named query defined with WITH) and treat it like any stored table.

WITH customer_orders AS (          -- join result, given a name
  SELECT c.id, c.name, o.amount
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
)
SELECT name, SUM(amount) AS spend   -- queried like any table
FROM customer_orders
GROUP BY name;

The CTE is a join; the outer query neither knows nor cares

A foreign key is a declared link in the schema: you tell the database that orders.customer_id must point at an existing customers.id. The database uses that declaration to reject bad data. A join condition is different. It is the query-time use of that link, where you write the predicate that says which rows belong together.

-- declared once, in the schema
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  amount      NUMERIC
);

-- used every time, in the query
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Declaring the key and joining on it are two separate acts

A foreign key is not required to join. The ON clause accepts any boolean expression over comparable values, so you can join on inequalities and computed values as easily as on matching ids. The database will not stop you, which is both the power and the danger of the feature.

-- range join: put each sale into a pricing band
SELECT s.id, b.label
FROM sales s
JOIN bands b ON s.amount BETWEEN b.min_amt AND b.max_amt;

-- computed join: match on an expression, not a stored key
SELECT a.id, b.id
FROM accounts a
JOIN archive b ON LOWER(a.email) = LOWER(b.email);

No foreign key exists in either query

Now the vocabulary. The left table is the one written before the JOIN keyword, and the right table is the one written after it. In a chain, the 'left side' means everything accumulated so far. These names only change the result for outer joins, where one side is preserved. Inner and cross joins give the same rows whichever way round you write them.

Before writing any join, name its cardinality: how many rows on one side can match one row on the other. There are four classes. Getting this wrong is the number one cause of row explosion, where a result is far bigger than either input and aggregates quietly turn into wrong numbers.

The dangerous case is joining two independent 1:many children to the same parent. For a given customer, every order is paired with every address. Two orders and two addresses give four rows, and each order's amount is counted twice. The next example shows the total going wrong with no error and no warning.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript("""
CREATE TABLE customers (id INT PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, amount INT);
CREATE TABLE addresses (addr_id INT PRIMARY KEY, customer_id INT, city TEXT);
INSERT INTO customers VALUES (1,'Asha'),(2,'Ben');
INSERT INTO orders VALUES (10,1,50),(11,1,20),(12,2,70);
INSERT INTO addresses VALUES (1,1,'Pune'),(2,1,'Goa'),(3,2,'Delhi');
""")
print('true total:', db.execute('SELECT SUM(amount) FROM orders').fetchone()[0])
n, total = db.execute("""
    SELECT COUNT(*), SUM(o.amount)
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    JOIN addresses a ON a.customer_id = c.id""").fetchone()
print('joined rows:', n)
print('joined total:', total)

Asha has 2 orders and 2 addresses, so her orders appear twice

The last distinction is the direction in which data grows. A join works horizontally: it adds columns by bringing attributes from another table alongside each row. The set operators UNION, INTERSECT, and EXCEPT work vertically: they stack or compare whole rows from queries that have the same shape. Different axis, different tool.

-- join: wider rows
SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id;

-- set operation: more rows, same two columns
SELECT id, name FROM users
UNION ALL
SELECT id, name FROM users_archive;

Same tables in spirit, opposite axes

Every join in this chapter runs against the same small database. Keeping the data fixed means each new join type can be judged against the same few numbers. The core is two tables, employees and departments. A second pair, customers and orders, is used whenever we need a one-to-many relationship.

employees holds 6 rows with the columns emp_id, name, dept_id, manager_id and salary. Two of its columns are allowed to be NULL on purpose. One employee has dept_id = NULL and belongs to no department. One employee has manager_id = NULL, which marks the CEO at the top of the reporting chain.

departments holds 4 rows. Three of them have staff. The fourth, Legal, has nobody working in it.

The many-side examples use customers(cust_id, name) and orders(order_id, customer_id, amount). Acme has two orders, Birch has one and Cobalt has none. One row on the customer side can therefore match several rows on the order side. That is exactly the situation where joins start to multiply rows.

The key declaration is the employees table. It has two foreign keys. dept_id points to departments. manager_id points back into employees itself. A foreign key column that allows NULL is what lets an employee exist with no department, or with no manager.

The script below builds the whole database in memory with Python's built-in sqlite3 module. The SQL inside is the real schema, including the CREATE TABLE employees statement. After loading the rows, it prints a row count for each table. Later examples in this section reuse the db connection created here.

The data is lopsided on purpose. Farah is an orphan employee: her dept_id is NULL, so no department claims her. Legal is a childless department: no employee points at it. Each join type treats these two rows differently. Inner joins drop both. A left join keeps Farah, a right join keeps Legal and a full join keeps both.

Three numbers are worth memorizing, because every later result is compared with them. The unmatched rows are one on each side, which is why the totals below do not simply add up to a clean table.

The method used for the rest of the chapter is short. Run the INNER JOIN first, then ask which rows were lost. The loss is exactly what the outer joins recover. The next script does this with the db connection from before. It counts the inner-join pairs and then lists the rows on each side that found no partner.

matched = db.execute("""
    SELECT e.name, d.dept_name
    FROM employees e
    INNER JOIN departments d ON e.dept_id = d.dept_id
""").fetchall()
print("matched pairs:", len(matched))

lost_emp = db.execute("""
    SELECT name FROM employees e
    WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.dept_id = e.dept_id)
""").fetchall()
lost_dept = db.execute("""
    SELECT dept_name FROM departments d
    WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id)
""").fetchall()
print("employees lost:", [r[0] for r in lost_emp])
print("departments lost:", [r[0] for r in lost_dept])

The inner join finds 5 pairs and loses one row on each side

Keep these predictions in mind. Each join type in the coming sections should produce one of these counts on the employee and department tables.

A CROSS JOIN takes two tables and pairs each row on the left with each row on the right. It never asks whether two rows belong together, so it has no ON clause at all, and writing one is a syntax error. The result is the Cartesian product of the two tables: every possible combination, nothing filtered out.

In the running schema there are 6 employees and 4 departments. Crossing them gives each employee a row for every department, including 'Legal', which has nobody in it, and including the employee who has no department. The pairing is purely mechanical, so the result says nothing about who actually works where. The example below builds a small copy of the schema in SQLite and counts the rows.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE departments (dept_id INT, dept_name TEXT);
INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'Marketing'),(4,'Legal');
CREATE TABLE employees (emp_id INT, name TEXT, dept_id INT);
INSERT INTO employees VALUES (1,'Asha',1),(2,'Ben',1),(3,'Chen',2),(4,'Dara',2),(5,'Eli',3),(6,'Fay',NULL);
''')

rows = db.execute('''
SELECT e.name, d.dept_name
FROM employees e CROSS JOIN departments d
ORDER BY e.emp_id, d.dept_id
''').fetchall()

print(len(rows))
for r in rows[:5]:
    print(r)

SELECT * FROM employees CROSS JOIN departments gives 6 x 4 rows

Asha appears four times, once per department, then Ben starts the next block of four. Fay, whose dept_id is NULL, appears four times as well, because a cross join never compares keys and so NULL never gets in the way.

The output size is exactly |A| × |B|, the left row count times the right row count. This makes CROSS JOIN the only join whose cardinality you can state without looking at the data. For an inner join the count depends on which keys match, and for a left join it is at least the left size but otherwise unknown. For a cross join, two row counts give you the answer.

Older SQL has no CROSS JOIN keyword in the FROM clause. You list the tables separated by a comma, and the engine treats that as a cross join. Because the comma looks like ordinary list punctuation, it is easy to miss. Adding a WHERE condition that links the two tables filters the product down to the matching pairs, which is exactly what an inner join does.

comma = db.execute('SELECT COUNT(*) FROM employees e, departments d').fetchone()[0]

filtered = db.execute('''
SELECT e.name, d.dept_name FROM employees e, departments d
WHERE e.dept_id = d.dept_id ORDER BY e.emp_id''').fetchall()

inner = db.execute('''
SELECT e.name, d.dept_name FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id''').fetchall()

print(comma, len(filtered), filtered == inner)

Reuses the db built above

The bare comma gives 24 rows, the comma plus a WHERE gives 5, and the result is identical to the explicit INNER JOIN. Fay and 'Legal' vanish because neither has a partner that passes the filter. The two spellings return the same rows, but the explicit one tells the reader which part is the pairing and which part is a filter.

This relationship is how joins are defined, and it goes beyond the comma syntax. Conceptually, any join starts by forming the full product of its inputs. The ON condition then keeps only the pairs where it is true. An outer join finishes by adding back the unmatched rows from the preserved side, padded with NULLs. Database engines never actually build the full product for a normal join, because they use hash, merge or indexed nested-loop algorithms to find the matches directly. The meaning of the query is still defined as if the product had been built.

CROSS JOIN is the first step with the other two steps removed. That is why it needs no ON clause: it has nothing to filter and nothing to preserve.

Most of the time you want pairs that belong together, and a cross join is a mistake. Sometimes the combinations themselves are the thing you want to generate. Three uses come up repeatedly.

The third use is the most common in reporting. Real data has gaps: a department with no sales in February simply has no February row, and a report built straight from the sales table leaves that month out. The usual fix is to build a dense scaffold of every department and month pair with a CROSS JOIN, then LEFT JOIN the real figures onto it. Pairs with no sales stay in the result, and you can fill their totals with COALESCE.

db.executescript('''
CREATE TABLE months (n INT, month TEXT);
INSERT INTO months VALUES (1,'Jan'),(2,'Feb');
CREATE TABLE sales (dept_id INT, month TEXT, total INT);
INSERT INTO sales VALUES (1,'Jan',100),(1,'Feb',150),(2,'Jan',80);
''')

rows = db.execute('''
SELECT d.dept_name, m.month, COALESCE(s.total, 0)
FROM departments d
CROSS JOIN months m
LEFT JOIN sales s ON s.dept_id = d.dept_id AND s.month = m.month
ORDER BY d.dept_id, m.n''').fetchall()

print(len(rows), 'rows')
for r in rows:
    print(*r)

Dense report scaffold: 4 departments x 2 months = 8 rows

The sales table has only three rows, yet the report has all eight department and month combinations. The ON clause of the LEFT JOIN must match on both dept_id and month. If it matched on only one of them, each scaffold row would pick up several sales rows and the totals would be wrong.

A plain CROSS JOIN evaluates both sides independently, so the right side cannot refer to columns of the left. CROSS JOIN LATERAL in PostgreSQL and MySQL, or CROSS APPLY in SQL Server, changes this. The right side becomes a correlated subquery that is evaluated once for each left row and can use that row's values. It still returns pairs, but only the pairs the subquery produces for that row. It is the standard tool for the top-N-per-group problem, where a window function would be heavier than needed.

SELECT d.dept_name, top.name, top.salary
FROM departments d
CROSS JOIN LATERAL (
  SELECT e.name, e.salary
  FROM employees e
  WHERE e.dept_id = d.dept_id
  ORDER BY e.salary DESC
  LIMIT 3
) AS top;

Top 3 earners per department. Not runnable in SQLite.

For each department, the subquery looks only at that department's employees, sorts them by salary and keeps three. A department that returns no rows from the subquery, such as 'Legal', disappears from the result, because crossing with an empty set gives an empty set. To keep such departments, use LEFT JOIN LATERAL (...) AS top ON TRUE.

The costly version of this join is the one you did not mean to write. Forget the ON clause or the WHERE link in a comma-style query and the engine does exactly what it was told: it returns every combination. Two tables of 100,000 rows each would produce ten billion rows, enough to exhaust memory or disk and stall the server. The accidental query is written the same way as an intentional one, so nothing in the text tells a reviewer which it is.

An INNER JOIN looks at every pair made from one row on the left and one row on the right, and evaluates the ON predicate for that pair. The pair appears in the output only when the predicate is TRUE. If the predicate is FALSE the pair is dropped, and if it is UNKNOWN (which happens whenever a NULL takes part in a comparison) the pair is dropped too.

The running schema has 6 employees and 4 departments. One employee, Fay, has a NULL dept_id, and the Legal department has no employees. The query below joins them on the department key. Five employees have a department that exists, so five rows come back.

import sqlite3

con = sqlite3.connect(':memory:')
con.executescript("""
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name TEXT);
CREATE TABLE employees (emp_id INT PRIMARY KEY, name TEXT, dept_id INT, manager_id INT);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'Support'),(40,'Legal');
INSERT INTO employees VALUES
  (1,'Asha',10,NULL),(2,'Ben',10,1),(3,'Chen',20,1),
  (4,'Dara',20,3),(5,'Eli',30,1),(6,'Fay',NULL,1);
""")

rows = con.execute("""
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id
""").fetchall()
for name, dept in rows:
    print(name, dept)
print(len(rows), 'rows')

Inner join of the running example

Two rows vanish without any warning. Fay never appears, because NULL = anything is UNKNOWN and no department can satisfy it. Legal never appears, because no employee row has dept_id = 40. An inner join returns only the intersection of what the two sides can prove they share, and it says nothing about what was left out.

The word INNER is optional. A bare JOIN means an inner join, so JOIN and INNER JOIN are the same operation. Writing INNER only documents your intent for the reader. Many teams write it because it makes the contrast with LEFT JOIN obvious in a long query.

An inner join is also commutative and associative. A JOIN B produces the same rows as B JOIN A, and (A JOIN B) JOIN C produces the same rows as A JOIN (B JOIN C). The optimizer therefore reorders any chain of inner joins however it likes, usually starting from the most selective table. The order you write in FROM is for readers and does not choose the plan. Only the column order of SELECT * can change.

def q(sql):
    return sorted(con.execute(sql).fetchall())

a = q('SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id')
b = q('SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id')
c = q('SELECT e.name, d.dept_name FROM departments d JOIN employees e ON e.dept_id = d.dept_id')
print(len(a), a == b, a == c)

Dropping INNER and swapping the tables changes nothing

The number of rows an inner join returns depends on the data, not on the table sizes alone. It can be smaller than both inputs, equal to one of them, or much larger than either.

In a one-to-many join, each row on the one side is repeated once per matching row on the many side. The customer's columns are copied onto every one of that customer's orders. When duplicate keys exist on both sides, the effect compounds: for any one key value, the output holds every combination, so 3 matching left rows times 2 matching right rows gives 6 rows.

con.executescript("""
CREATE TABLE l (k INT, v TEXT);
CREATE TABLE r (k INT, w TEXT);
INSERT INTO l VALUES (1,'a1'),(1,'a2'),(1,'a3'),(2,'a4');
INSERT INTO r VALUES (1,'b1'),(1,'b2'),(3,'b3');
""")
rows = con.execute('SELECT l.v, r.w FROM l JOIN r ON l.k = r.k ORDER BY l.v, r.w').fetchall()
print(len(rows), 'rows from 4 left and 3 right')
for v, w in rows:
    print(v, w)

Key 1 appears 3 times on the left and 2 times on the right

Keys 2 and 3 have no partner, so they contribute nothing. Key 1 alone produces 6 rows, more than either input has for that key. This is why SUM over a many-to-many join returns totals that are exact multiples of the truth.

An inner join does not need an equality. The ON clause accepts any boolean expression, and a pair is kept whenever that expression is TRUE. A band join (or range join) uses an inequality, most often BETWEEN, to place a value in a bucket. Here each sale is matched to the pricing band whose range contains its amount.

con.executescript("""
CREATE TABLE sales (sale_id INT, amount INT);
CREATE TABLE bands (band TEXT, min_amt INT, max_amt INT);
INSERT INTO sales VALUES (1,50),(2,250),(3,900),(4,5000);
INSERT INTO bands VALUES ('small',0,99),('medium',100,999),('large',1000,9999);
""")
for row in con.execute("""
SELECT s.sale_id, s.amount, b.band
FROM sales s
JOIN bands b ON s.amount BETWEEN b.min_amt AND b.max_amt
ORDER BY s.sale_id
"""):
    print(*row)

Band join: an inner join with an inequality predicate

If the bands overlapped, a sale could fall into two of them and would appear twice, which is the multiplication rule again. If a sale fell into no band, it would vanish. A band join is only as safe as the ranges you store.

The other common shape is a multi-column join. When a table's identity is a composite key, the join must compare every column of that key. In a multi-tenant system the user id is unique only inside one tenant, so ON a.tenant_id = b.tenant_id AND a.user_id = b.user_id is the complete key. The next example drops the tenant condition and compares the result with the full key.

con.executescript("""
CREATE TABLE users (tenant_id INT, user_id INT, name TEXT);
CREATE TABLE settings (tenant_id INT, user_id INT, plan TEXT);
INSERT INTO users VALUES (1,1,'Ann'),(1,2,'Bo'),(2,1,'Cy');
INSERT INTO settings VALUES (1,1,'pro'),(2,1,'free'),(1,2,'free');
""")
wrong = 'SELECT u.name, s.plan FROM users u JOIN settings s ON u.user_id = s.user_id ORDER BY u.name, s.plan'
right = '''SELECT u.name, s.plan FROM users u JOIN settings s
          ON u.tenant_id = s.tenant_id AND u.user_id = s.user_id
          ORDER BY u.name, s.plan'''
print('missing tenant column:', con.execute(wrong).fetchall())
print('full key:', con.execute(right).fetchall())

Two tenants both have a user_id of 1

With the tenant column missing, Ann is paired with tenant 2's settings and Cy with tenant 1's. Five rows come back instead of three. The many-to-many row multiplication described under 'Keyword, ordering, and row counts' is at work here, but the extra rows are also another customer's data.

Pick an inner join when a missing match means the row is irrelevant to the question you are asking. 'Which employees work in which department?' has no use for an employee without a department. 'Total sales per pricing band' has no use for a sale that falls in no band. In both cases the lost rows are not a loss, because they were never part of the answer.

An inner join throws away any row that finds no partner. A LEFT JOIN refuses to. Every row of the table written before the JOIN keyword, the left table, appears in the result at least once. When a left row has no match on the right, the engine still emits it and fills every right-table column with NULL. This is called padding.

The sample data has six employees and four departments. Fay belongs to no department (her dept_id is NULL), and Legal has no employees. Run the query below and watch what happens to Fay. The show helper is reused by later examples, so the setup lives in this first block.

import sqlite3
con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name TEXT)')
con.execute('CREATE TABLE employees (emp_id INT PRIMARY KEY, name TEXT, dept_id INT)')
con.executemany('INSERT INTO departments VALUES (?, ?)',
                [(10, 'Engineering'), (20, 'Sales'), (30, 'Marketing'), (40, 'Legal')])
con.executemany('INSERT INTO employees VALUES (?, ?, ?)',
                [(1, 'Asha', 10), (2, 'Ben', 10), (3, 'Chen', 20),
                 (4, 'Dana', 20), (5, 'Eli', 30), (6, 'Fay', None)])

def show(sql):
    rows = con.execute(sql).fetchall()
    for row in rows:
        print(row)
    print('rows:', len(rows))

show('SELECT e.name, d.dept_name '
     'FROM employees e LEFT JOIN departments d '
     'ON e.dept_id = d.dept_id ORDER BY e.emp_id')

SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id

Six employees in, six rows out, and exactly one of them carries dept_name = NULL. An inner join would have returned five rows and silently lost Fay. Legal does not appear at all, because it sits on the right side and nothing preserves it.

A LEFT JOIN gives you one firm guarantee: the output has at least as many rows as the left table. It equals the left row count only when every left row matches at most once. The catch is that preserving all left rows is not the same as preserving the left row count. A left row with three matches shows up three times, exactly as in an inner join.

So the output size is the sum over all left rows of max(1, matches). Below, Ana has two orders, Bo has one, and Cy has none. Three customers go in and four rows come out.

The NULL that padding puts in d.dept_name is indistinguishable from a NULL that was actually stored there. The result set carries no flag saying a value was invented. To tell a real match from padding, test a right-table column that can never be NULL when a match exists, usually its primary key.

COUNT(column) skips NULL values, which is why it counts matches only. SUM ignores NULL inputs too, but when every input is NULL it returns NULL rather than 0. A report that shows blank cells where it should show zero spend is this trap. Wrap the sum in COALESCE.

LEFT JOIN is not commutative. A LEFT JOIN B preserves A, while B LEFT JOIN A preserves B. Swapping the tables changes which side is protected and therefore which unmatched rows survive. Here the employee and department tables trade places.

show('SELECT d.dept_name, e.name '
     'FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id '
     'ORDER BY d.dept_id, e.emp_id')

departments is now the left table

The count is again six, but only by coincidence. Fay is gone, and the empty Legal department has taken her place. Compare the contents, not just the totals.

The same padding gives you the anti-join idiom. Add WHERE right.key IS NULL after a LEFT JOIN and only the left rows with no partner remain, because only those rows were padded. Filter on the right table's primary key, which is never NULL in a real match.

show('SELECT c.name '
     'FROM customers c LEFT JOIN orders o ON o.cust_id = c.cust_id '
     'WHERE o.order_id IS NULL')

customers who have never ordered

A RIGHT OUTER JOIN is the mirror image of the LEFT JOIN you just met. Every row of the table written after the JOIN keyword survives. When a right row has no partner on the left, the engine still emits it and fills every left-table column with NULL. (OUTER is optional, so RIGHT JOIN and RIGHT OUTER JOIN mean the same thing.)

In our running schema, the right-hand table is departments, and 'Legal' is the department nobody belongs to. An INNER JOIN would drop it. A RIGHT JOIN keeps it and pads the employee side with NULL.

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d
  ON e.dept_id = d.dept_id;

6 rows: 5 matched pairs plus the empty department

Notice what is missing: the employee whose dept_id is NULL. That employee sits on the left, has no match, and the left side is not the preserved one here, so they disappear. The row count is 6 because there are 5 matched pairs and 1 unmatched right row. As with LEFT JOIN, 'preserves every right row' does not mean 'exactly one output row per right row': a department with three employees still produces three rows.

RIGHT JOIN adds no expressive power. For any predicate p, A RIGHT JOIN B ON p produces exactly the same rows as B LEFT JOIN A ON p. The only visible difference appears with SELECT *, where the columns come out in the order the tables are written, so the two forms list their columns in opposite order. If you name your columns explicitly, as you should, the results are indistinguishable.

You can check the claim without a database. The sketch below implements both joins as plain loops over the sample data, then compares them. The right join walks the right table and pads the left side. The left join is run with the tables swapped, and its pairs are flipped back.

Because the two forms are interchangeable, the question becomes one of readability, and readability is why most teams ban RIGHT JOIN in their style guides. A query is read top to bottom, and the table in FROM is naturally taken to be the subject of the report. A LEFT JOIN confirms that expectation: the first table is the one that is kept. A RIGHT JOIN contradicts it, and the reader must notice the keyword and mentally flip the query before they know which rows are guaranteed to appear.

The confusion grows with every table you add. Joins are evaluated left to right, so in A JOIN B JOIN C the 'left side' of the second join is not just B. It is the entire result accumulated so far, (A JOIN B). A RIGHT JOIN in position three therefore preserves table C while quietly making the whole earlier join the optional, NULL-padded side.

Rewriting such a chain means reversing it so the preserved table comes first. Write the table you want to keep in FROM, then attach everything else with LEFT JOINs, in the order each table depends on the one before it.

Mixing both directions in one query is a stronger warning sign still. If one join keeps the left side and another keeps the right, the author may really have wanted both sides kept, which is what a FULL OUTER JOIN does, and the next section covers it. Treat a LEFT-and-RIGHT mixture as a prompt to ask whether the question is really about unmatched rows on both sides.

There is one legitimate niche. Generated SQL, such as ORM output or a query assembled by a reporting tool, often arrives with a FROM clause you are not allowed to reorder. If you need to append one more table whose rows must all survive, a RIGHT JOIN at the end lets you do that without touching the generated prefix. Outside that case, prefer LEFT.

Portability is therefore a weak argument in either direction today, but it is one more reason that LEFT JOIN, which every engine has always had, is the safer default.

LEFT JOIN rescues the unmatched rows of one table and RIGHT JOIN rescues the other's. FULL OUTER JOIN rescues both at once. The result is built from three groups of rows: every matched pair, then every unmatched left row padded with NULL in the right-hand columns, then every unmatched right row padded with NULL in the left-hand columns. Nothing from either input is thrown away.

The sample data was built with one asymmetry on each side, and this is where both of them show up. Fay belongs to no department, and Legal holds nobody. An inner join loses both, a LEFT join recovers only Fay, a RIGHT join recovers only Legal, and the full join keeps them together.

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d
  ON e.dept_id = d.dept_id;
-- 7 rows: 5 matched, (Fay, NULL), (NULL, Legal)

Standard syntax, as PostgreSQL, SQL Server and Oracle accept it. OUTER is optional: FULL JOIN means the same thing.

The row count is not fixed by the table sizes alone. A full join always returns at least as many rows as the larger input, because every row of both tables appears at least once. If one key matches several rows on the other side, those pairs multiply exactly as they do in an inner join, so the total can go above 7 on other data.

Swap the two tables in a LEFT join and you get a different result, because the preserved side changes. Swap them in a FULL join and the row content stays identical: the same pairs, the same orphans, the same padding. Only the order of the columns in a SELECT * flips, since the table written first now supplies the first columns. That makes FULL the one outer join where it genuinely does not matter which table you wrote first.

A full join hands you one flat list, and the three groups look alike except for where the NULLs sit. Almost every real use starts by labelling each row with the group it belongs to. The label comes from asking which side failed to supply a row: if the left side is all NULL, the row exists only on the right, and the other way around.

In SQL this is a single CASE expression, and it is the core of any reconciliation report: CASE WHEN a.k IS NULL THEN 'only in B' WHEN b.k IS NULL THEN 'only in A' ELSE 'both' END. The order of the branches matters. A row cannot be NULL on both sides, so the first two tests never collide, and anything that survives them is a real match.

The second problem is the join key itself. For a matched row both sides carry the key. For an orphan only one side does, and it may be either one, so SELECT e.dept_id shows NULL for every department-only row and SELECT d.dept_id shows NULL for every employee-only row. Neither column is safe on its own. Use COALESCE(a.key, b.key) AS key, which takes whichever side actually has a value.

SELECT COALESCE(e.dept_id, d.dept_id) AS k,
       CASE WHEN e.emp_id  IS NULL THEN 'only in departments'
            WHEN d.dept_id IS NULL THEN 'only in employees'
            ELSE 'both' END AS side,
       e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

One merged key column and one label column. Fay's merged key stays NULL because her employee row has no department at all.

Once rows carry a label, the report is easy to finish. Count rows per label for a summary, filter on the label to list the orphans, or look inside the both rows and compare the remaining columns to find records that exist on both sides but disagree.

MySQL has no FULL OUTER JOIN. The keyword is a syntax error there, and older SQLite versions (before 3.39) lack it too. You build it from the two outer joins you do have. The result is the union of the left-preserving half and the right-preserving half, and the only care needed is the matched rows, which both halves produce.

(SELECT e.name, d.dept_name
   FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id)
UNION
(SELECT e.name, d.dept_name
   FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id);

Simple version. UNION, not UNION ALL, removes the 5 matched rows that both halves return.

The matched pairs show up once in each half, so plain UNION ALL would report them twice. UNION removes the doubles by comparing whole rows. That works here, but it has two costs. The engine must sort or hash the entire combined result to find duplicates. Worse, it cannot tell a double from a genuine duplicate: two different employees who share the same projected values come out as one row. A reconciliation report that silently loses rows is the worst kind of bug.

The safer form avoids duplicates by construction. Take the whole left join, then add only the right-side rows that found no partner, which is an anti-join, and stack them with UNION ALL. No comparison work is done, and no real row is ever collapsed.

SELECT e.name, d.dept_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION ALL
SELECT e.name, d.dept_name
FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

The WHERE here belongs to the anti-join half and is correct. Test a primary key, not the join key.

The runnable example below uses the safer form, so it works on any SQLite as well as in MySQL. It is wrapped in a view named full_join so the later examples can reuse it. The view also keeps both departments keys and the employee primary key, for the labelling and filtering shown afterwards.

Seven rows, as predicted: five pairs, then Legal with no employee, then Fay with no department. Now the labelling from the previous page can run on the same view.

A full join is the natural tool whenever two collections should describe the same set of things and you need to see every disagreement. Neither side is the subject, so neither side may lose rows. That is why it sits behind most reconciliation work.

The both rows deserve a second look in every one of these. Matching on the key only proves that the record exists on both sides, so compare the remaining columns of those rows to catch values that changed on the way.

Padded rows have NULL in all the columns of the missing side. A WHERE filter runs after the join and compares those NULLs, and a comparison with NULL is never true, so the padded rows are discarded. Filtering on a column of either table therefore throws away one group of orphans, or both, and the query quietly turns into an inner-ish join. The query still runs and returns plausible rows, so nothing warns you.

-- Drops every orphan: padded rows fail the comparison
SELECT * FROM employees e
FULL JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Sales';

-- Filter one side first, then join; the other side's rows survive as orphans
SELECT * FROM employees e
FULL JOIN (SELECT * FROM departments WHERE dept_name = 'Sales') d
  ON e.dept_id = d.dept_id;

Putting the condition in the ON clause also stops it from deleting rows, but it changes what counts as a match rather than what is shown.

The same effect shows on the sample data. Applying the filter to the finished result keeps only the two Sales employees. Filtering the department side before the join keeps every employee, with a department only where the Sales row matched.

A nested-loop join walks the outer table and, for each row, searches the inner one. That is enough to find unmatched outer rows, but an inner row is visited many times and no step is ready to say 'this one never matched anything'. Because of that, FULL OUTER JOIN usually cannot use a nested-loop plan. Engines pick a hash join or a merge join instead, and PostgreSQL even refuses a full join whose condition is neither hash-joinable nor merge-joinable. One consequence is that a full join on an inequality or a function-wrapped condition may fail or run slowly.

A self join is any join where both sides are the same table. SQL has no special keyword for it. You write an ordinary JOIN and name the table twice. The trick is that each mention plays a different role: in an employee table, one copy is "the employee" and the other is "that employee's manager".

Because both copies have identical column names, aliases are mandatory. Without e and m, a reference to name or emp_id is ambiguous and the engine refuses to run the query. The alias is also how you tell the reader which role a column belongs to.

The classic use is a manager lookup. Each employee row stores only a manager_id, which is a foreign key back into the same table. To turn that number into a name, you join the table to itself and match the employee's manager_id against the manager copy's emp_id.

The program below builds a small version of the running schema (six employees, one in no department, one CEO with no manager) and runs the lookup. It also counts what an INNER JOIN would have returned.

import sqlite3
con = sqlite3.connect(':memory:')
con.executescript('''
CREATE TABLE employees (emp_id INT PRIMARY KEY, name TEXT, dept_id INT, manager_id INT);
INSERT INTO employees VALUES
  (1,'Asha',10,NULL),(2,'Ben',10,1),(3,'Chen',20,1),
  (4,'Dara',10,2),(5,'Esi',20,3),(6,'Farid',NULL,2);
''')
sql = '''SELECT e.name, m.name
         FROM employees e {kind} JOIN employees m
           ON e.manager_id = m.emp_id
         ORDER BY e.emp_id'''
for emp, mgr in con.execute(sql.format(kind='LEFT')):
    print(f"{emp:<6} -> {mgr or 'NULL'}")
inner = con.execute(sql.format(kind='INNER')).fetchall()
print('inner rows:', len(inner), '- the CEO is gone')

Manager lookup with LEFT JOIN, compared with INNER

The word that matters here is LEFT. The CEO's manager_id is NULL, and a NULL key never matches anything in a join predicate. An INNER JOIN therefore discards Asha silently, leaving five rows instead of six. A LEFT JOIN keeps every employee and pads the manager column with NULL where no manager exists.

The second classic self join builds pairs: every two colleagues in the same department, for a pairing exercise or a duplicate check. Joining a table to itself on dept_id alone gives every combination, including each person paired with themselves and each pair listed in both orders. The extra condition a.emp_id < b.emp_id fixes both problems at once.

SELECT a.name, b.name
FROM employees a
JOIN employees b
  ON a.dept_id = b.dept_id
 AND a.emp_id < b.emp_id;

Each unordered pair exactly once

Why does < work? For any two different people x and y, exactly one of x.emp_id < y.emp_id and y.emp_id < x.emp_id is true, so only one ordering survives. For a person paired with themselves, x.emp_id < x.emp_id is false, so self-pairs vanish.

The two near-miss operators fail in opposite ways. Using <> instead of < yields each pair twice, once as (x, y) and once as (y, x). Using = yields every row paired with itself and nothing else. The program below runs all three against the same data.

base = '''SELECT a.name, b.name FROM employees a
          JOIN employees b ON a.dept_id = b.dept_id
           AND a.emp_id {op} b.emp_id
          ORDER BY a.emp_id, b.emp_id'''
for op in ('<', '<>', '='):
    rows = con.execute(base.format(op=op)).fetchall()
    print(f'{op:<2} {len(rows)} rows, e.g. {rows[:2]}')

Same join, three operators on emp_id

Notice that Farid never appears in any of the three results. His dept_id is NULL, and the a.dept_id = b.dept_id part of the predicate never matches NULL, even against himself. That is why the = variant returns five rows rather than six.

A self join can also compare a row with its neighbour. Suppose daily holds one value per day and you want the change since the previous day. Join the table to itself so each row t meets the row p whose day is one earlier, then subtract. This was the standard idiom before window functions existed.

SELECT t.day, t.value - p.value AS delta
FROM daily t
LEFT JOIN daily p ON p.day = t.day - 1;

Delta with a self join

The LEFT JOIN matters again: the first day has no predecessor, and with an inner join it would disappear. The join also depends on days being consecutive. If a day is missing, the row after the gap finds no partner and gets a NULL delta.

The modern alternative is LAG() (or LEAD() for the next row). It reads the neighbouring row directly in sorted order, so it needs no second copy of the table, no join condition and no assumption that days are consecutive. The program below runs both on data with a gap at day 4.

con.executescript('''
CREATE TABLE daily (day INT, value INT);
INSERT INTO daily VALUES (1,100),(2,110),(3,105),(5,130);
''')
join_sql = '''SELECT t.day, t.value - p.value
              FROM daily t LEFT JOIN daily p ON p.day = t.day - 1
              ORDER BY t.day'''
lag_sql = '''SELECT day, value - LAG(value) OVER (ORDER BY day)
             FROM daily ORDER BY day'''
print('self join:', con.execute(join_sql).fetchall())
print('LAG      :', con.execute(lag_sql).fetchall())

Day 4 is missing from the data

The two disagree on day 5. The self join asked for "exactly yesterday" and found nothing, while LAG asked for "the previous row that exists" and found day 3. Neither is wrong, but you must decide which question you mean. If you want strict calendar adjacency with gaps shown as NULL, the join says so explicitly. For "previous available row", LAG is shorter and clearer.

A hierarchy stored in one table is walked one level per join. To list an employee, their manager and their manager's manager, you chain two self joins with three aliases. Each extra level adds another join, and every one of them must be a LEFT JOIN or the shallower chains drop out.

SELECT e.name, m1.name AS boss, m2.name AS boss_of_boss
FROM employees e
LEFT JOIN employees m1 ON e.manager_id  = m1.emp_id
LEFT JOIN employees m2 ON m1.manager_id = m2.emp_id;

Two levels up needs two joins

This stops working when you do not know the depth in advance. N levels need N joins, and the query text must change whenever the org chart gets deeper. For arbitrary depth, use a recursive CTE (WITH RECURSIVE): it starts from the root and repeatedly joins the rows found so far back to the table.

cte = '''WITH RECURSIVE chain(emp_id, name, depth) AS (
  SELECT emp_id, name, 0 FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, c.depth + 1
  FROM employees e JOIN chain c ON e.manager_id = c.emp_id)
SELECT name, depth FROM chain ORDER BY depth, name'''
for name, depth in con.execute(cte):
    print('  ' * depth + name)

Any depth, one query

Cost is the other thing to watch. A self join reads the same table twice, so on a large table that can mean two full scans plus the join work itself. Only a plan that shares one scan between both sides avoids this, and you cannot count on the optimizer doing so. An index on the join key (manager_id or the lookup column) keeps each side cheap, and a window function sidesteps the problem for adjacent-row cases.

The last gotcha is cosmetic but nasty. SELECT * on a self join returns every column of both copies, so the result has duplicate column names. The program below shows the header of such a query.

cur = con.execute('''SELECT * FROM employees e
                     JOIN employees m ON e.manager_id = m.emp_id''')
print([d[0] for d in cur.description])

Duplicate names in the result header

Application code that reads columns by name will get whichever name the driver keeps, usually the last, so the employee's name may silently turn into the manager's. Always project explicit, aliased columns such as e.name AS employee and m.name AS manager.

Every join needs a rule for pairing rows, and SQL gives you several spellings of it. They are not equally safe. This section walks through them from most flexible to most dangerous, using a small dataset: six employees (one, Fay, has no department), four departments (Legal has nobody) and four orders.

The ON clause accepts any boolean expression. Plain equality is the common case, but inequalities, OR, function calls and multi-column conjunctions are all legal. A pair of rows is kept only when the expression evaluates to TRUE; FALSE and UNKNOWN both discard it. The example below sets up the dataset and joins on equality plus a salary threshold in one predicate.

import sqlite3
db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE departments (dept_id INT, dept_name TEXT);
CREATE TABLE employees (emp_id INT, name TEXT, dept_id INT, salary INT);
CREATE TABLE orders (order_id INT, emp_id INT, year INT);
INSERT INTO departments VALUES (10,'Eng'),(20,'Sales'),(30,'Ops'),(40,'Legal');
INSERT INTO employees VALUES (1,'Ana',10,90),(2,'Ben',10,70),(3,'Cy',20,80),
  (4,'Di',20,60),(5,'Eli',30,75),(6,'Fay',NULL,95);
INSERT INTO orders VALUES (1,1,2024),(2,1,2023),(3,3,2024),(4,4,2023);
''')

def show(sql):
    for row in db.execute(sql):
        print(row)

show('''SELECT e.name, d.dept_name
FROM employees e JOIN departments d
  ON e.dept_id = d.dept_id AND e.salary >= 75
ORDER BY e.emp_id''')

Equality and a range test combined in one ON

The same slot could hold ON e.dept_id = d.dept_id OR e.salary > 90, or ON LOWER(a.code) = LOWER(b.code). Keep in mind that a function wrapped around a column usually stops the engine from using an index on it.

When both tables name the key column identically, USING (dept_id) means the same as an equality test on that pair. It also changes the output: the two columns collapse into a single merged column. Because only one dept_id exists in the result, the usual style is to write it unqualified, as SELECT dept_id. PostgreSQL, MySQL and SQLite also accept a qualified e.dept_id with USING, but a few engines (Oracle, for example) reject a qualified reference to a USING column, so the unqualified form is the portable habit.

In an outer join the merged column holds the COALESCE of both sides, meaning whichever side has a value. In a FULL join, the unmatched Legal department therefore shows 40 rather than NULL. In a LEFT join, as below, the left value is always present when the left row has one, and Fay's key stays NULL because she has none.

show('''SELECT dept_id, name, dept_name
FROM employees LEFT JOIN departments USING (dept_id)
ORDER BY emp_id''')

One merged dept_id column, referenced without a qualifier

NATURAL JOIN goes one step further and joins on every column pair that happens to share a name. You never write the predicate, so the predicate can change without any edit to your query. Adding a created_at column to both tables makes it a second join condition, and since the timestamps differ, the join quietly stops matching.

q = 'SELECT COUNT(*) FROM employees NATURAL JOIN departments'
print(db.execute(q).fetchone()[0])
db.executescript('''
ALTER TABLE employees ADD COLUMN created_at TEXT;
ALTER TABLE departments ADD COLUMN created_at TEXT;
UPDATE employees SET created_at = '2024-01-05';
UPDATE departments SET created_at = '2023-06-01';
''')
print(db.execute(q).fetchone()[0])

Same query text, different result after a schema change

The old style lists tables with commas and puts the condition in WHERE. It works for inner joins, because a comma join is a cross join that WHERE then filters. It cannot express outer joins portably. The vendor workarounds, Oracle's (+) marker and SQL Server's *= operator, are deprecated and should not appear in new code.

For an inner join, a predicate in ON and the same predicate in WHERE give identical results. A row pair that fails the test is dropped either way, and there are no preserved rows to protect. The example runs the same year filter both ways and compares the two result lists.

With an outer join the two placements diverge, because the join happens in stages. ON decides which pairs match. Then the engine pads unmatched preserved rows with NULLs. Only after that does WHERE run, on the padded result.

Consider the request "all employees, with their 2024 orders". Putting o.year = 2024 in ON keeps every employee: those without a 2024 order simply show NULL. Moving the same test to WHERE evaluates NULL = 2024 on padded rows, which is UNKNOWN, so those employees vanish and the LEFT JOIN behaves like an inner join. The last query shows the one deliberate use of WHERE after an outer join: testing a right-side key for NULL to find unmatched left rows (an anti-join).

base = 'SELECT e.name, o.order_id FROM employees e LEFT JOIN orders o '
queries = [
    ('ON:', base + 'ON o.emp_id = e.emp_id AND o.year = 2024 ORDER BY e.emp_id'),
    ('WHERE:', base + 'ON o.emp_id = e.emp_id WHERE o.year = 2024 ORDER BY e.emp_id'),
    ('anti:', 'SELECT e.name FROM employees e LEFT JOIN orders o '
              'ON o.emp_id = e.emp_id WHERE o.order_id IS NULL ORDER BY e.emp_id'),
]
for label, sql in queries:
    print(label, db.execute(sql).fetchall())

Filter in ON, filter in WHERE, and the intentional IS NULL test

SQL logic has three outcomes, not two: TRUE, FALSE and UNKNOWN. A NULL means 'a value exists but we do not know it', so comparing it to anything, including another NULL, cannot be answered with yes or no. NULL = NULL therefore evaluates to UNKNOWN, not TRUE. The practical result is that a NULL join key never matches anything, not even another NULL key.

In the running schema, Fay is the employee whose dept_id is NULL. Her key cannot equal any department's key, so on an equality predicate she pairs with no row, whatever the join type. An outer join can still keep her, but only by padding, which is a different mechanism that the next page covers. The first example sets up the tables and shows all three facts.

import sqlite3
con = sqlite3.connect(":memory:")
q = lambda sql: con.execute(sql).fetchall()
con.execute("CREATE TABLE departments (dept_id INT, dept_name TEXT)")
con.executemany("INSERT INTO departments VALUES (?, ?)", [(10, 'Eng'), (20, 'Sales'), (30, 'Ops'), (40, 'Legal')])
con.execute("CREATE TABLE employees (emp_id INT, name TEXT, dept_id INT)")
con.executemany("INSERT INTO employees VALUES (?, ?, ?)", [(1, 'Ana', 10), (2, 'Ben', 10), (3, 'Cy', 20), (4, 'Di', 30), (5, 'Eli', 20), (6, 'Fay', None)])
print(q("SELECT NULL = NULL, NULL IS NULL, 1 = NULL"))
print(q("SELECT COUNT(*) FROM employees e JOIN departments d ON e.dept_id = d.dept_id"))
print(q("SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id WHERE e.name = 'Fay'"))

SQLite shows UNKNOWN as None and TRUE as 1

The first line shows that NULL = NULL and 1 = NULL both come back as None, which is how SQLite displays UNKNOWN, while IS NULL gives a real TRUE. The inner join finds only 5 matches out of 6 employees. Fay is missing, and 'Legal' is missing from the other side too. The third line shows the left join keeping Fay, but only as a row whose department columns are padded with NULL.

A join predicate is a filter over candidate row pairs, and it has one rule: only pairs where the predicate is TRUE survive. A pair that evaluates to FALSE is rejected, and so is a pair that evaluates to UNKNOWN. A NULL key always produces UNKNOWN, which is why it behaves exactly like a mismatch.

An outer join adds one more step after the predicate. Every row from the preserved side that found no TRUE partner is added back, with NULL in all the columns of the other side. That is why Fay shows up in a left join: she lost every comparison, then was re-added with a NULL department name. She was never matched.

When NULL keys should be treated as equal, use a NULL-safe equality operator. It returns TRUE when both sides are NULL, TRUE when both hold the same value, and FALSE otherwise, so it never produces UNKNOWN. The standard spelling is IS NOT DISTINCT FROM, and MySQL has the shorthand <=>. SQLite spells it plain IS.

The last row is the portable fallback. It is correct, but the OR usually stops the engine from using an index on the key, so large tables may fall back to slower plans. Prefer the native operator where one exists. The next example runs a small pair of tables, l and r, each holding one NULL key, through every form.

The left join with plain = shows a row (None, None), but that is only the left NULL being padded, not a match. The plain inner join finds just (2, 2). Both NULL-safe forms add the genuine (None, None) pair and give identical results.

A common shortcut is to wrap both keys in COALESCE with a placeholder value, which turns every NULL into the same number. It works only when the sentinel can never appear as a real key. The next example breaks it by giving table t a real key of -1.

After a left join, a NULL in a right-table column can mean two different things: the right row was missing and the engine padded it, or the right row matched and its stored value really is NULL. To test for 'no match', check a right-table column that can never be NULL in a real row, usually its primary key. Counting works the same way: COUNT(*) counts every output row, including padded ones, while COUNT(right_col) skips NULLs and so skips padding.

con.execute("CREATE TABLE customers (cust_id INT, name TEXT)")
con.execute("CREATE TABLE orders (order_id INT, cust_id INT, amount INT)")
con.executemany("INSERT INTO customers VALUES (?, ?)", [(1, 'Asha'), (2, 'Bo'), (3, 'Cai')])
con.executemany("INSERT INTO orders VALUES (?, ?, ?)", [(100, 1, 50), (101, 1, 30), (102, 2, None)])
print(q("SELECT c.name, COUNT(*), COUNT(o.order_id) FROM customers c LEFT JOIN orders o ON o.cust_id = c.cust_id GROUP BY c.name ORDER BY c.name"))
print(q("SELECT c.name FROM customers c LEFT JOIN orders o ON o.cust_id = c.cust_id WHERE o.amount IS NULL ORDER BY c.name"))
print(q("SELECT c.name FROM customers c LEFT JOIN orders o ON o.cust_id = c.cust_id WHERE o.order_id IS NULL"))

Bo's only order has a stored NULL amount

Cai has no orders, yet COUNT(*) reports 1 for her because the padded row is still a row. COUNT(o.order_id) reports the true 0. Testing o.amount IS NULL wrongly returns Bo as well, since his matched order has a stored NULL amount. Testing the primary key o.order_id IS NULL finds only Cai.

Joins treat NULLs as unequal, but UNION, INTERSECT, EXCEPT, GROUP BY and DISTINCT treat two NULLs as the same value when deciding whether rows are duplicates. The final example runs the same l and r tables through all of them.

The two NULLs collapse into one in the UNION. INTERSECT keeps NULL as a common row, which the join on page 3 could not do with plain =. EXCEPT removes it from the left side because the right side has one too. GROUP BY puts both NULLs in one group of size 2, and DISTINCT keeps a single NULL.

Sometimes the question is not 'show me customers with their orders' but simply 'which customers have ordered at all?'. That question is about existence. A semi-join answers it: it returns rows from the left table when at least one matching row exists on the right, and it returns each left row once, no matter how many matches it has. The right table is only consulted, never displayed.

SQL has no SEMI JOIN keyword. You spell a semi-join in one of three ways: a correlated WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.cust_id), an IN (SELECT ...) subquery, or an ordinary join against a DISTINCT subquery so the right side is already unique before it joins.

The reason to care is the plain INNER JOIN. It pairs a customer with every one of their orders, so a customer with three orders appears three times. If you only wanted 'customers who ordered', those repeats are a silent bug, and every count or sum built on top of them is inflated. The example below sets up a small version of the running schema and runs all four forms side by side.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (cust_id INT PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INT PRIMARY KEY, cust_id INT);
INSERT INTO customers VALUES (1,'Asha'),(2,'Ben'),(3,'Cara');
INSERT INTO orders VALUES (10,1),(11,1),(12,2);
""")

FORMS = {
    "inner:": "SELECT c.name FROM customers c JOIN orders o ON o.cust_id = c.cust_id ORDER BY c.name",
    "exists:": "SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.cust_id)",
    "in:": "SELECT name FROM customers WHERE cust_id IN (SELECT cust_id FROM orders)",
    "distinct:": "SELECT c.name FROM customers c JOIN (SELECT DISTINCT cust_id FROM orders) o ON o.cust_id = c.cust_id",
}
for label, sql in FORMS.items():
    print(f"{label:<10}{[r[0] for r in db.execute(sql)]}")

Asha has two orders, Ben one, Cara none.

The inner join returned Asha twice, once per order. The three semi-join forms returned her once. Cara is absent from all four because she has no orders, which is exactly the group the next page goes looking for.

An anti-join is the mirror image: it keeps the left rows that have no match on the right. 'Customers who never ordered' is the classic case. Again there is no keyword, and again there are three spellings: NOT EXISTS, LEFT JOIN ... WHERE right.key IS NULL, and NOT IN. They look interchangeable, but one of them behaves differently as soon as a NULL appears in the right-hand data.

NOT IN is dangerous. If the subquery returns even one NULL, the whole result becomes empty, with no error and no warning. The reason is three-valued logic. x NOT IN (1, NULL) is shorthand for x <> 1 AND x <> NULL. Comparing anything to NULL evaluates to UNKNOWN, and WHERE keeps a row only when the predicate is TRUE. The table walks through it for two values of x.

A value that is not in the list can never be proven to be different from the NULL, so no row can ever evaluate to TRUE. The next example runs all three anti-join forms, first on clean data and then after one order is inserted with a missing customer id. It reuses the tables from the previous page.

ANTI = {
    "NOT EXISTS": "SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.cust_id)",
    "LEFT JOIN": "SELECT c.name FROM customers c LEFT JOIN orders o ON o.cust_id = c.cust_id WHERE o.order_id IS NULL",
    "NOT IN": "SELECT name FROM customers WHERE cust_id NOT IN (SELECT cust_id FROM orders)",
}

def run(title):
    print(title)
    for label, sql in ANTI.items():
        print(f"  {label:<11}{[r[0] for r in db.execute(sql)]}")

run("before NULL row")
db.execute("INSERT INTO orders VALUES (13, NULL)")
run("after NULL row")

Order 13 has no customer id, like a guest checkout.

On clean data all three agree. One NULL later, NOT IN quietly returns nothing while the other two still find Cara. This is why NOT EXISTS is generally the recommended default for anti-joins: it tests whether a correlated row exists, so a NULL in the right table simply never matches and cannot poison the answer. It is NULL-safe without any extra care, it reads as the plain-English question, and it is a close second to LEFT JOIN ... IS NULL for clarity.

On performance, EXISTS has a natural advantage: it only has to find the first match for each left row, so it can stop early and never builds the full list of right-hand values. The LEFT JOIN ... IS NULL form conceptually produces every joined row, padded ones included, and filters afterwards, so it may materialize more. In practice most optimizers recognise all three anti-join spellings and rewrite them to the same internal anti-join operator, so the plans often match. Check with EXPLAIN ANALYZE rather than assuming.

A related fact: EXISTS ignores the subquery's SELECT list entirely. It asks only whether any row comes back, never what the row contains. SELECT 1, SELECT * and even SELECT 1/0 behave identically, because the expressions are never used. The loop below proves it on the tables from before.

Putting the three join shapes next to each other makes the difference concrete. They differ in what they keep and in whether a left row can repeat.

The correlated NOT EXISTS is the canonical gap-finding query. It answers 'what is missing?' for products never ordered, users who never logged in, and orphan foreign keys left behind by a bad migration, where a child row points at a parent that no longer exists.

-- products never ordered
SELECT p.product_id, p.name FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.product_id = p.product_id);

-- users with no login
SELECT u.user_id FROM users u
WHERE NOT EXISTS (SELECT 1 FROM logins l WHERE l.user_id = u.user_id);

-- orphan foreign keys: orders whose customer is gone
SELECT o.order_id FROM orders o
WHERE o.cust_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.cust_id = o.cust_id);

The last query skips NULL cust_id on purpose: a missing key is not an orphan.

A join in SQL only says which rows should pair up. It never says how to find them. The engine chooses one of three physical algorithms for each join in the plan: nested loop, hash join or merge join. They return the same rows, but their costs differ by orders of magnitude. Knowing which one is running tells you why a query is fast or slow.

The simplest algorithm picks a row from the outer input, looks through the inner input for partners, emits every match, then moves to the next outer row. With N outer rows and M inner rows, scanning the inner side each time costs about N × M comparisons. If the inner join key has an index, each probe becomes a tree lookup instead of a scan, and the cost drops to about N × log M.

Nested loop wins when the outer side is tiny and the inner side has a selective index. That describes the classic OLTP query, such as fetching one customer and their handful of orders. Three index lookups cost almost nothing, and nothing has to be built or sorted first. The same algorithm is a disaster on two large unindexed tables, because N × M grows without bound.

A hash join works in two phases. It first reads the smaller input, called the build side, into an in-memory hash table keyed on the join column. It then streams the larger probe side. Each probe row hashes its key and checks one bucket. Every row is touched about once, so the cost is roughly N + M rather than N × M.

Hashing only tells you whether two keys are equal. It says nothing about which is larger. A hash join therefore requires an equality predicate, and it cannot serve ON a.x < b.y. Within that limit it is the workhorse for large, unindexed analytical joins, where nested loop would be hopeless and sorting everything would be wasted effort.

The hash table has to fit in the work memory budget, such as work_mem in PostgreSQL. If the build side is bigger, the engine falls back to a grace hash join. It partitions both inputs by hash value, writes the partitions to disk, then joins them partition by partition. The result is still correct, but the extra disk writes and reads make it much slower.

A merge join, also called sort-merge, sorts both inputs on the join key and then advances a pointer on each side in lockstep. Whichever side holds the smaller key moves forward. When the keys are equal, the engine emits the pairs. Each input is read once during the walk. Sorting costs about N log N + M log M. If both inputs already arrive in key order, for example from an index scan or a clustered table, the sort disappears and the cost falls to about N + M.

Because the walk follows the sort order, a merge join's output is already sorted on the join key. When the query ends in ORDER BY on that key, the planner can drop the separate sort. That makes the ordering free.

Outer joins need no new algorithm. Each algorithm gets a variant that tracks whether every row from the preserved side found a partner. A row that never matched is emitted once, with NULLs in all columns from the other side.

In a hash join the same idea works at the end of the probe phase. To preserve the build side, the engine flags each hash entry when it is hit. After all probe rows have been streamed, it emits every unflagged entry padded with NULLs. This also explains why FULL OUTER JOIN is normally hash- or merge-based. It must emit unmatched rows from both inputs, and a plain nested loop that walks the outer side has no cheap way to find which inner rows were never touched. PostgreSQL rejects a full join whose condition cannot be hashed or merged for that reason.

The sketch below runs the employee and department join from the running schema three ways and counts the basic work each one does. The orphan employee has a NULL key. It matches nothing in any algorithm, and the empty Legal department is likewise left out. The merge version filters out NULL keys before sorting. The merge count covers only the walk, not the sort.

emps = [('Ana', 10), ('Ben', 10), ('Cy', 20), ('Di', 30), ('Eve', 30), ('Flo', None)]
depts = [(10, 'Eng'), (20, 'Sales'), (30, 'Ops'), (40, 'Legal')]

def nested_loop(outer, inner):
    rows, work = [], 0
    for name, d in outer:
        for key, label in inner:
            work += 1
            if d == key:
                rows.append((name, label))
    return rows, work

def hash_join(build, probe):
    table, work = {}, 0
    for key, label in build:
        table[key] = label
        work += 1
    rows = []
    for name, d in probe:
        work += 1
        if d in table:
            rows.append((name, table[d]))
    return rows, work

def merge_join(left, right):
    left = sorted((r for r in left if r[1] is not None), key=lambda r: r[1])
    right = sorted(right)
    rows, work, i, j = [], 0, 0, 0
    while i < len(left) and j < len(right):
        work += 1
        lk, rk = left[i][1], right[j][0]
        if lk < rk:
            i += 1
        elif lk > rk:
            j += 1
        else:
            rows.append((left[i][0], right[j][1]))
            i += 1
    return rows, work

nl, w1 = nested_loop(emps, depts)
hj, w2 = hash_join(depts, emps)
mj, w3 = merge_join(emps, depts)
print(f'nested loop: {len(nl)} rows, {w1} comparisons')
print(f'hash join:   {len(hj)} rows, {w2} hash operations')
print(f'merge join:  {len(mj)} rows, {w3} merge steps')
print('same rows:', sorted(nl) == sorted(hj) == sorted(mj))

Dictionary keys are unique here, so the hash and merge versions can assume each department matches at most once.

All three return the same five rows. They differ only in the work done to get there. At this size the gap is small. Scale the tables up and the nested loop's N × M comparisons dwarf the other two.

The usual decision logic follows from that table. Engines weigh estimated costs rather than fixed rules, but the shape of the choice looks like this.

EXPLAIN shows the chosen algorithm as a node name: Nested Loop, Hash Join or Merge Join. Next to each node sit the estimated row count and, if you used EXPLAIN ANALYZE, the actual row count. Look at both. The plan below is healthy, because the estimates match what really happened.

Hash Join  (rows=1200 est)  (actual rows=1198)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (rows=1200 est)  (actual rows=1198)
  ->  Hash  (rows=300 est)  (actual rows=300)
        ->  Seq Scan on customers c  (rows=300 est)  (actual rows=300)

A trimmed PostgreSQL-style plan. Estimates and actuals agree.

Nested Loop  (rows=3 est)  (actual rows=94000)
  ->  Index Scan on customers c  (rows=3 est)  (actual rows=2100)
  ->  Index Scan on orders o  (rows=1 est)  (actual rows=45)

The planner expected three rows and got 94,000. It picked a loop that suits three rows.

A big gap between estimated and actual rows, roughly 10x or more, means the planner is working from bad statistics. It picked the nested loop because it believed the outer side was tiny. The first node where estimates and actuals diverge is where to start looking.

When you write FROM a JOIN b JOIN c, you are describing what you want, not the order in which tables get read. Inner joins are commutative (a JOIN b equals b JOIN a) and associative ((a JOIN b) JOIN c equals a JOIN (b JOIN c)), so every ordering returns the same rows. The planner is therefore free to pick whichever order it estimates to be cheapest. The order you type in the FROM clause is a readability choice, not a plan.

Freedom has a price: the number of candidate orderings grows factorially. With N tables there are up to N! left-deep orderings, and bushy trees (joining two intermediate results together) add even more. The script below prints how fast that grows.

import math

for n in (3, 5, 10, 15):
    print(n, math.factorial(n))

Upper bound on left-deep join orders for N tables

No planner can price a trillion plans inside a query's planning budget, so engines switch strategy as N grows. The table shows the usual split.

Every choice the planner makes depends on one number: how many rows will come out of each step. This is cardinality estimation. If the planner thinks a filter leaves 2,000 rows when it really leaves 400,000, it will pick a nested loop where a hash join belonged, and it will place that table too early in the order. Because each node's estimate feeds the next one, a wrong guess at the bottom of the plan spreads upward.

Estimates are built from statistics the database keeps per column: a histogram of value ranges, the number of distinct values, the null fraction, and the most common values. A predicate like status = 'shipped' is turned into a selectivity (a fraction of the table) using those numbers, and selectivity times table size gives the row estimate.

The second classic failure is correlated columns. The planner assumes predicates are independent and multiplies their selectivities. When the columns are linked, as city and country are, that product is far too small.

The estimate is 10x too low, and a join sitting above this filter will inherit that error. The fix is to tell the database the columns are related by creating multi-column (Postgres calls them extended) statistics, then refreshing them.

CREATE STATISTICS addr_city_country (dependencies)
  ON city, country FROM addresses;

ANALYZE addresses;

Postgres: teach the planner that city determines country

The rule of thumb is filter early, join late: reduce the number of rows before they enter a join, not after. A join's cost grows with its inputs, so a filter that removes 99% of a table before the join saves work at every step above it. For plain inner joins the optimizer pushes simple WHERE filters down for you. The rule matters most where it cannot: filters that must apply after a GROUP BY, a DISTINCT, or inside the nullable side of an outer join, and pre-aggregation of large tables.

Outer joins limit how freely the planner can reorder. a LEFT JOIN b must keep every row of a, so b cannot be moved ahead of a, and a LEFT JOIN (b JOIN c) is not the same query as (a LEFT JOIN b) JOIN c. A chain of five LEFT JOINs therefore has far fewer legal plans than five inner joins, which means fewer chances to find a cheap one.

Join order is also where the silent corruption happens. When two independent 1:many tables hang off the same parent, joining both multiplies the children: a customer with 3 orders and 2 addresses yields 3 × 2 = 6 rows, and every order amount is counted twice. With 10 orders and 5 addresses it would be 50 rows per customer. The example below shows the damage and the cure, which is to aggregate each branch in its own subquery before joining.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INT, amount INT);
CREATE TABLE addresses(id INTEGER PRIMARY KEY, customer_id INT, city TEXT);
INSERT INTO customers VALUES (1, 'Asha');
INSERT INTO orders VALUES (1, 1, 10), (2, 1, 20), (3, 1, 30);
INSERT INTO addresses VALUES (1, 1, 'Pune'), (2, 1, 'Delhi');
''')

bad = db.execute('''
SELECT COUNT(*), SUM(o.amount)
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN addresses a ON a.customer_id = c.id''').fetchone()
print('fan-out rows, sum:', bad)

good = db.execute('''
SELECT c.name, o.total, a.n
FROM customers c
JOIN (SELECT customer_id, SUM(amount) AS total
      FROM orders GROUP BY customer_id) o ON o.customer_id = c.id
JOIN (SELECT customer_id, COUNT(*) AS n
      FROM addresses GROUP BY customer_id) a ON a.customer_id = c.id''').fetchall()
print('pre-aggregated:', good)

The true order total is 60, not 120

When a join is slow, do not guess. Run EXPLAIN ANALYZE, which executes the query and reports both the planner's estimated rows and the actual rows at each node. Plain EXPLAIN shows only estimates, and estimates are exactly what might be wrong. Walk up the plan from the leaves and find the first node where estimate and actual differ by 10x or more. Everything above it is built on a bad number, so that node is where you start.

Nested Loop  (rows=2000)  (actual rows=400000)
  -> Seq Scan on addresses  (rows=2000)  (actual rows=20000)   -- 10x off: start here
       Filter: city = 'Paris' AND country = 'France'
  -> Index Scan on orders  (rows=1)  (actual rows=20 loops=20000)

Simplified plan: the correlated filter is underestimated, so a nested loop was chosen

Here the scan on addresses is off by exactly the correlation error from earlier. The planner expected 2,000 probes into orders and got 20,000, so the nested loop it chose is a poor fit. Fixing the estimate at that scan usually fixes the whole plan, with no change to the query text.

Work through the fixes in this order, from cheapest and most durable to most invasive. Hints come last.

-- before: runs the subquery once per customer
SELECT c.name,
       (SELECT MAX(o.placed_at) FROM orders o
        WHERE o.customer_id = c.id) AS last_order
FROM customers c;

-- after: aggregate once, then join
SELECT c.name, o.last_order
FROM customers c
LEFT JOIN (SELECT customer_id, MAX(placed_at) AS last_order
           FROM orders GROUP BY customer_id) o
  ON o.customer_id = c.id;

Rewrite a correlated subquery as a join

Every major engine offers a way to force a join algorithm or order. They work, but they freeze the plan: when the data grows or its shape changes, the hinted plan stays the same while the optimizer would have moved on. Treat a hint as a last resort, document why it exists, and revisit it.

A join takes two tables and returns one table. Because the result is itself a table, you can feed it into another join. That is all a chain is. When you write A JOIN B JOIN C, the engine reads it as (A JOIN B) JOIN C. The first join runs, and its output becomes the input to the second.

This matters for the ON clause of the third join. It may refer to columns from any table already in the chain, not only the one written just above it. It also matters for outer joins. For the third join, the 'left table' is everything accumulated so far: every column and every row, including rows that were NULL-padded by earlier joins.

That is why RIGHT JOIN confuses readers inside a chain. In customers c LEFT JOIN orders o ON ... RIGHT JOIN shipments s ON s.order_id = o.order_id, the RIGHT JOIN keeps every shipment. It is not preserving just orders. It preserves one side of a join whose other side is the whole customers-and-orders result. A reader has to rebuild that composite in their head, which is the main reason teams keep chains LEFT all the way down.

The examples in this section use a small shop schema. Customer Ana has two orders, both shipped. Ben has one order that has not shipped yet. Cy has never ordered. The first block builds the tables and shows the intermediate result AB. That is the table the shipments join will be applied to.

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (cust_id INT, name TEXT);
CREATE TABLE orders (order_id INT, cust_id INT, amount INT);
CREATE TABLE shipments (ship_id INT, order_id INT, carrier TEXT);
CREATE TABLE tickets (ticket_id INT, cust_id INT);
INSERT INTO customers VALUES (1,'Ana'),(2,'Ben'),(3,'Cy');
INSERT INTO orders VALUES (10,1,50),(11,1,30),(12,2,20);
INSERT INTO shipments VALUES (100,10,'DHL'),(101,11,'UPS');
INSERT INTO tickets VALUES (900,1),(901,1),(902,1),(903,2);
""")

def run(label, sql):
    print(label)
    for row in db.execute(sql):
        print(row)

run("customers LEFT JOIN orders",
    "SELECT c.name, o.order_id FROM customers c "
    "LEFT JOIN orders o ON o.cust_id = c.cust_id "
    "ORDER BY c.cust_id, o.order_id")

The composite AB: four rows, with Cy padded by NULL.

Take the composite above and join shipments to it. Cy's row carries order_id = NULL. An INNER JOIN on s.order_id = o.order_id evaluates NULL = something, which is UNKNOWN, and an inner join discards anything that is not TRUE. So Cy's row is dropped. A single inner join after a left join silently cancels the outer-ness, and the query behaves as if the first join had been inner too.

Ben loses his row for a different reason. His order 12 has no shipment, so the inner join finds no partner for it. The block below runs three versions of the chain. Compare which customers survive.

There are two ways to fix this. The first is to keep the chain LEFT all the way down, which is the first result above. Every customer survives, and Ben's unshipped order is still visible with a NULL carrier. The second is to parenthesize: A LEFT JOIN (B JOIN C ON ...) ON .... This joins B and C first, then attaches that result to A. The third result shows what this means. The inner part keeps only orders that have shipments, so Ben's unshipped order 12 never reaches the outer join and Ben appears with NULL. The two fixes give different answers, so pick the one that matches the question being asked.

The fan trap happens when one parent row is joined to two child tables that have nothing to do with each other. Ana has 2 orders and 3 support tickets. Joined together, each order meets each ticket, giving 2 × 3 = 6 rows. The orders and tickets are never related to each other, but the join multiplies them anyway. The visible symptom is an inflated SUM or COUNT: Ana's spend is counted three times and her tickets twice.

The fix is to collapse each child to one row per parent before it enters the chain, so every join is at most many-to-one against the parent. A subquery with GROUP BY does this. Engines that support LATERAL can do the same with a correlated aggregate per customer. The block below compares the naive chain with the pre-aggregated one.

Ana's true spend is 80 with 3 tickets, but the naive chain reports 240 and 6. Ben looks fine only by luck, because he has one order and one ticket. A bug that appears only for some rows is easy to miss in a quick check.

The chasm trap is the same multiplication seen from the other side. Two tables each point many-to-one at a shared dimension, such as sales and refunds that both carry a store_id. They share a store but have no relationship to each other. Joining both to the store pairs every sale with every refund from the same store. Those pairs correspond to no real event.

North produces four rows from two sales and two refunds. 'Sale 1 with refund 7' is not a fact anywhere in the data. The remedy is the same as for the fan trap: aggregate sales and refunds separately per store, then join the two summaries.

The safest shape for a wide query is the star schema pattern. One fact table, such as orders or events, is the driving table. Each descriptive dimension is LEFT JOINed to it on a surrogate key. Every one of those joins is many-to-one, since many fact rows point to one dimension row, so no join can multiply the fact rows. The result has exactly one row per fact row, and no dimension can remove a fact because every join is LEFT.

SELECT f.order_id, f.amount,
       c.name  AS customer,
       p.title AS product,
       d.month
FROM fact_orders f
LEFT JOIN dim_customer c ON c.customer_key = f.customer_key
LEFT JOIN dim_product  p ON p.product_key  = f.product_key
LEFT JOIN dim_date     d ON d.date_key     = f.date_key;

Fact first, dimensions after; each join is many:1.

That layout also shows two habits for any multi-join query. First, order the FROM clause to tell a story: the driving or fact table first, then the lookups it needs. For inner joins the optimizer ignores your order and picks its own, so this is purely for readers. For outer joins the order does carry meaning, as the earlier examples showed, so it is worth getting right.

Second, alias every table and qualify every column. In a six-table query, a bare name or created_at is ambiguous. Worse, a query that works today can start failing with an 'ambiguous column' error the day someone adds a name column to another table in the chain. Short, meaningful aliases such as c, o and s cost nothing.

When a query reaches nine tables, break it into named steps with CTEs. Each CTE joins a few tables and gives the result a name, and the final query joins the named pieces. On most engines the optimizer flattens simple CTEs back into one plan, so the semantics and usually the speed are unchanged. The only difference is that people can read it. CTEs are also the natural place to pre-aggregate children before a join.

WITH order_context AS (
  SELECT o.order_id, o.amount, c.name, r.region_name
  FROM orders o
  LEFT JOIN customers c ON c.cust_id = o.cust_id
  LEFT JOIN regions   r ON r.region_id = c.region_id
),
order_tickets AS (
  SELECT cust_id, COUNT(*) AS tickets
  FROM tickets GROUP BY cust_id
),
order_shipping AS (
  SELECT order_id, MIN(carrier) AS carrier
  FROM shipments GROUP BY order_id
))
SELECT oc.*, os.carrier
FROM order_context oc
LEFT JOIN order_shipping os ON os.order_id = oc.order_id;

Illustrative: three named steps instead of one long chain.

Finally, run a sanity drill while you build a chain: add one join, then check the row count. Each join should change the count only in a way you can explain. If the count jumps unexpectedly, the newest join is many-to-many against what you already had. The block below runs this on our data, adding one join at a time.

Orders add one row because Ana has two orders, and that growth is expected for a one-to-many join. Tickets then double the count from 4 to 8, which no customer-level question explains. The tickets join is many-to-many against orders, and it is the one to fix.

Joins make a result wider by adding columns. The set operators make it longer by adding rows. UNION takes two result sets and stacks them one on top of the other. It then removes every duplicate row from the combined set, so a row that appears in both inputs is returned once. UNION ALL stacks the same two inputs and keeps every row, duplicates included.

The difference matters for cost as well as for the result. To remove duplicates, UNION has to compare rows with each other, either by sorting the whole combined set or by building a hash table of it. UNION ALL does no comparison at all. It just appends the second input to the first.

Here is the difference on a small example. The sample data has a users table and a users_archive table. Ben and a row with a NULL name appear in both. The helper show runs a query and prints each row, with NULL appearing as None.

Look at the row (3, None). A join predicate never treats NULL as equal to NULL, but duplicate detection does. UNION therefore collapses the two NULL-name rows into one, because it considers the whole row identical. This is the opposite of what you saw with join keys, and it follows the same rule as DISTINCT and GROUP BY.

Stacking only works if the two result sets have the same shape. Both SELECTs must return the same number of columns, in the same order, with compatible types position by position. The engine matches columns by position and never by name, so the third column of the first query is paired with the third column of the second even if their names differ.

The column names come from the first SELECT. Any aliases you put on the second branch are ignored. Because of this, alias the columns in the first branch and treat the later branches as positional.

When rows from several branches end up in one list, you usually want to know where each one came from. The fix is to tag your branches with a constant column. In the example, the first branch names that column source, and the second branch only needs to supply the value.

The output header is source, id, label, all taken from the first branch. The tag also settles the duplicate question in a useful way. If you tag the rows and then use plain UNION, the tags make the rows differ, so nothing is removed. When you tag branches, UNION ALL is the right operator.

The last rule is about types. If one side supplies an integer and the other text in the same position, engines disagree. Some raise an error. Others silently coerce the integers to text, which changes how the values sort ('10' comes before '9'). Do not rely on either behaviour. Cast one side explicitly so the result type is a choice you made.

-- may error, or silently turn ids into text
SELECT id FROM users
UNION ALL
SELECT 'n/a';

-- explicit and portable
SELECT CAST(id AS TEXT) AS id FROM users
UNION ALL
SELECT 'n/a';

Make the target type visible in the query

A set operation produces a single combined result, so ORDER BY belongs to that whole result. You write it once, after the last SELECT. It cannot sort one branch on its own, and it can only refer to the output columns, either by the names from the first SELECT or by position, such as ORDER BY 1. An expression over the original table columns is not allowed here, and several engines will reject it.

LIMIT on a single branch is different. The clause would otherwise attach to the whole statement, so to limit one branch you put that branch in parentheses. Each parenthesised branch can then carry its own ORDER BY and LIMIT, and the final ORDER BY after the closing parenthesis still applies to everything.

(SELECT id FROM users ORDER BY id DESC LIMIT 5)
UNION ALL
(SELECT id FROM users_archive ORDER BY id LIMIT 5)
ORDER BY id;

Postgres and MySQL. SQLite does not accept parentheses here, so wrap each branch as SELECT * FROM (SELECT ... LIMIT 5).

One common use of UNION ALL is adding a synthetic Total row to a report. The first branch groups the data, and the second branch computes the grand total and labels it. Because the total must come last, the query adds a small sort column to both branches and orders by it.

The Total row must use UNION ALL. With plain UNION, a region whose sum happened to equal the grand total would not be removed (the labels differ), but you would still pay for a pointless sort. Without the ord column, nothing guarantees the Total lands last, since a set operation has no inherent order.

UNION ALL fits any case where the same kind of row lives in several places and you want one list. The three common cases are below.

The third case is the one where UNION, rather than UNION ALL, is correct. If a customer can match both rules and you want each customer once, the dedupe is the whole point. Even then, prefer to select only the key columns so that the comparison is cheap and the rows really are identical.

UNION is not a substitute for OR in a WHERE clause. They answer different questions: OR filters one set of rows, while UNION combines two queries, and UNION also removes duplicate rows that the OR version would keep. The rewrite is sometimes worth doing anyway, because a single OR across two different columns can stop the planner from using either index. As a UNION ALL, each branch is a plain filter that can use its own index.

-- one filter, two columns: the planner may scan everything
SELECT * FROM orders
WHERE customer_id = 7 OR salesperson_id = 12;

-- each branch seeks its own index
SELECT * FROM orders WHERE customer_id = 7
UNION ALL
SELECT * FROM orders
WHERE salesperson_id = 12 AND customer_id IS DISTINCT FROM 7;

The second branch excludes rows the first already returned, so UNION ALL gives the same rows as the OR

The exclusion in the second branch is what keeps the rewrite equivalent. Without it, an order for customer 7 handled by salesperson 12 would appear twice. Using UNION instead would hide that problem, but it would also collapse genuinely duplicate rows and add the dedupe sort. The NULL-safe IS DISTINCT FROM matters too, since a plain <> 7 would drop rows whose customer is NULL. MySQL spells it NOT (customer_id <=> 7).

UNION stacks two result sets. INTERSECT and EXCEPT compare them instead. INTERSECT keeps only the rows that appear in both result sets. EXCEPT keeps the rows from the first set that do not appear in the second. Oracle spells that second operator MINUS. Both remove duplicates from their output by default, the same way UNION does.

The second step is where people go wrong. These operators have no key column and no ON clause. Two rows are the same only when every projected column is equal. If your SELECT lists user_id, plan, a user who moved from the free plan to the pro plan is a different row, not the same user seen twice.

The example below uses SQLite from the Python standard library, which has both operators. It also shows how the order of operands matters for one operator but not the other.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE march (user_id INT, plan TEXT);
CREATE TABLE april (user_id INT, plan TEXT);
INSERT INTO march VALUES (1,'free'),(2,'pro'),(3,'pro'),(3,'pro'),(4,'free');
INSERT INTO april VALUES (2,'pro'),(3,'free'),(4,'free'),(5,'pro');
''')

def q(op, cols='user_id, plan', a='march', b='april'):
    sql = f'SELECT {cols} FROM {a} {op} SELECT {cols} FROM {b} ORDER BY 1'
    return db.execute(sql).fetchall()

print('INTERSECT, whole row:', q('INTERSECT'))
print('INTERSECT, user_id only:', q('INTERSECT', 'user_id'))
print('march EXCEPT april:', q('EXCEPT'))
print('april EXCEPT march:', q('EXCEPT', a='april', b='march'))

User 3 is pro in March and free in April, so the whole row differs

Look at user 3. Compared on user_id alone, the user is in both months. Compared on the whole row, the user is not shared, because the plan changed. March also held two identical copies of (3, 'pro'), and EXCEPT returned a single row for them. The last two lines show that swapping the operands changes the answer.

By default these operators work on sets, so copies collapse. The ALL variants, INTERSECT ALL and EXCEPT ALL, work on multisets and count copies. Say the left side holds a row three times and the right side holds it once. INTERSECT ALL keeps the smaller count, so it returns 1 copy. EXCEPT ALL subtracts counts, so it returns 2 copies. Postgres supports both ALL variants. SQL Server does not, and MySQL is not somewhere to count on them either.

Python's Counter follows the same rules. Its & takes the minimum count per item, and its - subtracts counts and floors them at zero. That makes it a handy way to see the arithmetic.

NULLs behave differently here than in a join. In a join predicate NULL = NULL is UNKNOWN, so NULL keys never match. In set operators two NULLs count as equal. A row of all NULLs on both sides therefore counts as a duplicate, and it appears in an INTERSECT. The next example runs the same data through both mechanisms.

db.executescript('''
CREATE TABLE x (a INT, b INT);
CREATE TABLE y (a INT, b INT);
INSERT INTO x VALUES (NULL,NULL),(NULL,NULL),(1,NULL);
INSERT INTO y VALUES (NULL,NULL),(1,2);
''')
print('INTERSECT:', q('INTERSECT', 'a, b', 'x', 'y'))
print('x EXCEPT y:', q('EXCEPT', 'a, b', 'x', 'y'))
n = db.execute('SELECT COUNT(*) FROM x JOIN y ON x.a = y.a AND x.b = y.b').fetchone()[0]
print('join on equality:', n)

Reuses the db and q helper from the first example

Now precedence. In the standard, and in Postgres, INTERSECT binds tighter than UNION and EXCEPT. UNION and EXCEPT share a level and group left to right. Mixing them without brackets can therefore give a result you did not intend. SQLite is the exception: it treats all compound operators equally, left to right, and has no brackets.

The best-known use of EXCEPT is checking whether two tables hold the same data. Subtract in both directions and put the results together: (A EXCEPT B) UNION ALL (B EXCEPT A). If the result is empty, nothing is in one table and missing from the other. Teams run this on the old and new versions of a query before merging a rewrite.

The second question in the flowchart matters because EXCEPT removes duplicates before it compares. A table with one copy of a row and a table with two copies look identical to it. The example below shows the trap, then closes it by numbering the copies of each row. That is a portable stand-in for EXCEPT ALL, and it also works on engines that lack the ALL variants.

MySQL only gained INTERSECT and EXCEPT in version 8.0.31. On an older server you emulate them. Use EXISTS or NOT EXISTS, compare every column with the NULL-safe operator <=>, and add DISTINCT to match the set semantics.

In day-to-day work the two operators answer membership questions. INTERSECT finds audience overlap: the users who are in both of two segments. EXCEPT finds churn: last month's users minus this month's. Both queries project a single id column, so comparing the whole row is the same as comparing the key.

Joins and set operations both combine two queries, but they grow the result along different axes. A join is wider: each output row carries the columns of both inputs, so you get more attributes about the same entity. A set operation is longer: the output has the same columns as its inputs and only the number of rows changes. Before you pick a tool, ask which way you want the data to grow.

The shape of the inputs is the quickest check. A set operation demands that both sides have the same number of columns with compatible types, in the same order. A join has no such rule, since you can join a 2-column table to a 9-column one. If you find yourself padding queries with dummy columns or casting just to line them up, you are probably reaching for a set operation when you wanted a join. If you find yourself joining two same-shaped tables only to stack them, you wanted UNION ALL.

There is one more asymmetry. UNION ALL has no join equivalent at all. A FULL OUTER JOIN may look similar because it keeps unmatched rows from both sides, but it places them side by side, with NULLs padding the columns of the other table. A row that exists in both tables becomes one wide row, not two stacked rows. UNION ALL keeps both rows, stacked in one shared set of columns.

INTERSECT and EXCEPT are really membership tests on whole rows, so they can be rewritten with the semi-join and anti-join forms from earlier. A INTERSECT B returns each distinct row of A that has an identical row in B. That is a semi-join on all columns, followed by DISTINCT, because INTERSECT removes duplicates.

SELECT DISTINCT a.k, a.v
FROM a
WHERE EXISTS (
  SELECT 1 FROM b
  WHERE b.k IS NOT DISTINCT FROM a.k
    AND b.v IS NOT DISTINCT FROM a.v
);

A INTERSECT B, written as a semi-join on every column

The comparison must be NULL-safe on every column. A plain = returns UNKNOWN when either side is NULL, so a row such as (NULL, 'z') would never match itself. Set operators treat two NULLs as equal, so the rewrite has to as well. IS NOT DISTINCT FROM does that in Postgres. SQLite spells the same test IS, and MySQL uses <=>.

A EXCEPT B is the mirror image: distinct rows of A with no identical row in B. That is an anti-join on all columns. Use NOT EXISTS with the same NULL-safe comparison on every column, and keep the DISTINCT.

SELECT DISTINCT a.k, a.v
FROM a
WHERE NOT EXISTS (
  SELECT 1 FROM b
  WHERE b.k IS NOT DISTINCT FROM a.k
    AND b.v IS NOT DISTINCT FROM a.v
);

A EXCEPT B, written as an anti-join on every column

The example below runs the set operators and the rewrites on two small tables, a and b. Table a holds a duplicated row (1, 'x') and a row with a NULL key. Table b also has a NULL-key row. The last query is a naive INNER JOIN on both columns, which shows two things: it multiplies the duplicated row, and it loses the NULL row because NULL = NULL is not true.

The NULL-safe EXISTS and NOT EXISTS forms agree exactly with INTERSECT and EXCEPT. The plain join does not: it returns the duplicated (1, 'x') twice and drops (NULL, 'z') entirely.

The flip side matters just as much. INTERSECT and EXCEPT always compare the full projected row. If the question is ''which customers appear in both lists, judged by customer id alone'', then the other columns, such as a name or a timestamp, must not take part in the comparison. Projecting only the id is sometimes enough, but if you need other columns in the output, you must use a join or EXISTS on just the key. Using INTERSECT or EXCEPT there is simply wrong.

Joins can multiply rows. If a customer has three orders, a join to orders yields three rows for that customer. INTERSECT and EXCEPT never multiply: their output has at most one copy of each row, whatever the input multiplicities. That determinism is a good reason to prefer them, or an EXISTS form, for pure membership questions such as ''who is in both lists''. A join answers that question only if you remember to deduplicate afterwards.

Performance points the other way, though. INTERSECT and EXCEPT typically sort or hash both inputs completely so they can compare whole rows. A correlated NOT EXISTS can instead probe an index on the large side for each row of the small side, and it can stop at the first match. When one side is huge and indexed, the EXISTS form often wins. When both sides are modest, or you want the simplest query, the set operator is clear and fast enough.

Read the first row as the joins and the rest as the set operations. Joins answer no, yes, no, no. The deduplicating set operations answer yes, no, yes, yes. UNION ALL never compares rows, so the NULL column does not apply to it.

Put the rules together by asking what the result should look like. If you have the same entity and want more attributes about it, the result grows wider, so use a join. If you have rows of the same shape from several places and want them all in one list, the result grows longer, so use UNION ALL. If you are asking whether a row exists elsewhere, you have a membership question: use EXISTS or NOT EXISTS, or INTERSECT and EXCEPT when whole rows are being compared.

Most join bugs do not raise an error. The query runs, returns rows, and the numbers look plausible. That is what makes them dangerous. This section walks through the mistakes that silently corrupt a result set, shows the symptom each one leaves, and gives the fix. The habits for catching them early are collected under 'Verification habits' and 'Reconciliation before you ship'.

A LEFT JOIN promises to keep every left row. It keeps that promise by padding the right-side columns with NULL when nothing matches. A WHERE clause runs after that padding, and a padded NULL fails nearly every comparison, so the unmatched rows are thrown away again. The query is now an inner join wearing an outer join's syntax. Putting the same predicate in ON filters the right table before the padding happens, so unmatched left rows survive.

A customer with zero orders still produces one output row, the padded one. COUNT(*) counts rows, so it reports 1 for that customer. COUNT(o.order_id) counts only non-NULL order ids, and a padded row has a NULL there, so it correctly reports 0. Count a non-nullable column from the right table, usually its primary key. The program below runs both filters and both counts on the same three customers. Chen has no orders, and Ben has only a 2023 order.

import sqlite3
db = sqlite3.connect(':memory:')
db.executescript("""
CREATE TABLE customers (cust_id INT PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INT PRIMARY KEY, cust_id INT, year INT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen');
INSERT INTO orders VALUES (10, 1, 2024), (11, 1, 2023), (12, 2, 2023);
""")

def show(label, sql):
    print(label, db.execute(sql).fetchall())

J = 'FROM customers c LEFT JOIN orders o ON o.cust_id = c.cust_id'
show('WHERE 2024:', f'SELECT c.name, o.order_id {J} WHERE o.year = 2024')
show('ON 2024   :', f'SELECT c.name, o.order_id {J} AND o.year = 2024')
show('COUNT(*)  :', f'SELECT c.name, COUNT(*) {J} GROUP BY c.name')
show('COUNT(id) :', f'SELECT c.name, COUNT(o.order_id) {J} GROUP BY c.name')

Same join, four different answers

When two tables are both on the many side of the same key, joining them pairs every row of one with every row of the other for that key. A customer with 2 payments and 3 support tickets becomes 6 rows. Summing the payment amount over those 6 rows counts each payment 3 times. The symptom to learn: totals that are an exact multiple of the truth, here 3 times too large. The fix is to collapse each branch to one row per key in its own subquery, and only then join.

db.executescript("""
CREATE TABLE payments (cust_id INT, amount INT);
CREATE TABLE tickets (cust_id INT, ticket_id INT);
INSERT INTO payments VALUES (1, 100), (1, 50);
INSERT INTO tickets VALUES (1, 7), (1, 8), (1, 9);
""")

show('true total  :', 'SELECT SUM(amount) FROM payments')
show('joined total:', 'SELECT SUM(p.amount) FROM payments p JOIN tickets t ON t.cust_id = p.cust_id')
show('pre-summed  :', 'SELECT p.total, t.n FROM (SELECT cust_id, SUM(amount) AS total FROM payments GROUP BY cust_id) p JOIN (SELECT cust_id, COUNT(*) AS n FROM tickets GROUP BY cust_id) t ON t.cust_id = p.cust_id')

Reuses db and show from the previous example

x NOT IN (2, NULL) expands to x <> 2 AND x <> NULL. The second comparison is UNKNOWN for every x, and a row survives a WHERE only when the predicate is TRUE. One NULL in the subquery therefore empties the whole result, with no warning. NOT EXISTS tests whether a matching row is present rather than comparing against a list, so a NULL in the other table simply never matches and the answer stays correct.

db.executescript("""
CREATE TABLE banned (cust_id INT);
INSERT INTO banned VALUES (2), (NULL);
""")

show('NOT IN    :', 'SELECT name FROM customers WHERE cust_id NOT IN (SELECT cust_id FROM banned)')
show('NOT EXISTS:', 'SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.cust_id = c.cust_id)')

Customer 2 is banned, and one banned row has a NULL id

In a multi-tenant table, user_id is only unique inside a tenant. Join on user_id alone and tenant 1's user 1 is matched with tenant 2's user 1. The same hole opens when you forget the date in a (product, day) key or the currency in a (account, currency) key. The result looks like a normal join with a few extra rows, but it is a data leak, not a performance problem. Always join on the full key.

NATURAL JOIN seems to avoid this, because it joins on every column the two tables share. The trouble is that you no longer control the predicate. It is whatever the column names happen to be today. The example below shows the leak first, then a NATURAL JOIN that works, then the moment someone adds an updated_at column to both tables.

db.executescript("""
CREATE TABLE users (tenant_id INT, user_id INT, name TEXT);
CREATE TABLE logins (tenant_id INT, user_id INT, at TEXT);
INSERT INTO users VALUES (1, 1, 'Asha'), (2, 1, 'Zed');
INSERT INTO logins VALUES (1, 1, 'mon'), (2, 1, 'tue');
""")

Q = 'SELECT u.name, l.at FROM users u {} ORDER BY 1, 2'
show('user_id only:', Q.format('JOIN logins l ON l.user_id = u.user_id'))
show('full key    :', Q.format('JOIN logins l ON l.user_id = u.user_id AND l.tenant_id = u.tenant_id'))
show('natural     :', Q.format('NATURAL JOIN logins l'))

db.executescript("""
ALTER TABLE users ADD COLUMN updated_at TEXT;
ALTER TABLE logins ADD COLUMN updated_at TEXT;
UPDATE users SET updated_at = '09:00';
UPDATE logins SET updated_at = '09:05';
""")
show('natural+col :', Q.format('NATURAL JOIN logins l'))

Two tenants that both have a user 1

Duplicate rows are a symptom of a join that multiplies, either a many:many pair or a missing key column. Adding DISTINCT makes the duplicates disappear, so the bug report is closed, but the cause is still there. Other columns may be inflated, sums are still wrong, and the engine now sorts or hashes the whole result to remove rows it should never have produced. Find the join that multiplies and fix it instead.

An index is built on the stored value of a column. When the join condition is ON UPPER(a.code) = b.code, the engine would have to compute UPPER for every row before it could compare, so it cannot seek into an index on a.code. The query still returns the right rows, only slowly, and the cost grows with table size. Two clean fixes exist: store the data already normalized, so the join compares plain columns, or create a functional index on the exact expression you join on.

Joining a VARCHAR key to an INT key makes the engine convert one side, and the conversion usually lands on the column you wanted to seek. A cast runs per row, an index on the converted column cannot be used for a seek, and the planner's row estimates get worse. The hidden danger is that it works in every test. Make the two columns the same type in the schema.

UNION and EXCEPT have the same precedence and are evaluated left to right, so a UNION b EXCEPT c means (a UNION b) EXCEPT c. If you meant to subtract c from b only, the result is different. INTERSECT is the exception: it binds tighter than both, so a UNION b INTERSECT c means a UNION (b INTERSECT c). The example uses sets where the two readings disagree: a is 1 and 2, b is 3, c is 2. SQLite does not accept parentheses around compound selects, so the groupings are written as subqueries. In PostgreSQL you would write (a UNION b) EXCEPT c directly.

Every mistake in this section changes the number of rows or the values in them. That means a few cheap checks, repeated as you build the query, catch nearly all of them. Add one join at a time and treat each new join as a hypothesis: the row count should move only in the way you can explain.

When you rewrite a query, the old one is your best test. Run both and compute (A EXCEPT B) UNION ALL (B EXCEPT A), which is every row in one result but not the other. An empty answer means the two return the same set of rows, and any rows returned show exactly where they disagree. The program below applies both habits. It first compares the row count of a join with its distinct customers, then reconciles a rewrite against the old query.

old = 'SELECT c.cust_id, c.name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.cust_id)'
joined = 'SELECT c.cust_id, c.name FROM customers c JOIN orders o ON o.cust_id = c.cust_id'
only24 = joined + ' AND o.year = 2024'

def diff(a, b):
    sql = (f'SELECT * FROM (SELECT * FROM ({a}) EXCEPT SELECT * FROM ({b})) '
           f'UNION ALL SELECT * FROM (SELECT * FROM ({b}) EXCEPT SELECT * FROM ({a}))')
    return db.execute(sql).fetchall()

print('rows       :', len(db.execute(joined).fetchall()))
print('distinct id:', db.execute(f'SELECT COUNT(DISTINCT cust_id) FROM ({joined})').fetchone()[0])
print('old vs join:', diff(old, joined))
print('old vs 2024:', diff(old, only24))

Reuses db, customers and orders from the first example

The two checks complement each other. The join returns 3 rows for 2 distinct customers, so the row-count check exposes the duplicate for Asha. The reconciliation of the old query against that same join comes back empty, because EXCEPT collapses duplicates and compares only which distinct rows exist. The second reconciliation finds Ben, a customer the 2024-only rewrite lost.

Most slow joins are slow for the same few reasons, and the cheapest fixes come first. A nested loop plan is only as fast as the lookup on its inner side, and a merge join is only cheap when its inputs arrive sorted. Both depend on indexes, so the checklist starts there.

In a 1:many join, the parent side is the primary key, which is always indexed. The child side, such as orders.customer_id, is the column the engine must search once per parent row, and it usually has no index. Declaring the foreign key does not create one in most engines. Without it, each probe becomes a full scan of the child table.

-- child side of customers 1:many orders
CREATE INDEX idx_orders_customer_id
    ON orders (customer_id);

One statement turns a per-row table scan into a per-row index seek

A composite index is sorted by its first column, then by the second within each first-column value. A seek can only use the leading columns of that order. If the join predicate filters on the second column alone, the engine cannot jump into the index and has to scan it.

CREATE INDEX idx_items_order_line ON order_items (order_id, line_no);

-- usable for a seek: leading column is in the predicate
... JOIN order_items i ON i.order_id = o.order_id

-- not usable for a seek: leading column is missing
... JOIN order_items i ON i.line_no = o.first_line

Put the columns your joins always supply first

Normally an index finds the matching row locations and the engine then visits the table to fetch the other columns. A covering index holds the join key plus every column the query selects, so the index alone answers the question and the table visit is skipped.

-- PostgreSQL / SQL Server syntax; the query reads only these columns
CREATE INDEX idx_orders_cust_cover
    ON orders (customer_id) INCLUDE (order_date, amount);

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

Plans show this as an index-only scan

Indexes give the optimizer options, but the optimizer still has to choose among them. It chooses using estimates, and the estimates come from stored statistics. The next four items keep those estimates honest and keep the join's working set small.

The planner stores row counts, distinct counts, and value histograms for each column. After a bulk load those numbers describe yesterday's table. It may expect 1,000 rows where 5 million arrived, and then pick a nested loop that runs 5 million probes. Refresh statistics right after any large load or delete.

ANALYZE orders;                  -- PostgreSQL, SQLite, MySQL
UPDATE STATISTICS dbo.orders;    -- SQL Server

Make this the last step of every bulk load job

The fewer rows enter a join, the less work it does. Filter on the large table in a place where the filter runs before the join. Optimizers usually push simple inner-join filters down on their own, but they cannot always do so across outer joins, subqueries with aggregates, or filters wrapped in functions. Writing the filter early removes the doubt.

-- filter first, then join the survivors
SELECT c.name, o.amount
FROM customers c
JOIN (SELECT customer_id, amount
      FROM orders
      WHERE order_date >= DATE '2026-01-01') o
  ON o.customer_id = c.customer_id;

Only this year's orders ever reach the join

For a LEFT JOIN, put filters on the right table in the ON clause or in a subquery. A WHERE on the right table runs after padding and turns the outer join back into an inner one.

A hash join builds an in-memory table from one input, and every selected column of every build-side row goes into it. SELECT * over a wide table, with long text or JSON columns, makes each row fat. The hash table is bigger, it fits in memory less often, and spills become more likely. Naming only the needed columns shrinks the build side with no other change.

When the hash table exceeds the memory budget (work_mem in PostgreSQL), the engine splits it into batches and writes them to disk. Each batch is then read back, which can turn a seconds-long join into minutes. The plan tells you when this happens: look for more than one batch.

Batches: 8 means the build side was split and spilled. Raising the memory setting for the session, say SET work_mem = '256MB';, often brings it back to Batches: 1. The query text does not change, so it is the cheapest experiment to try.

Sometimes the index and statistics are fine and the query itself is asking for more work than the question needs. These four items change the shape of the query or the data model.

When the question is only whether a match exists, EXISTS can stop at the first match for each outer row. An IN subquery may first collect the whole list, duplicates included, and then test against it. If the subquery returns millions of repeated customer ids, that list is wasted effort. Many optimizers rewrite both forms to the same semi-join, but EXISTS is the form that is dependably cheap, and it stays correct when NULLs appear.

-- may build a long list full of repeated ids
SELECT c.* FROM customers c
WHERE c.customer_id IN (SELECT customer_id FROM orders);

-- stops at the first matching order per customer
SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.customer_id);

Same customers, one row each, less work

Comparing each row with the previous one by self joining a table to itself reads the table twice and needs a join key that exactly lines up. A window function such as LAG does it in one pass over the data, sorted once. With several comparisons (previous day, previous week, next day), the self-join approach adds a join for each one, while a window adds just another column.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE daily (day INT, value INT)")
con.executemany("INSERT INTO daily VALUES (?, ?)",
                [(1, 10), (2, 15), (3, 13), (4, 20)])

self_join = con.execute(
    "SELECT t.day, t.value - p.value FROM daily t "
    "LEFT JOIN daily p ON p.day = t.day - 1 ORDER BY t.day").fetchall()
window = con.execute(
    "SELECT day, value - LAG(value) OVER (ORDER BY day) "
    "FROM daily ORDER BY day").fetchall()

print(self_join)
print(window)
print(self_join == window)

Both queries give the same deltas, but only the self join needs consecutive day numbers

The two agree here because the days have no gaps. If day 3 were missing, the self join would produce NULL for day 4, while LAG would still return the previous existing row. Check which behavior you actually want before you switch.

A hash join works by hashing an equality key, so it has nothing to hash for ON e.ts BETWEEN p.start_ts AND p.end_ts. A range predicate leaves the engine with a nested loop, or with a merge-style join over sorted input. Two things help: make sure a sorted index exists on the range column, and bound the range tightly so each row only searches a small window.

CREATE INDEX idx_events_ts ON events (ts);

SELECT e.id, p.label
FROM events e
JOIN periods p
  ON e.ts BETWEEN p.start_ts AND p.end_ts
WHERE e.ts >= DATE '2026-09-01'      -- bound the range
  AND e.ts <  DATE '2026-10-01';

The date window shrinks the outer side, and the index serves each probe

When tuning the query is not enough, change what the query has to do. Three common options trade extra storage or maintenance for faster reads.

CREATE MATERIALIZED VIEW order_totals AS
SELECT c.customer_id, c.name, SUM(o.amount) AS spend
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

REFRESH MATERIALIZED VIEW order_totals;

Dashboards read the small precomputed table instead of re-joining millions of rows

Plain EXPLAIN shows the plan the optimizer expects to run, with row counts that are guesses from statistics. EXPLAIN ANALYZE actually runs the query and reports the actual row counts and times beside the estimates. Estimated rows can be wrong by orders of magnitude, but actual rows are what happened. Because it executes the statement, wrap data-changing queries in a transaction you roll back.

Nested Loop  (cost=0.43..912.10 rows=40 width=24)
             (actual time=0.05..2840.30 rows=380000 loops=1)
  ->  Seq Scan on customers c  (rows=20 actual rows=19000 loops=1)
  ->  Index Scan on orders o   (rows=2 actual rows=20 loops=19000)

Expected 40 rows, got 380,000: the estimates were built on stale data

Read the plan from the innermost nodes outward and compare rows with actual rows at each node. The first node where they differ by about 10x is where the optimizer's picture went wrong, and every join above it inherits the error. Fix there first.

If the estimates are fine and the cost is a scan or a spill, add the missing index, trim the columns, or raise the memory for that query. Then measure again, because only a second EXPLAIN ANALYZE proves the change helped.

Nearly every join decision comes down to one question: which rows must survive even when they find no partner? Answer that and the join type follows. The tree below is the whole selection process, and each leaf is a join you already know from earlier sections.

Notice there is no RIGHT JOIN leaf. Needing all of B is just needing all of A with the tables written in the other order, so you swap them and keep reading top to bottom with the preserved table first.

The second thing to carry in your head is how many rows each join can possibly return. These bounds are your first sanity check on any result: if the count falls outside the range, the query is not doing what you think.

A second way to remember the types is by what each one keeps, using the picture of two overlapping circles. The 'intersection' is the set of rows that found a partner.

The most expensive join bugs come from two rules that people half-remember. The first: ON decides what matches; WHERE decides what survives. On an inner join the two are interchangeable, but on an outer join they happen at different moments. ON runs while pairs are being formed, before unmatched rows get their NULL padding. WHERE runs afterwards, on the finished rows, padding included.

Using the employees and departments tables from the running schema (six employees, one with no department, plus a childless 'Legal' department), here is the same filter placed in each spot. In ON, every employee stays and only the department name is blanked. In WHERE, the padded rows fail the test and vanish, so the LEFT JOIN quietly turns into an inner join.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('''
CREATE TABLE departments (dept_id INT, dept_name TEXT);
CREATE TABLE employees (emp_id INT, name TEXT, dept_id INT);
INSERT INTO departments VALUES (10,'Eng'),(20,'Sales'),(30,'Ops'),(40,'Legal');
INSERT INTO employees VALUES (1,'Ada',10),(2,'Ben',10),(3,'Cy',20),
  (4,'Di',20),(5,'Eve',30),(6,'Finn',NULL);
''')

in_on = db.execute('''
SELECT e.name, d.dept_name FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id AND d.dept_name = 'Eng'
''').fetchall()
in_where = db.execute('''
SELECT e.name, d.dept_name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Eng'
''').fetchall()
print(len(in_on), len(in_where))

Same predicate, two placements

The one deliberate exception is the anti-join filter, WHERE right.key IS NULL, which exists precisely to select the padded rows.

The second rule is about NULL, and it is an asymmetry. In a join predicate, NULL = NULL is UNKNOWN, so NULL never matches anything, not even another NULL. In UNION, INTERSECT, EXCEPT, GROUP BY, and DISTINCT, two NULLs count as the same value. The next example shows both behaviors side by side.

joined = db.execute(
    'SELECT 1 FROM (SELECT NULL AS k) a JOIN (SELECT NULL AS k) b ON a.k = b.k'
).fetchall()
common = db.execute('SELECT NULL INTERSECT SELECT NULL').fetchall()
print(len(joined), common)

NULL in a join versus in INTERSECT

Joins widen a result by adding columns; set operators lengthen it by adding rows. All four set operators below compare whole rows, treat NULLs as equal, and require both queries to have the same column count, order, and compatible types. That last requirement is the quickest sign you reached for the wrong tool.

You never choose the join algorithm directly, but you influence it through indexes, fresh statistics, and selective predicates. Knowing what each algorithm is good at tells you what a plan should look like when things are healthy.

When a result looks wrong, do not stare at the final query. Use the debug ritual: run the INNER JOIN first and count its rows, then widen to LEFT and see exactly which rows appeared. That difference is your unmatched data, named precisely.

FROM = 'SELECT e.name, d.dept_name FROM employees e {} departments d ON e.dept_id = d.dept_id'
inner = db.execute(FROM.format('JOIN')).fetchall()
left = db.execute(FROM.format('LEFT JOIN')).fetchall()
print(len(inner), len(left))
print([r for r in left if r not in inner])

The delta names the orphan employee

To find the other kind of unmatched row, the department nobody belongs to, swap the tables and keep only the padded rows.

rows = db.execute('''
SELECT d.dept_name FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL
''').fetchall()
print(rows)

Swap sides to see the childless department

When you review someone else's SQL, a handful of patterns deserve an immediate question. None is always wrong, but each is a bug until proven otherwise.