INNER vs LEFT vs FULL JOIN, With the Rows Each Returns

INNER vs LEFT vs FULL JOIN, With the Rows Each Returns, in one line: every join result is built from three groups of rows, the matched pairs, the left rows with no match and the right rows with no match, and the join type only decides which groups you keep. Count the three groups and you can predict the size of any join before running it. The queries below run on SQLite 3.37; native FULL OUTER JOIN support in SQLite and PostgreSQL is described as of October 2026.

Join	Rows returned	Lesson's shelter, plus one
INNER JOIN	matched	2
LEFT JOIN	matched + left-only	2 + 2 = 4
FULL OUTER JOIN	matched + left-only + right-only	2 + 2 + 1 = 5

The problem it solves

"How many rows will this return?" catches join bugs early. A report that should list every dog lists only adopted ones; a total doubles after someone adds a join; a cleanup query finds no orphans when there are hundreds. Each is a counting mistake, visible on paper once the result is split into its three groups. The pictures and the matching itself are covered in SQL joins explained visually; this article does the arithmetic.

The intuition

Take every key and count how many rows carry it on each side, say a on the left and b on the right.

• Matched pairs: a key present on both sides contributes a × b rows. One left row and one right row give one pair; three and two give six.
• Left-only rows: a left row whose key appears nowhere on the right contributes exactly one row in a LEFT or FULL join, with every right column NULL.
• Right-only rows: the mirror image, kept only by a RIGHT or FULL join.
• NULL keys never match, not even each other, so a row with a NULL key always lands in the "only" group of its side.

So the formulas are short:

• INNER = Σ a × b, summed over the shared keys.
• LEFT = INNER + left-only, and therefore never fewer rows than the left table has.
• FULL = LEFT + right-only.

Two consequences follow. The anti-join, LEFT JOIN ... WHERE right.key IS NULL, returns exactly the left-only group: LEFT − INNER rows. And a condition on the right table placed in WHERE runs after the join, where the padded rows hold NULL and fail it, so the left-only group is thrown away and the LEFT JOIN quietly returns INNER-sized results. Put that condition in ON, where it decides which right rows count as a match before any padding happens.

Watch it run

The animation uses the lesson's two registers, three dogs on the left and two adoptions on the right, with adoption.dog_id = dog.id as the condition. Each dog is offered to the condition in turn: Pepper finds a match, Rusty finds none, Nell is matched by Marta's row. INNER JOIN keeps the two pairs and drops Rusty; LEFT JOIN brings him back with NULL in every adoption column, and WHERE adoption.dog_id IS NULL turns that into the list of dogs still in the kennels. In counting terms: two matched pairs, one left-only row, no right-only rows.

The code

The three joins in SQL, written for an engine that has FULL OUTER JOIN:

SELECT d.name, a.adopter FROM dog d INNER JOIN adoption a ON a.dog_id = d.id;
SELECT d.name, a.adopter FROM dog d LEFT JOIN adoption a ON a.dog_id = d.id;
SELECT d.name, a.adopter FROM dog d FULL OUTER JOIN adoption a ON a.dog_id = d.id;

-- dogs nobody has adopted: the left-only group
SELECT d.name FROM dog d LEFT JOIN adoption a ON a.dog_id = d.id
WHERE a.dog_id IS NULL;

The lesson's shelter with one more dog, Bramble, who is not adopted, and one adoption for dog 5, who is missing from the register. SQLite 3.37 has no FULL OUTER JOIN, so it is emulated: the LEFT JOIN, then UNION ALL the right rows that found no dog. Rows are printed in the order SQLite returned them:

import sqlite3

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

INNER = """SELECT d.name, a.adopter FROM dog d
           JOIN adoption a ON a.dog_id = d.id"""
LEFT = """SELECT d.name, a.adopter FROM dog d
          LEFT JOIN adoption a ON a.dog_id = d.id"""
RIGHT_ONLY = """SELECT d.name, a.adopter FROM adoption a
                LEFT JOIN dog d ON d.id = a.dog_id
                WHERE d.id IS NULL"""            # adoptions whose dog is missing
FULL = LEFT + " UNION ALL " + RIGHT_ONLY         # SQLite 3.37 has no FULL JOIN

