A JOIN combines rows from two row sets by pairing them according to a condition, and it is the operation that makes a normalised schema usable: customers live in one table, orders in another, and a join stitches them back together at query time. Interviewers probe it because the syntax is easy but the behaviour is full of traps: which rows disappear, which turn into NULLs, how one wrong condition multiplies row counts, and why a WHERE filter can quietly turn a LEFT JOIN into an INNER JOIN. For a Backend or AI engineer, joins are behind most list endpoints, reports, and data-cleaning jobs, and a bad join is one of the most common causes of both wrong numbers and slow queries.
-- Test data (sqlite3 :memory:)
-- customers: (1,'Asha') (2,'Ben') (3,'Chen')
-- orders: (10,cust 1,50) (11,cust 1,70) (12,cust 2,20) (13,cust NULL,99)
SELECT c.name, o.id AS order_id, o.total
FROM customers c INNER JOIN orders o ON o.customer_id = c.id;
-- name order_id total
-- Asha 10 50
-- Asha 11 70
-- Ben 12 20 <- Chen (no orders) and order 13 (no customer) are dropped
SELECT c.name, o.id AS order_id, o.total
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;
-- name order_id total
-- Asha 10 50
-- Asha 11 70
-- Ben 12 20
-- Chen NULL NULL <- kept, right-side columns filled with NULL
-- Anti-join: customers who never ordered
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- ChenAn INNER JOIN keeps only rows where the join condition is true on both sides. A LEFT JOIN keeps every row from the left table and, when no right-side row matches, still emits the left row with NULLs in all right-side columns. So the real question to ask is "may a row on this side legitimately have no partner?" If a customer with no orders still belongs in the report (a "customers and their order counts" page), you need LEFT; if you only care about pairs that exist, INNER is correct and gives the planner more freedom to choose which side to scan first, because an inner join is commutative and outer joins are not.
The LEFT JOIN ... WHERE right.id IS NULL idiom is the anti-join: it finds left rows with no match. The subtlety that catches people is where a filter on the right table goes:
-- Filter on the right table in WHERE: unmatched rows have NULL total,
-- NULL > 30 is not true, so Ben and Chen vanish. LEFT JOIN behaves like INNER.
SELECT c.name, o.id AS order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 30;
-- Asha 10
-- Asha 11
-- Same filter in ON: it only decides which orders MATCH; every customer is kept.
SELECT c.name, o.id AS order_id, o.total
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 30;
-- name order_id total
-- Asha 10 50
-- Asha 11 70
-- Ben NULL NULL <- Ben's order (20) did not qualify as a match
-- Chen NULL NULLThe difference is timing. ON is applied while pairing rows, before the outer join pads unmatched rows with NULLs, whereas WHERE runs afterwards on the padded result. For an INNER JOIN the two placements give the same answer, which is why the mistake only bites with outer joins.
SELECT c.name, o.id AS order_id, o.total
FROM customers c FULL OUTER JOIN orders o ON o.customer_id = c.id;
-- name order_id total
-- NULL 13 99 <- order with no customer (customer_id is NULL)
-- Asha 10 50
-- Asha 11 70
-- Ben 12 20
-- Chen NULL NULL <- customer with no orders
-- (RIGHT JOIN = LEFT JOIN with the tables swapped: keeps all orders)
-- CROSS JOIN: every left row paired with every right row, no condition
-- sizes: S, M colors: red, blue, green
SELECT s, c FROM sizes CROSS JOIN colors;
-- S red | S blue | S green | M red | M blue | M green (2 x 3 = 6 rows)
-- SELF JOIN: a table joined to itself, using aliases to tell the copies apart
-- emp: (1,Maya,NULL) (2,Noah,1) (3,Omar,1) (4,Pia,2)
SELECT e.name AS employee, m.name AS manager
FROM emp e LEFT JOIN emp m ON e.manager_id = m.id ORDER BY e.id;
-- employee manager
-- Maya NULL <- top of the tree has no manager; LEFT JOIN keeps her
-- Noah Maya
-- Omar Maya
-- Pia Noah
-- Pairs of colleagues with the same manager (a.id < b.id avoids A-A and B-A duplicates)
SELECT a.name, b.name FROM emp a JOIN emp b
ON a.manager_id = b.manager_id AND a.id < b.id;
-- Noah OmarFULL OUTER JOIN is the union of "left join" and "right join" results: unmatched rows from either side are kept and padded with NULLs. It is the natural tool for reconciliation ("which orders have no customer and which customers have no orders?"). Support varies: PostgreSQL and SQL Server support it, SQLite since 3.39, but MySQL/MariaDB do not, so you emulate it with a LEFT JOIN unioned with a RIGHT JOIN (or an anti-join). RIGHT JOIN is rarely needed, since you can always swap the table order and use LEFT JOIN, which most teams prefer for readability.
A CROSS JOIN produces the cartesian product, which is useful on purpose (generate all size and colour combinations, a calendar of dates times a list of stores) and disastrous by accident. A self join is not a special join type, just an ordinary join where both sides happen to be the same table under different aliases; it is how you follow a parent pointer stored in the same table, though for arbitrary depth you would use a recursive CTE instead.
SELECT COUNT(*) FROM customers c JOIN orders o; -- SQLite allows JOIN without ON
-- 12 -- 3 customers x 4 orders
SELECT COUNT(*) FROM customers, orders; -- old comma syntax, no WHERE
-- 12
-- A filter without a join condition does not fix it:
SELECT c.name, o.id, o.total
FROM customers c, orders o WHERE o.total > 60;
-- name id total
-- Asha 11 70
-- Asha 13 99
-- Ben 11 70
-- Ben 13 99
-- Chen 11 70
-- Chen 13 99 <- every customer "owns" both big orders. Wrong, and it looks plausible.
Row growth of a missing condition:
1,000 users x 5,000 orders = 5,000,000 rows
1,000,000 users x 10,000,000 orders = 10^13 rows (the query will not finish)Without a condition, every left row is paired with every right row, so the result has |left| x |right| rows. On tiny test data this is invisible (12 rows), which is exactly why it slips through review and then takes down production when the tables are large: the database burns CPU and memory generating and shipping a result nobody wanted. Strictly, PostgreSQL and SQL Server require ON for an explicit INNER JOIN and raise a syntax error; SQLite and MySQL accept it, and the old comma syntax with a forgotten WHERE is accepted everywhere, so the safest habit is always to write JOIN ... ON.
A related and subtler mistake is a join condition that is present but wrong, such as joining on a non-unique column. That is a many-to-many join and it duplicates rows, which corrupts aggregates:
-- orders total = 239 (10:50 + 11:70 + 12:20 + 13:99).
-- payments: (order 10, 25) (order 10, 25) (order 11, 70) -- order 10 was paid in two parts
SELECT SUM(o.total) AS inflated
FROM orders o JOIN payments p ON p.order_id = o.id;
-- 170 <- order 10 counted twice (50 + 50 + 70)
SELECT SUM(o.total) AS correct
FROM orders o WHERE o.id IN (SELECT order_id FROM payments);
-- 120 <- each order counted once (50 + 70)This is the "fan-out" problem: joining a parent to a child that has many rows per parent repeats the parent's columns once per child, so summing a parent column afterwards over-counts. The defence is to know the cardinality (one-to-one, one-to-many, many-to-many) of every join, aggregate the child side in a subquery or CTE before joining, or use EXISTS/IN when you only need to test for existence. A quick sanity check is to compare COUNT(*) before and after adding a join.
-- Customers who have at least one order, three ways
SELECT c.name FROM customers c WHERE c.id IN (SELECT customer_id FROM orders);
-- Asha, Ben
SELECT c.name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Asha, Ben
SELECT c.name FROM customers c JOIN orders o ON o.customer_id = c.id;
-- Asha, Asha, Ben <- JOIN repeats Asha once per order; need DISTINCT to match
-- Customers with NO orders: NOT IN vs NOT EXISTS (orders.customer_id contains a NULL)
SELECT c.name FROM customers c WHERE c.id NOT IN (SELECT customer_id FROM orders);
-- (no rows) <- x NOT IN (1, 2, NULL) is never true: comparing with NULL is unknown
SELECT c.name FROM customers c WHERE NOT EXISTS
(SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Chen <- correctSubquery and join are often interchangeable in what they return but not in what they mean. A join combines columns from both tables and can multiply rows; a semi-join subquery (IN/EXISTS) only tests existence and never duplicates the outer row. So use a join when you need columns from the other table, and EXISTS when you only need to filter. Modern optimizers in PostgreSQL, MySQL 8 and SQL Server frequently rewrite IN/EXISTS subqueries into semi-joins and generally produce similar plans, so the correct answer to "which is faster?" is "it depends on the engine, the data and the indexes; compare with EXPLAIN", not a rule.
The NOT IN trap is a correctness issue rather than a performance one: if the subquery returns even one NULL, NOT IN returns no rows at all because three-valued logic makes the comparison unknown. NOT EXISTS (or a LEFT JOIN ... IS NULL anti-join) does not have that problem. Correlated subqueries in the SELECT list (one lookup per outer row) are the form most likely to be slow if the planner cannot decorrelate them.
-- users: (1,[email protected]) (2,[email protected]) (3,[email protected]) (4,[email protected]) (5,[email protected]) (6,[email protected])
-- 1) Which values are duplicated, and how often?
SELECT email, COUNT(*) AS n FROM users GROUP BY email HAVING COUNT(*) > 1;
-- email n
-- [email protected] 3
-- [email protected] 2
-- 2) Which specific rows are the extras (keep the first by id)?
WITH t AS (SELECT id, email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users)
SELECT id, email FROM t WHERE rn > 1 ORDER BY id;
-- 3 [email protected] | 4 [email protected] | 6 [email protected]
-- 3) Self join version (note: id 4 appears twice, once for each earlier duplicate)
SELECT u.id, u.email FROM users u
JOIN users v ON u.email = v.email AND u.id > v.id ORDER BY u.id;
-- 3 [email protected] | 4 [email protected] | 4 [email protected] | 6 [email protected]
-- 4) Delete the extras, keeping the lowest id per email
DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);
-- remaining: 1 [email protected] | 2 [email protected] | 5 [email protected]GROUP BY ... HAVING COUNT(*) > 1 answers "which values are duplicated", but it collapses the rows, so it cannot show you the ids to delete. The ROW_NUMBER() OVER (PARTITION BY key ORDER BY id) version keeps every row and labels the second and later copies with rn > 1, which is what you want for cleanup. The self join works but produces one row per duplicate pair, so three copies yield extra output rows. After cleaning, add a UNIQUE constraint or unique index so the database, not application code, prevents new duplicates. Note that in most engines a unique index treats NULLs as distinct, so rows with NULL in the key are not caught by it.
INNER JOIN keeps only matching pairs. LEFT JOIN keeps all left rows and fills right columns with NULL when nothing matches. Choose by asking whether an unmatched left row still belongs in the result.LEFT JOIN ... WHERE right.key IS NULL is the anti-join ("left rows with no match"). NOT EXISTS is the equivalent and NULL-safe.WHERE discards the NULL-padded rows and silently turns LEFT into INNER; put such filters in the ON clause when you want to keep unmatched left rows.|A| x |B|, and joining on a non-unique column fans out and inflates SUM/COUNT. Always write JOIN ... ON and check the row count.FULL OUTER JOIN exists in PostgreSQL and SQL Server (and SQLite 3.39+) but not MySQL/MariaDB; RIGHT JOIN is just a swapped LEFT JOIN.=: NULL = NULL is unknown, so those rows drop out of an inner join (null-safe operators such as IS NOT DISTINCT FROM or SQLite's IS exist).EXPLAIN. Index the foreign-key side of frequent joins.ON conditions are applied before this padding and WHERE conditions after it.WHERE (for anything not already applied), GROUP BY, HAVING, window functions, ORDER BY and LIMIT. The available algorithms and their names differ by engine (MySQL 8.0.18+ has hash join, older versions relied on nested loops).-- Run with: sqlite3 :memory: (FULL/RIGHT JOIN need SQLite 3.39+; verified on 3.51)
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1,'Asha'),(2,'Ben'),(3,'Chen');
INSERT INTO orders VALUES (10,1,50),(11,1,70),(12,2,20),(13,NULL,99);
-- 1) INNER vs LEFT: compare row counts
SELECT COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.id; -- 3
SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id; -- 4
-- 2) Who never ordered? (anti-join)
SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- Chen
-- 3) Break it: turn the LEFT JOIN into an INNER JOIN with a WHERE on the right side
SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 30;
-- Asha / Asha (Ben and Chen are gone). Now move the filter into ON and compare.
-- 4) Break it: forget the join condition
SELECT COUNT(*) FROM customers, orders;
-- 12 (3 x 4 cartesian product)
-- 5) The NOT IN trap (orders.customer_id contains a NULL)
SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- no rows
SELECT name FROM customers c WHERE NOT EXISTS
(SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Chen
-- 6) Reconcile both sides with FULL OUTER JOIN
SELECT c.name, o.id FROM customers c FULL OUTER JOIN orders o ON o.customer_id = c.id;
-- 5 rows: (NULL,13) (Asha,10) (Asha,11) (Ben,12) (Chen,NULL)
Q: INNER JOIN vs LEFT JOIN?
A: INNER JOIN returns only rows with a match on both sides, while LEFT JOIN returns every left row and fills right-side columns with NULL where there is no match. Use LEFT when the left row is meaningful on its own (a customer with zero orders), and INNER when only complete pairs matter. The trade-off is that outer joins constrain the optimizer's join order and the NULLs must be handled downstream, and a WHERE filter on the right table will silently turn a LEFT JOIN back into an inner join.
Q: What happens when you forget a JOIN condition?
A: You get a cartesian product: every row of one table paired with every row of the other, so the row count is the product of both sizes (3 x 4 = 12 in the example above, but 1,000 x 5,000 = 5 million in a real system). The result is wrong, the query can consume huge CPU, memory and network, and aggregates over it are nonsense. PostgreSQL and SQL Server reject an INNER JOIN with no ON, but MySQL and SQLite accept it and the old comma syntax accepts a missing WHERE everywhere, so always write JOIN ... ON and sanity-check row counts.
Q: Subquery vs JOIN?
A: A join combines columns from both tables and can multiply rows when the relationship is one-to-many; an IN/EXISTS subquery only tests existence and never duplicates outer rows. Optimizers commonly rewrite one into the other, so performance is generally similar and should be checked with EXPLAIN rather than assumed. Prefer EXISTS/NOT EXISTS for existence checks (and avoid NOT IN over a nullable column), and a join when you need the other table's data.
Q: How would you find duplicate records?
A: To find duplicated values, GROUP BY the columns that should be unique and keep groups with HAVING COUNT(*) > 1. To locate the exact extra rows for deletion, use ROW_NUMBER() OVER (PARTITION BY key ORDER BY id) and select rn > 1, or a self join on the key with a.id > b.id. Then prevent recurrence with a unique constraint or index, remembering that most engines allow multiple NULLs in a unique column, and delete in batches on big tables to avoid long locks.
Follow-up: A LEFT JOIN with a WHERE filter on the right table returns fewer rows than expected. Why?
A: Unmatched left rows carry NULLs for every right column, and WHERE right.col > 30 evaluates to unknown for them, so they are filtered out after the join. Move the condition into the ON clause if you want to keep unmatched left rows, or test right.col > 30 OR right.id IS NULL if that is genuinely the intent. The clue in an interview is "the LEFT JOIN behaves like an INNER JOIN".
Follow-up: My total revenue doubled after I joined in the payments table. What happened?
A: The join is one-to-many, so each order row is repeated once per payment row, and SUM(order.total) counts it several times (170 instead of 120 in the example above). Aggregate the child table first in a subquery or CTE (SUM(amount) GROUP BY order_id) and join that one-row-per-order result, or use EXISTS if you only need to check that a payment exists. Always ask which side is the "one" and which is the "many" before summing across a join.
Follow-up: A join between two large tables is slow. What do you check?
A: Run EXPLAIN (or EXPLAIN ANALYZE where available) and see the join algorithm and row estimates. Common causes are a missing index on the join column (forcing a large nested loop or hash of everything), stale statistics producing bad row estimates, a function on the join column that prevents index use, joining on mismatched types that force casts, and fan-out that multiplies rows. Fixes include indexing the foreign key, filtering earlier, selecting only needed columns, and updating statistics; the cost is extra write overhead and index storage.
Joins are the reason to normalise: data stored once and referenced by key means updates are cheap and consistent, and joins reassemble it on read. That works well on a single node with indexed foreign keys, but joins become the pressure point when you scale. Once you shard, a join across shards means scatter-gather across the network or application-side joining, which is why sharded systems tend to co-locate related rows (choose a shard key such as customer_id so a customer's orders live together) or denormalise. In an API layer, the classic bug is the N+1 pattern (one query for a list, then one query per row) which a single join or an IN batch removes; the opposite bug is a giant many-way join returning a wide, row-multiplied result for a paginated endpoint. For analytics and AI feature pipelines, joins between event and dimension tables benefit from pre-aggregating the many-side before joining, and for read-heavy paths a denormalised read model or materialised view can remove the join from the hot request altogether.
Using joins trades storage duplication for query-time work: a normalised schema stays consistent and compact but pays for joins on every read, whereas denormalising removes the join but introduces update anomalies and more data to keep in sync. Outer joins preserve information (customers with no orders) but restrict optimizer choices and force you to reason about NULLs, and a fan-out join is correct as SQL but wrong for aggregation unless you control cardinality. A join is the wrong tool when you only need an existence check (use EXISTS), when the two datasets live on different shards or services and the network hop will dominate (join in the application or precompute), or when a huge join would repeatedly be recomputed for a hot endpoint (cache, materialise, or denormalise). Choose the join type by the semantics you need, verify cardinality, index the join keys, and confirm the plan on the actual engine instead of assuming one form is faster.