SQL Joins Explained Visually: INNER, LEFT and FULL OUTER

Joins are usually taught with two overlapping circles. The picture is memorable and a little misleading: it suggests a join is a set operation on values, when it is really a pairing of rows. That difference is exactly where joins surprise people — rows that multiply, rows that vanish, and NULLs that match nothing. This article builds the join from the row up and runs every query on SQLite, so each result below is real output.

The problem it solves

Data about one thing is spread across tables so that each fact is stored once. A rescue shelter keeps dogs in one table and adoptions in another; an adoption row points at a dog by dog_id. To answer "who adopted which dog?" you have to line the two tables up again. A join does that: it pairs rows from the left table with rows from the right table wherever a condition — usually matching keys — holds.

The only real decision is what happens to a row that finds no partner.

The intuition

Think of a join as a nested loop. For every row on the left, scan every row on the right and emit one output row for each pair that satisfies the ON condition. Databases use faster plans — hash tables, sorted merges, index lookups — but they must return the same rows as this loop.

That loop explains the join types:

• INNER JOIN emits only the pairs that matched. A left row with no partner produces nothing.
• LEFT JOIN emits the matched pairs too, but a left row with no partner is still emitted once, with every right-hand column filled with NULL.
• RIGHT JOIN is the same with the roles swapped.
• FULL OUTER JOIN keeps the unmatched rows from both sides.

It also explains two things the circles hide. A left row that matches three right rows appears three times. And a NULL key never matches anything, not even another NULL, because NULL = NULL is not true in SQL.

Watch it run

The animation uses the lesson's shelter: Pepper, Rusty and Nell on the left, adoptions for dogs 1 and 3 on the right. Each dog is offered to the condition in turn. Pepper and Nell find partners and Rusty does not. The INNER JOIN drops Rusty; the LEFT JOIN brings him back with a NULL adopter, and filtering on that NULL gives the list of dogs still waiting.

The code

The same shelter, with one extra adoption row that points at a dog, 4, which is missing from the dog table. The SQLite used here is version 3.37, which has no FULL OUTER JOIN keyword (it arrived in a later version), so the last query builds one from a LEFT JOIN plus the unmatched right-hand rows:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE dog (id INTEGER, name TEXT);
  CREATE TABLE adoption (dog_id INTEGER, adopter TEXT);
  INSERT INTO dog VALUES (1, 'Pepper'), (2, 'Rusty'), (3, 'Nell');
  INSERT INTO adoption VALUES (1, 'Yusuf'), (3, 'Marta'), (4, 'Ines');
