SQL Joins and Set Operations Questions

Direct answer. INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table and pads unmatched right-side columns with NULL. RIGHT JOIN is the mirror image. FULL OUTER JOIN keeps unmatched rows from both sides. CROSS JOIN pairs every row on one side with every row on the other, with no matching condition at all.

Structured elaboration. For customers and orders joined on customer_id:

• INNER JOIN: one row per matching (customer, order) pair. A customer with zero orders disappears entirely; a customer with three orders appears three times.
• LEFT JOIN: every customer appears at least once. A customer with zero orders appears once with all order columns NULL. A customer with three orders still appears three times (once per order), it just never drops the zero-order customers.
• RIGHT JOIN: same idea, but it guarantees every order appears, padding missing customer columns instead.
• FULL OUTER JOIN: guarantees every customer AND every order appears, even orders whose customer_id doesn't exist in customers (an orphan row you'd otherwise never see).
• CROSS JOIN: with no condition at all, row count is simply (rows in customers) times (rows in orders). It's rarely what you want between two unrelated fact-sized tables.

Worked example. customers(1, 'Alice'), (2, 'Bob'). orders(101, customer_id=1), (102, customer_id=1), (103, customer_id=3) (note: customer_id 3 doesn't exist in customers).

• INNER JOIN on customer_id: 2 rows (both Alice's orders). Bob and the orphan order 103 vanish.
• LEFT JOIN (customers LEFT JOIN orders): 3 rows. Alice appears twice (101, 102), Bob appears once with order columns NULL. Order 103 never appears because it isn't reachable from the left side.
• FULL OUTER JOIN: 4 rows. Adds a row for order 103 with customer columns NULL, surfacing the orphan.

Trade-offs and pitfalls. The most common mistake is treating LEFT JOIN as "the safe default" without checking multiplicity: if the right side isn't unique per left key, a LEFT JOIN still duplicates the left row once per match, which silently inflates any SUM or COUNT computed downstream. RIGHT JOIN is functionally redundant (swap the table order and it's a LEFT JOIN), so most style guides ban it for readability rather than correctness. CROSS JOIN is legitimate when you deliberately want every combination (generating a date-times-category grid), but an accidental one from a missing join predicate is one of the most common production SQL bugs.

Direct answer. Compare the query's actual row count to what the join's cardinality SHOULD produce (a rough estimate from each table's row count and the expected match ratio), then rebuild the query one join at a time, checking the row count after each addition, to find the exact join where it blows up.

Structured elaboration. A genuine data issue (an unexpectedly large but legitimate result) and an accidental Cartesian product look similar from a distance (the query returns way more rows than expected) but are structurally different: a Cartesian product's row count is the PRODUCT of the tables involved, which is usually an unmistakably huge and suspicious number once you compute it, whereas a real data issue tends to produce a large but still roughly additive or proportional count. Progressive construction (start from one table, add each join and re-check COUNT(*) after every addition) isolates exactly which join introduced the multiplication, because the jump will be dramatic and specific to one join, not gradual across several.

Worked example. users(1),(2). orders(order_id=10, user_id=1), (order_id=11, user_id=2). Someone intended to also filter to products relevant to each order but forgot the join predicate:

-- buggy: products isn't joined on anything, so it's an implicit Cartesian product
SELECT u.user_id, o.order_id, p.product_id
FROM users u, orders o, products p
WHERE u.user_id = o.user_id;
-- with 2 users, 2 orders, and 2 products, this returns 4 rows, not 2:
-- (1,10,100), (1,10,200), (2,11,100), (2,11,200)
-- every (user, order) pair got paired with EVERY product, because nothing
-- constrains which product goes with which order

Checking SELECT COUNT(*) FROM users u JOIN orders o ON u.user_id = o.user_id alone (without products) confirms the expected 2 rows, isolating the products table as the source of the multiplication the moment it's added back in.

Trade-offs and pitfalls. This bug is especially dangerous because it doesn't error, it just silently returns more rows than intended, often still looking plausible if the actual multiplier happens to be small. As a CI/code-review guardrail: add a data test that asserts the row count of a join-heavy query stays within some multiple of its primary table's row count (or an EXPECT-based test with a small fixture and a known expected row count), and treat any comma-separated FROM list or any JOIN whose ON clause got dropped during a refactor as an automatic review flag.

Direct answer. Move the per-row scalar computation out of the SELECT list and into a JOIN with a GROUP BY, so the database computes the aggregate once per outer-table row via a single pass, instead of re-scanning the inner table separately for every outer row.

Structured elaboration. A correlated scalar subquery in the SELECT list runs once PER ROW of the outer query, each time re-scanning (or re-probing an index on) the inner table for that one row's value. A LEFT JOIN followed by GROUP BY instead lets the engine compute the whole join and its aggregation together, typically as a single hash or merge pass over both tables, which scales much better as the outer table grows, because the cost stops being "outer rows times inner-table lookup cost" and becomes closer to "size of both tables, scanned once each."

Worked example. customers(1),(2),(3). orders(order_id=100, customer_id=1), (101, customer_id=1), (102, customer_id=2).

-- correlated subquery: one subquery execution per customer row
SELECT c.customer_id,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders_count
FROM customers c;

-- equivalent join + aggregation: one pass over both tables
SELECT c.customer_id, COUNT(o.order_id) AS orders_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

Both return the identical result: (1, 2), (2, 1), (3, 0). Note the LEFT JOIN (not INNER) and COUNT(o.order_id) (not COUNT()) are both required to correctly get 0 for customer 3, who has no orders at all; getting either one wrong (an INNER JOIN, or COUNT() instead of COUNT(order_id)) would silently drop customer 3 or overcount.

Trade-offs and pitfalls. The rewrite is only correct if you're careful about two things the original subquery handled implicitly: use LEFT JOIN (not INNER) if rows with zero matches should still appear, and use COUNT(specific_column) rather than COUNT(*) so that a customer with zero orders (whose joined columns are all NULL) counts as 0 rather than 1. The correlated-subquery form remains readable and perfectly fine for a handful of rows or a one-off analysis; the join rewrite earns its complexity specifically at scale, where the per-row subquery cost compounds.

Direct answer. Use LEFT JOINs (not INNER) across the whole chain, and count DISTINCT C rows per A, so an A row with no B rows at all (a genuinely missing intermediate) still appears with a count of zero rather than disappearing from the result entirely.

Structured elaboration. A naive INNER JOIN across A to B to C requires a match at EVERY step to produce any row at all, so an A row whose B side is completely empty vanishes silently, along with any legitimate business meaning "zero C's" was supposed to carry. Chaining LEFT JOINs instead lets each step independently produce NULLs rather than dropping the row, and COUNT(DISTINCT c.id) then correctly counts zero for a row where the LEFT JOIN chain bottomed out in NULLs at any point, without needing special-case logic.

Worked example. a_tbl(id): 1, 2. b_tbl(id, a_id): (10, a_id=1), (11, a_id=1) (A=1 has two B rows). A=2 has no B rows at all. c_tbl(id, b_id): (100, b_id=10), (101, b_id=10) (B=10 has two C rows; B=11 has none).

SELECT a.id, COUNT(DISTINCT c.id) AS c_count
FROM a_tbl a
LEFT JOIN b_tbl b ON a.id = b.a_id
LEFT JOIN c_tbl c ON b.id = c.b_id
GROUP BY a.id
ORDER BY a.id;

Result: (1, 2), (2, 0). A=1 correctly reports 2 distinct C rows (through B=10; B=11 contributed nothing but didn't break the count), and A=2 correctly reports 0 rather than disappearing, even though it has no B row at all to even start the chain.

Trade-offs and pitfalls. COUNT(DISTINCT c.id) is essential here, not just COUNT(c.id): because A=1 fans out through TWO B rows, and one of those B rows (11) contributes a NULL c.id from the LEFT JOIN (since B=11 has no matching C rows), a naive COUNT(c.id) would still correctly skip the NULL, but if the chain instead produced genuine duplicate C ids through multiple paths (a more tangled many-to-many shape), only DISTINCT protects you from over-counting. It's worth explicitly testing the "missing intermediate" case (an A row with literally zero B rows) as its own scenario, since it's exactly the kind of edge case that a query written and tested only against "happy path" data with a B row for every A silently fails to handle.

Direct answer. With a chain of LEFT JOINs, a filter placed on the LAST table's column (even one that looks harmless) can retroactively demote an EARLIER join's outer-join semantics if that filter ends up in the WHERE clause rather than the corresponding ON clause, because WHERE evaluates after the whole chain has already produced its NULL-padded rows.

Structured elaboration. The subtlety with three or more chained outer joins is that "reordering" isn't really about which JOIN keyword comes first in the text, it's about where a downstream filter lives relative to the chain. A LEFT JOIN B LEFT JOIN C is evaluated left to right: first A LEFT JOIN B produces a NULL-padded intermediate result, then THAT result is LEFT JOINed to C. If you then filter on a column from C in the WHERE clause, any row where B (and therefore C) had no match gets excluded, even though the FIRST join (A to B) was written specifically to preserve A's unmatched rows. The fix is the same principle as the two-table case: put the filter in C's ON clause, not in WHERE, so it only constrains which C rows are eligible to match, without discarding upstream rows that legitimately have no C match at all.

Worked example. a_tbl(id): 1, 2. b_tbl(id, a_id): (10, a_id=1). c_tbl(id, b_id, flag): (100, b_id=10, flag='X'). Row a_id=2 has no matching b row at all.

-- buggy: filtering c.flag in WHERE demotes the WHOLE chain back toward an inner join
SELECT a.id, b.id AS b_id, c.id AS c_id
FROM a_tbl a
LEFT JOIN b_tbl b ON a.id = b.a_id
LEFT JOIN c_tbl c ON b.id = c.b_id
WHERE c.flag = 'X';
-- returns only (1, 10, 100). Row a.id=2 vanishes entirely, even though the
-- LEFT JOIN to b_tbl was written specifically to keep A-rows with no B match.

-- fixed: move the flag condition into c's own ON clause
SELECT a.id, b.id AS b_id, c.id AS c_id
FROM a_tbl a
LEFT JOIN b_tbl b ON a.id = b.a_id
LEFT JOIN c_tbl c ON b.id = c.b_id AND c.flag = 'X'
ORDER BY a.id;
-- returns (1, 10, 100) AND (2, NULL, NULL). Row a.id=2 is preserved, as intended.

Trade-offs and pitfalls. The practical fix for readability, once a chain gets to three or more joins, is often to restructure the later stages as a derived table or CTE that pre-filters C on its own terms BEFORE joining it to the chain, rather than relying on everyone downstream to remember which conditions belong in which ON clause. That also makes the intended semantics explicit at a glance, instead of implicit in clause placement that's easy to get subtly wrong during a later edit.