What a SQL join Venn diagram shows and where it misleads

In this article

1. Before you start
2. Why interviewers ask this
3. The SQL join Venn diagram from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

Why interviewers ask this

A candidate who says "an inner join is the overlap of two circles" has given the picture and skipped the mechanism. SQL screens, including the ones inside a data engineering loop (the interview loops guide shows how loops are staged) and the SQL problems in the coding rounds described in coding interviews, use joins to test whether you can predict a row count before running the query. The usual prompts are "how many rows does this inner join return", "how do you find customers with no orders", and "why did my join double the totals".

A weak answer recites the Venn pictures. A strong answer says a join is all pairs of rows filtered by a condition, then predicts the count by multiplying duplicates and treating NULL keys as unmatched.

The SQL join Venn diagram from first principles

In plain words

Join predicate

A join predicate is the condition, written after the word on, that decides whether a pair of rows is kept. Only true keeps a pair; false or NULL drops it.

Outer join

An outer join is a join that also keeps rows with no partner, filling the other table's columns with NULL. A left outer join keeps every left row, a right outer join every right row, and a full outer join both.

Picture two guest lists for a wedding, one per side. A Venn diagram, the overlapping-circles picture from set theory that probability and statistics also uses, shows who is on both lists. That works when each guest appears once. A join instead makes a seating pair for every combination that meets a rule, so a guest listed twice is paired twice.

The analogy breaks at a blank name: a host might seat two unnamed guests together, but a join never pairs two NULL keys.

Precisely, a join is every pair of rows that satisfies a predicate, plus unmatched rows once each for an outer join.

The examples use two small tables. The customers table holds four customers: Ada, Ben, Cy and Di. The orders table holds five orders: order 10 and order 11 belong to customer 1 (Ada), order 12 to customer 2 (Ben), order 13 to a customer 5 who does not exist in the table, and order 14 has a NULL customer.¹

c and o in the queries below are short names for the two tables.

CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO customers VALUES (1,'Ada'),(2,'Ben'),(3,'Cy'),(4,'Di');
INSERT INTO orders VALUES (10,1),(11,1),(12,2),(13,5),(14,NULL);

What the Venn picture shows

Two circles stand for the two tables, left and right, and the shaded part is the rows the join keeps. On the example tables, the overlap holds the matched rows: Ada and Ben on the left, orders 10, 11 and 12 on the right. Cy and Di sit in the left-only part, and orders 13 and 14 in the right-only part.

The shading is a fair guide to which rows qualify. It says nothing about how many rows result, because it counts values rather than pairs, and it has no shape at all for a cross join. The steps below build the row counts from first principles, starting from the pairs.

Step 1: A join starts from every pair

Every join conceptually starts from the cross join, the list of all pairs of rows. The PostgreSQL 18 documentation states that for tables of N and M rows a cross join "will have N * M rows", and that a cross join is equivalent to an inner join whose condition is always true, a pair of rows matching only when its join condition evaluates to true. With four customers and five orders there are 20 pairs.¹

Databases do not usually materialise all 20 pairs; the optimiser can use indexes and hashing to reach the same answer. The pair model is the way to reason about the result, not a statement about speed. Eder (2016) puts the idea this way: a join is always a cross product with a predicate.

The input is two tables. The output is the 20 candidate pairs, before any condition is applied.

Step 2: A join returns pairs, not values

An inner join keeps the pairs where the predicate is true, so it returns one row per matching pair rather than one row per matching value. In the example, customers.id = orders.customer_id is true for three pairs: Ada with order 10, Ada with order 11, and Ben with order 12. Only two customers are in the overlap, yet the result has three rows, because Ada matches twice.¹

SELECT c.name, o.id
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

A Venn diagram counts values in the overlap. The join counts pairs. With duplicate keys on both sides the gap grows: two rows with key 1 on the left and two on the right produce four result rows from one shared value.¹ This is why a join "doubles the totals" in a report.

The input is the 20 pairs. The output is the 3 pairs where the condition is true.

Step 3: A left join pads the unmatched left rows

A left outer join returns every inner-join row and then adds each left row that had no partner, with NULL in the right-hand columns. The PostgreSQL documentation describes it as an inner join followed by one added row, with nulls, for each unmatched row of the left table, so "the joined table always has at least one row for each row in T1".

Here Cy and Di have no orders, so the left join returns the 3 inner rows plus 2 padded rows, 5 in total.¹ Keeping only the padded rows leaves exactly Cy and Di, the classic answer to "customers with no orders". The test is a NULL check on o.id in the where clause, as the query below shows, which works because id is the orders table's primary key, so a real order never has a NULL id and a NULL can only come from padding. This is the deliberate exception to the rule in the common mistakes below.

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

The input is the 3 inner rows. The output adds one padded row per unmatched customer.

Step 4: Right and full joins pad the other side

A right outer join is the mirror image, and a full outer join pads both sides. The same documentation says a right join always has a row for each row of the right table, and a full join adds the unmatched rows of both tables. Orders 13 and 14 have no customer, so the right join returns 3 + 2 = 5 rows and the full join returns 3 + 2 + 2 = 7.¹

Support differs by engine and version. SQLite added support for right and full outer joins in version 3.39.0, released June 2022, as its release notes record, so SQLite before 3.39.0 rejects them. Other engines differ too, so confirm the engine supports a full join before you rely on it.

