SQL Joins and Set Operations Questions

Direct answer. Pre-aggregate the "many" side down to one row per key BEFORE joining it to the fact table, or deduplicate it down to one canonical row per key using an explicit tie-breaking rule; either one fixes the duplication at its source, whereas slapping DISTINCT on the final result only hides symptoms and can silently drop legitimately different rows.

Structured elaboration. The two real fixes and the one fake fix:

• Pre-aggregate before joining: if you need SUM(orders.total) per customer and orders is being joined through a one-to-many table like order_items, compute the per-order (or per-customer) aggregate in a subquery or CTE first, and join the already-aggregated result. The join can no longer multiply anything because the thing you're joining is already at the right grain.
• Deduplicate the many side with a canonical-row rule: when what you actually want is ONE representative row per key (the latest address, the primary email), pick a specific tie-breaking rule (most recent by timestamp, a boolean is_primary flag, or ROW_NUMBER() partitioned by the key) and join against only that one row per key.
• DISTINCT on the final result (the band-aid): this looks like it fixes the symptom because the visible duplicate ROWS disappear, but it operates on the wrong thing. If two DIFFERENT orders happen to produce an identical row on the columns you selected, DISTINCT silently merges them too, and you've now lost real data rather than fixed a join. It also does nothing to fix an aggregate computed from the pre-DISTINCT, already-inflated rows.

Worked example. customers(1,'Alice'). orders(100, customer_id=1, total=50), (101, customer_id=1, total=30). order_items has two rows for order 100 and one for order 101 (three item rows total).

-- naive (inflated): joining orders to order_items before aggregating double-counts order 100
SELECT c.customer_id, SUM(o.total) AS inflated_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id;
-- returns (1, 130.00): order 100's 50 got summed twice (once per item)

-- fixed: aggregate orders on their own; never join order_items for this metric at all
SELECT c.customer_id, SUM(o.total) AS correct_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;
-- returns (1, 80.00), the correct sum of order totals (50 + 30)

The fix here is really "don't join the many-side table at all if this metric doesn't need it," which is the pre-aggregation principle taken to its logical conclusion: join only what the specific metric actually requires.