for label, sql in (("INNER", INNER), ("LEFT", LEFT), ("FULL", FULL)):
    rows = db.execute(sql).fetchall()
    print(label, len(rows), rows)
# INNER 2 [('Pepper', 'Yusuf'), ('Nell', 'Marta')]
# LEFT 4 [('Pepper', 'Yusuf'), ('Rusty', None), ('Nell', 'Marta'), ('Bramble', None)]
# FULL 5 [('Pepper', 'Yusuf'), ('Rusty', None), ('Nell', 'Marta'), ('Bramble', None), (None, 'Ines')]

matched = len(db.execute(INNER).fetchall())
left_only = len(db.execute(LEFT + " WHERE a.dog_id IS NULL").fetchall())   # the anti-join
right_only = len(db.execute(RIGHT_ONLY).fetchall())
print(matched, left_only, right_only)                     # 2 2 1
print(matched, matched + left_only, matched + left_only + right_only)      # 2 4 5

Two matched, two left-only, one right-only, and the totals fall out as 2, 4 and 5. The anti-join counted the left-only group directly.

Now the three traps. A many-to-many join, the same filter in WHERE and in ON, and why the emulation must use UNION ALL and not UNION:

db.executescript("""
  CREATE TABLE application (dog_id INTEGER, applicant TEXT);
  CREATE TABLE walk (dog_id INTEGER, walker TEXT);
  INSERT INTO application VALUES (1, 'Yusuf'), (1, 'Omar'), (1, 'Lena'), (3, 'Marta');
  INSERT INTO walk VALUES (1, 'Sam'), (1, 'Ade'), (3, 'Kit');
""")
rows = db.execute("""SELECT p.dog_id, p.applicant, w.walker FROM application p
                     JOIN walk w ON w.dog_id = p.dog_id""").fetchall()
print(len(rows), sum(1 for r in rows if r[0] == 1))      # 7 6   <- 3 x 2 for dog 1, 1 x 1 for dog 3

in_where = db.execute("""SELECT d.name, a.adopter, a.year FROM dog d
                         LEFT JOIN adoption a ON a.dog_id = d.id
                         WHERE a.year = 2026""").fetchall()
in_on = db.execute("""SELECT d.name, a.adopter, a.year FROM dog d
                      LEFT JOIN adoption a ON a.dog_id = d.id AND a.year = 2026""").fetchall()
print(len(in_where), in_where)    # 1 [('Nell', 'Marta', 2026)]
print(len(in_on), in_on)
# 4 [('Pepper', None, None), ('Rusty', None, None), ('Nell', 'Marta', 2026), ('Bramble', None, None)]

db.execute("INSERT INTO adoption VALUES (3, 'Marta', 2026)")  # the same adoption entered twice
print(len(db.execute(FULL).fetchall()),                        # 6
      len(db.execute(FULL.replace("UNION ALL", "UNION")).fetchall()))   # 5  <- a real row lost

Dog 1 has three applications and two walkers, so it contributes 3 × 2 = 6 rows, and dog 3 adds 1 × 1. With a.year = 2026 in WHERE, the LEFT JOIN returns one row, not the four dogs it promised. In ON, all four dogs survive, and Pepper is now unmatched because his only adoption is from 2025. The last line enters Marta's adoption twice: the result should hold both copies, and UNION silently merges them, because it removes duplicate rows. The reasons are in UNION vs UNION ALL.

Finally, the formulas from the intuition section checked against SQLite on 500 random table pairs full of repeated and NULL keys. The emulated FULL join tests l.rowid IS NULL, a column that can never be NULL in a real row:

import random
from collections import Counter

def predicted(left, right):
    """Row counts from the arithmetic alone. NULL keys never match."""
    a = Counter(k for k in left if k is not None)
    b = Counter(k for k in right if k is not None)
    matched = sum(a[k] * b[k] for k in a)
    left_only = sum(1 for k in left if k is None or b[k] == 0)
    right_only = sum(1 for k in right if k is None or a[k] == 0)
    return matched, matched + left_only, matched + left_only + right_only