""")

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

show("""SELECT d.name, a.adopter FROM dog d
        INNER JOIN adoption a ON a.dog_id = d.id ORDER BY d.id""")
# [('Pepper', 'Yusuf'), ('Nell', 'Marta')]

show("""SELECT d.name, a.adopter FROM dog d
        LEFT JOIN adoption a ON a.dog_id = d.id ORDER BY d.id""")
# [('Pepper', 'Yusuf'), ('Rusty', None), ('Nell', 'Marta')]

show("""SELECT d.name FROM dog d
        LEFT JOIN adoption a ON a.dog_id = d.id
        WHERE a.dog_id IS NULL""")
# [('Rusty',)]

show("""SELECT d.name, a.adopter FROM dog d
        LEFT JOIN adoption a ON a.dog_id = d.id
        UNION ALL
        SELECT NULL, a.adopter FROM adoption a
        LEFT JOIN dog d ON a.dog_id = d.id
        WHERE d.id IS NULL""")
# [('Pepper', 'Yusuf'), ('Rusty', None), ('Nell', 'Marta'), (None, 'Ines')]

The third query is the anti-join: keep every dog, then keep only those whose match came back empty. Test the right table's join key for NULL, not some other column that might legitimately be NULL in a matched row.

Now the surprises. Pepper is returned and adopted again by Omar, and an adoption row arrives with no dog_id at all:

db.execute("INSERT INTO adoption VALUES (1, 'Omar'), (NULL, 'Lena')")

show("""SELECT d.name, a.adopter FROM dog d
        INNER JOIN adoption a ON a.dog_id = d.id ORDER BY d.id, a.adopter""")
# [('Pepper', 'Omar'), ('Pepper', 'Yusuf'), ('Nell', 'Marta')]

show("""SELECT d.name, a.adopter FROM dog d
        LEFT JOIN adoption a ON a.dog_id = d.id
        WHERE a.adopter <> 'Omar' ORDER BY d.id""")
# [('Pepper', 'Yusuf'), ('Nell', 'Marta')]

show("""SELECT d.name, a.adopter FROM dog d
        LEFT JOIN adoption a ON a.dog_id = d.id AND a.adopter <> 'Omar'
        ORDER BY d.id""")
# [('Pepper', 'Yusuf'), ('Rusty', None), ('Nell', 'Marta')]

show("SELECT COUNT(*) FROM adoption a JOIN adoption b ON a.dog_id = b.dog_id")
# [(6,)]

Pepper now appears twice, because two adoption rows match him. The two middle queries differ only in where the filter sits. In WHERE, it runs after the join, and Rusty's padded NULL adopter fails <> 'Omar' — the comparison with NULL is unknown, which WHERE treats as false — so the LEFT JOIN has quietly become an INNER one. In ON, it is part of the matching, and Rusty survives. The last line joins the five adoption rows to themselves: dog 1's two rows pair four ways, dogs 3 and 4 pair once each, and Lena's NULL row does not even match itself. Six.

Finally, the nested loop from the intuition section is written out in Python and compared with SQLite on 500 pairs of random tables full of duplicate and NULL keys:

import random

def nested_loop(left, right, keep_unmatched):   # the definition, row by row
    out = []
    for l in left:
        matched = [(l[1], r[1]) for r in right
                   if l[0] is not None and l[0] == r[0]]   # NULL = x is never true
        out += matched or ([(l[1], None)] if keep_unmatched else [])
    return out

def ordered(rows):
    return sorted(rows, key=repr)

random.seed(8)
ok = True
for _ in range(500):
    left = [(random.choice([None, 1, 2, 3]), f"L{i}") for i in range(random.randint(0, 6))]
    right = [(random.choice([None, 1, 2, 3, 4]), f"R{i}") for i in range(random.randint(0, 6))]
    t = sqlite3.connect(":memory:")
    t.executescript("CREATE TABLE l (k, v); CREATE TABLE r (k, v);")
    t.executemany("INSERT INTO l VALUES (?, ?)", left)
    t.executemany("INSERT INTO r VALUES (?, ?)", right)
    for kind, keep in (("INNER", False), ("LEFT", True)):
        got = t.execute(f"SELECT l.v, r.v FROM l {kind} JOIN r ON l.k = r.k").fetchall()
        ok &= ordered(got) == ordered(nested_loop(left, right, keep))
print(ok)                                           # True

The results are compared as sorted lists because SQL promises no row order without ORDER BY.

The complexity

The nested loop is O(n × m) comparisons, and a database falls back to it only when it has nothing better. With an index on the right table's key, each left row costs one index lookup, roughly O(n log m). A hash join builds a hash table on one side and probes it with the other, about O(n + m) plus memory for the table. The output size is its own cost: a key shared by a rows on the left and b rows on the right produces a × b rows, which is how an innocent join turns into millions of rows.

Where it goes wrong

• Filtering the right table in WHERE after a LEFT JOIN. Shown above: it removes the padded rows and silently becomes an INNER JOIN. Put conditions on the right table in ON.
• Duplicate matches inflating totals. A SUM over a join counts a left row once per match. Aggregate the many-side first, or join on a unique key.
• Expecting NULL keys to match. They never do. Use IS NULL for the anti-join, and clean or coalesce keys deliberately if two NULLs should mean the same thing.
• Assuming a row order. Add ORDER BY whenever order matters, including in tests.

How to say it in an interview

"A join pairs each left row with every right row that satisfies the ON condition. INNER keeps only matched pairs; LEFT also keeps unmatched left rows with NULLs on the right; FULL OUTER keeps unmatched rows from both sides. A row matching several rows appears several times, NULL keys never match, and a WHERE filter on the right table after a LEFT JOIN discards the unmatched rows — so that condition belongs in ON."

To see how the database actually runs a join, reading a query plan is the next step.