Trade-offs and pitfalls. Pre-aggregation is the more scalable fix (the join now operates on a table that's already the right size) but requires you to correctly identify every place a metric is computed and make sure the aggregation grain matches; get the grain wrong and you've traded a visible bug for a subtle one. The canonical-row approach needs an explicit, deterministic tie-breaking rule, an ambiguous one (pick "any" row when several are equally recent) produces answers that vary between runs. Treat any DISTINCT you find sitting after a multi-table join as a signal to go find out WHY duplicates existed in the first place, not as the fix itself.

Direct answer. A RIGHT JOIN keeps every row from the right-hand table and NULL-pads the left side where there's no match, the exact mirror of a LEFT JOIN; swapping which table is written first and changing RIGHT JOIN to LEFT JOIN produces an identical result, which is why many teams simply avoid RIGHT JOIN and standardize on LEFT JOIN for everything.

Structured elaboration. Because RIGHT JOIN and LEFT JOIN are true mirrors of each other, any RIGHT JOIN can be rewritten by physically swapping the two table references and changing the keyword, with no change in meaning at all. Teams that ban RIGHT JOIN do it for pure readability and consistency: a codebase where every join is either INNER or LEFT is easier to scan (a reader always knows "the important, must-appear table comes first"), and RIGHT JOIN's rarity means readers spend an extra beat parsing it every time it shows up, even though it's perfectly valid SQL.

Worked example. employees(employee_id, name, dept_id): (1, 'Alice', 10). departments(dept_id, dept_name): (10, 'Eng'), (20, 'Sales').

-- RIGHT JOIN: keep every department, even ones with no employees
SELECT e.name, d.dept_name
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_id;
-- returns (Alice, Eng), (NULL, Sales)

-- identical result via LEFT JOIN, with the tables swapped
SELECT e.name, d.dept_name
FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id
ORDER BY d.dept_id;
-- returns (Alice, Eng), (NULL, Sales)

Both queries return the identical two rows: Sales has no employees, so it appears with a NULL name either way.

Trade-offs and pitfalls. There's no correctness or performance difference between the two forms, so this is purely a house-style convention, not a technical constraint; some optimizers may even normalize a RIGHT JOIN into the equivalent LEFT JOIN internally before planning it. The main practical risk with RIGHT JOIN isn't the join itself, it's that a query mixing LEFT and RIGHT JOINs across a longer chain becomes genuinely hard for a reader to trace, since they can no longer assume "the must-preserve table is always written first"; standardizing on LEFT JOIN everywhere removes that ambiguity by convention rather than by any property of the SQL itself.

Direct answer. Start by comparing actual counts against expected ones (row count of the join versus row count of the left table, and distinct-count of the join key versus its expected cardinality) to confirm inflation is really happening, then isolate WHICH join is responsible by re-running the query with joins removed one at a time, and finally check whether the culprit is the join TYPE (a genuine one-to-many relationship you didn't account for) or the join KEY itself (joining on the wrong column, or two columns at different grain).

Structured elaboration. A practical investigation sequence:

1. Confirm the LEFT table's own row count with a plain SELECT COUNT(*) FROM left_table, and compare it to the row count after each join is added, one at a time. The join where the count first jumps beyond the left table's count is your culprit.
2. For that join's key, check whether it's actually unique on the "one" side you assumed it was: SELECT key, COUNT(*) FROM right_table GROUP BY key HAVING COUNT(*) > 1. Any key with a count greater than 1 is duplicating every left row that matches it.
3. If the key looks unique but you're still seeing duplication, check whether you joined on the WRONG column, one that happens to have many-to-many overlap for reasons unrelated to your intended relationship, for example joining on shipping_country when you meant customer_id.
4. If step 2 or 3 confirms a genuine one-to-many or many-to-many relationship, decide whether to pre-aggregate the "many" side before joining, or whether DISTINCT/COUNT(DISTINCT ...) at the end is the right fix (it usually isn't, see the remediation trade-offs below).
5. For an ongoing pipeline rather than a one-off investigation, two habits catch this class of bug earlier: a checksum or hash-aggregation comparison (hash or sum a stable business key across the pre-join and post-join result sets and confirm they agree) run automatically after any join-heavy transformation, and, on the query itself, a match-cardinality column computed with a window function (labeling each left row as having had zero, one, or multiple right-side matches) so a reviewer can see the shape of the join directly in the output rather than only in an aggregate count.

Worked example. customers(1). orders(100, customer_id=1, total=50), (101, customer_id=1, total=30). order_items(item_id=1, order_id=100), (item_id=2, order_id=100), (item_id=3, order_id=101) (order 100 has two line items).

-- naive: joins orders to order_items, which duplicates each order once per item
SELECT c.customer_id, SUM(o.total) AS inflated_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id;
-- returns (1, 130.00): order 100's total (50) got counted TWICE because it has two line items

Running SELECT order_id, COUNT(*) FROM order_items GROUP BY order_id HAVING COUNT(*) > 1 immediately surfaces order 100 as a duplicating key, confirming the order_items join is the culprit and that orders-to-order_items is genuinely one-to-many.

Trade-offs and pitfalls. It's tempting to reach for SELECT DISTINCT or COUNT(DISTINCT column) the moment you see inflated numbers, and that can mask the real cause rather than fix it: DISTINCT on the WRONG grain can silently drop legitimately different rows (two real orders that happen to look identical on the columns you selected) while still leaving duplication elsewhere in the result untouched. The systematic count-comparison approach above tells you definitively which join and which key are responsible before you touch the query, rather than guessing at a fix and hoping the numbers happen to come out right.

Direct answer. EXCEPT and INTERSECT compare rows as whole tuples and, unlike an ordinary equality comparison, most engines treat two NULLs in the same column position as equal for this specific purpose, which is the opposite of how NULL behaves in a join predicate, and is exactly what surprises people who've internalized "NULL never equals NULL" from working with joins.

Structured elaboration. This is a genuinely engine-and-context-specific behavior worth verifying rather than assuming: the SQL standard's row-comparison semantics for set operations special-case NULL to be "the same as itself" for the purposes of determining whether two rows are duplicates or whether one row exists in another set, a deliberate exception to ordinary three-valued NULL logic. Dialect naming and availability also differ: EXCEPT is the standard and Postgres/SQL Server keyword, Oracle uses MINUS for the identical operation, and older or more limited engines may lack one or both operations entirely.

Worked example. set_a(id, val): (1, 'x'), (2, NULL). set_b(id, val): (1, 'x'), (2, NULL).

SELECT * FROM set_a
EXCEPT
SELECT * FROM set_b;

A newcomer reasoning from ordinary NULL semantics might expect row (2, NULL) to still appear in the result, on the theory that "NULL never equals NULL, so set_a's NULL row can't be proven identical to set_b's NULL row." In practice, this returns ZERO rows: both engines treat the (2, NULL) rows as matching duplicates for EXCEPT's purposes, so it's correctly excluded as "already present in set_b," even though the individual column values would never satisfy a plain = comparison.

Trade-offs and pitfalls. If you specifically need to EXCLUDE NULL-containing rows from this kind of comparison (treating "unknown" as never confirmable as a duplicate, which is arguably the more standards-purist interpretation of NULL's meaning), you have to filter them out explicitly with a WHERE clause before the EXCEPT/INTERSECT runs, since the set operation itself won't do that filtering for you. Given how easy it is to get this backwards from intuition, it's worth writing a tiny throwaway test against your actual engine (exactly like the example above) before relying on either behavior in a data-quality or reconciliation query where getting NULL handling wrong could hide a real discrepancy.

Direct answer. All three give the same logical answer for "rows in the first set that have no counterpart in the second," but they differ in NULL-safety and how explicitly they handle duplicate rows: NOT EXISTS is the safest default, the LEFT JOIN anti-join is usually just as correct and often the most readable, and EXCEPT is the most concise but silently dedups its output, which is a behavior you need to want.

Structured elaboration.

• LEFT JOIN anti-join: LEFT JOIN ... WHERE right.key IS NULL is NULL-safe with respect to the join KEY itself (a NULL key on the right side simply never matches, which is what you want), and it reads clearly to anyone familiar with joins.
• NOT EXISTS: correlated, checks row-by-row whether a match exists, and is immune by construction to the NOT IN + NULL trap because it never compares against a literal list.
• EXCEPT: compares whole rows between two result sets and automatically deduplicates its output; if the left set has the same row twice, EXCEPT returns it at most once, which the other two approaches do NOT do (they'd preserve however many times it appeared on the left).

Worked example. q1(customer_id): 1, 2, 3 (this month's active customers). q2(customer_id): 2, 3 (customers who also had activity last month). The question: which customers are new to q1 this month, i.e., in q1 but not q2?

-- (a) LEFT JOIN anti-join
SELECT q1.customer_id FROM q1
LEFT JOIN q2 ON q1.customer_id = q2.customer_id
WHERE q2.customer_id IS NULL;
-- returns 1

-- (b) NOT EXISTS
SELECT customer_id FROM q1
WHERE NOT EXISTS (SELECT 1 FROM q2 WHERE q2.customer_id = q1.customer_id);
-- returns 1

-- (c) EXCEPT
SELECT customer_id FROM q1
EXCEPT
SELECT customer_id FROM q2;
-- returns 1

All three agree here. The divergence shows up once q1 has a duplicate: if q1 also contained a second row for customer_id = 1, (a) and (b) would both return TWO rows for customer 1, while (c) would still return exactly one, because EXCEPT dedups.

Trade-offs and pitfalls. Reach for NOT EXISTS as the safe general default, especially when you're not certain the tables are duplicate-free. Reach for EXCEPT when you specifically want a deduplicated result and the two sides are naturally same-shaped snapshots (comparing two full exports of the same entity). Avoid NOT IN against a subquery entirely unless you can guarantee the subquery's column is never NULL. Performance-wise, all three typically compile to similar plans on well-indexed columns, so correctness and readability, not raw speed, should usually decide between them.