random.seed(18)
ok = True
for _ in range(500):
    left = [random.choice([None, 1, 2, 3]) for _ in range(random.randint(0, 7))]
    right = [random.choice([None, 1, 2, 3, 4]) for _ in range(random.randint(0, 7))]
    t = sqlite3.connect(":memory:")
    t.executescript("CREATE TABLE l (k); CREATE TABLE r (k);")
    t.executemany("INSERT INTO l VALUES (?)", [(k,) for k in left])
    t.executemany("INSERT INTO r VALUES (?)", [(k,) for k in right])
    count = lambda sql: t.execute(f"SELECT COUNT(*) FROM ({sql})").fetchone()[0]
    inner = count("SELECT l.k FROM l JOIN r ON l.k = r.k")
    left_join = count("SELECT l.k FROM l LEFT JOIN r ON l.k = r.k")
    full = count("""SELECT l.k FROM l LEFT JOIN r ON l.k = r.k
                    UNION ALL
                    SELECT r.k FROM r LEFT JOIN l ON l.k = r.k WHERE l.rowid IS NULL""")
    ok &= (inner, left_join, full) == predicted(left, right)
print(ok)                                                  # True

The complexity

• Output size first: a key shared by 1,000 rows on each side yields a million rows on its own, and no algorithm is faster than the rows it must return.
• Finding the pairs: a nested loop costs O(n × m) comparisons; an index on the right key gives about O(n log m), and a hash join about O(n + m) plus memory for one side.
• The FULL emulation runs two joins, about twice the work of a LEFT JOIN.

Where it goes wrong

• Filtering the right table in WHERE after a LEFT JOIN. The left-only rows fail the test and vanish. Move the condition into ON.
• Joining two child tables through one parent. They multiply; aggregate each child per key first.
• Testing the wrong column in an anti-join. Check the join key or the primary key of the right table; a column that is legitimately NULL in matched rows gives false orphans.
• Building FULL with UNION. It merges genuine duplicate rows; use UNION ALL.
• Assuming FULL is everywhere. MySQL's join syntax lists no FULL OUTER JOIN, so the same emulation is the usual answer there.

When it shows up in interviews

Expect the definition, then a count: "A has 5 rows, B has 3, how many does the LEFT JOIN return?". The honest answer is "at least 5, depending on duplicates", plus the formula. Follow-ups are the anti-join ("customers with no orders") and the WHERE versus ON trap. The matching itself is the subject of the INNER and OUTER JOINs lesson, and practice queries are collected in SQL query interview questions.

How to say it in an interview

"I split any join result into three groups: matched pairs, left rows with no match and right rows with no match. INNER returns the matched pairs, LEFT adds the left-only rows padded with NULL, and FULL adds the right-only rows too. Matched pairs multiply, a key with a rows on one side and b on the other gives a × b rows, and NULL keys never match. For rows with no match I use a LEFT JOIN with WHERE right.key IS NULL, and conditions on the right table go in ON, because in WHERE they discard the padded rows. Without FULL, I write the LEFT JOIN plus a UNION ALL of the unmatched right rows."

Frequently asked questions

What is the difference between INNER, LEFT and FULL JOIN?

INNER JOIN returns only the matched pairs. LEFT JOIN returns the matched pairs plus every left row with no match, with NULL in the right columns. FULL OUTER JOIN returns all of that plus every right row with no match. The matching rule is the same; only the kept groups differ.

Can a LEFT JOIN return more rows than the left table?

Yes. Every left row appears at least once, but a left row that matches several right rows appears once per match. A key with three rows on the left and two on the right produces six rows, so LEFT JOIN results are often larger than the left table.

Does SQLite support FULL OUTER JOIN?

Yes, since version 3.39.0; PostgreSQL supports it too, as of October 2026. On older SQLite, as in this article, or on MySQL, emulate it: the LEFT JOIN, then UNION ALL the right rows whose left match is missing. Use UNION ALL, not UNION, so genuine duplicate rows survive.

Sources

• SELECT, join operators — SQLite Documentation
• SQLite Release 3.39.0 — SQLite Documentation
• Table Expressions, joined tables — PostgreSQL Documentation
• JOIN Clause — MySQL 8.4 Reference Manual