The input is the left-join result. The output adds the unmatched orders.

1 · All pairs2 · Match3 · Pad the left4 · Pad the rightReplay

Step-by-step description

1. Four customers times five orders is 20 pairs.
2. The inner join keeps the 3 pairs where the condition is true.
3. Cy and Di are added with NULL orders: 5 rows.
4. Orders 13 and 14 are added with NULL customers: 7 rows.

• Four customers and five orders give 20 pairs.
• The inner join keeps 3 pairs, the left join returns 5 rows and the full join returns 7 rows.

Worked example

The row counts were produced by a short script that applies the join rules to the example rows.¹ The overlap holds two shared key values (1 and 2), yet the inner join returns three rows, because the count is a sum of products per key value, not a count of values.

Text description of the diagram

• The inner join returns 3 rows and the left join returns 5.
• The right join returns 5 rows, the full join 7 and the cross join 20.

The first two rows are explained in the next section; the last three use two single-column tables holding the values 1, 1 and NULL.¹

Trade-offs and edge cases

The Venn picture is a fair memory aid for which rows qualify and a poor model of the result, so know the places it fails.

• Duplicates multiply. Matching keys produce a row for every combination, so the result can be larger than either table. For each key value, multiply its left count by its right count and add the products (1 x 2 + 1 x 1 = 3 here, and 2 x 2 = 4 for the keys 1, 1, NULL); an outer join then adds one row per unmatched row.
• NULL keys never match under an equals condition. A predicate that compares NULL with anything is neither true nor false, and the PostgreSQL documentation says a row is kept only when the condition is true, so NULL keys fall out of an inner join. A null-safe comparison such as is not distinct from in PostgreSQL does match NULLs, which is the explicit way to handle them. Set operators behave differently: intersect treated the two NULLs as the same value in the example, giving two shared values where the inner join matched four pairs.¹
• The join condition and the where clause differ for outer joins. The join condition is what follows on; the where clause filters after the join. The PostgreSQL documentation says a restriction in the join condition is applied before the join and one in the where clause after it, which "matters a lot with outer joins". In the example, o.id >= 12 inside the on condition returned 4 rows, and the same test in the where clause returned 1, because a test that rejects NULL removes the padded rows and makes the left join behave like an inner join.¹
• A cross join has no picture. Atwood (2007) is the post that popularised the Venn pictures for joins, and even he notes that a cross join "can't be expressed as a Venn diagram". Eder (2016) is the critique: a join is always a cross product with a predicate.
• A natural join is risky. It joins on every column name the tables share, so a later schema change can alter the result silently, a warning in the PostgreSQL documentation.

Text description of the diagram

• Duplicate keys multiply rows.
• NULL keys never match under an equals condition.

How to answer it in an interview

The spoken structure is: concede what the picture gets right, switch to pairs, show the duplicate and NULL consequences, then name where a right-table filter goes.

Common mistakes

• "An inner join returns the rows in the overlap" (wrong). It returns one row per matching pair, so duplicates multiply (right: count pairs, not values).
• "A left join keeps only the left table's columns" (wrong). It keeps all left rows and also the matching right columns, with NULL where nothing matched (right: say which columns come back NULL).
• "Put every filter in the where clause" (wrong). A right-table condition that rejects NULL, such as o.id >= 12, removes the padded rows there and the left join behaves like an inner join (right: put it in the on condition to keep every left row; a NULL check in the where clause is the deliberate anti-join exception from Step 3).
• "NULL equals NULL, so null keys join" (wrong). With an equals condition the comparison is not true, so those rows stay unmatched (right: join on a non-null key, or compare with a null-safe operator).
• "A right join is a different operation from a left join" (wrong). It is a left join with the tables swapped, so swapping the table order and using a left join gives the same rows (right: pick one direction and keep it consistent).

Follow-up questions

Is a SQL join the same as a set intersection?

No. An intersection returns distinct shared values and treats NULLs as equal, while an inner join returns one row per matching pair and drops NULL keys. In the example the intersection held 2 values and the join returned 4 rows.

Why does my inner join return more rows than either table has?

Duplicate keys on both sides multiply: a key that appears 2 times in one table and 2 times in the other yields 4 rows. Check uniqueness of the join key on at least one side, or aggregate one side before joining. Designing the tables so the join key is unique on one side, a data-modelling choice that system design rounds also weigh, prevents the multiplication at its source.

How do you find the rows in one table with no match in the other?

Left join, then keep the rows where the right table's join column (o.customer_id) or its primary key (o.id) is NULL, as in Step 3, which returned Cy and Di. Testing a nullable column that is not the join key can also return matched rows that hold NULL in it. A not-exists subquery gives the same answer and avoids the padding step.

Does moving a filter out of the join condition change a left join?

Yes. In the example the filter inside the join condition kept 4 rows and the same filter in the where clause kept 1, because the where clause ran after the padding and removed the NULL rows.

Sources

1. PostgreSQL 18 documentation, 7.2. Table Expressions (joined tables) — PostgreSQL Global Development Group (page shows version 18.6; read 2026-10-24), 24 Oct 2026
2. SQLite release 3.39.0 release notes — SQLite project, 25 Jun 2022
3. A Visual Explanation of SQL Joins — Coding Horror (Jeff Atwood), 11 Oct 2007
4. Say NO to Venn Diagrams When Explaining JOINs — jOOQ blog (Lukas Eder), 5 Jul 2016