SQL Joins and Set Operations Questions

Direct answer. Placing a filter on the right-hand table's column in WHERE, rather than in the join's ON clause, forces that column to be non-NULL to pass the filter, which throws away every left row that had no match (and therefore had NULL there), functionally converting your LEFT JOIN back into an INNER JOIN.

Structured elaboration. A LEFT JOIN's whole purpose is to preserve unmatched left rows by filling the right side with NULLs. WHERE runs AFTER the join has already produced its rows (including the NULL-padded ones), so a condition like WHERE o.status = 'SHIPPED' evaluates to unknown for any row where o.status is NULL, and WHERE discards anything that isn't TRUE. The fix is to move the condition into the ON clause instead, so it only constrains WHICH right-side rows are allowed to match in the first place, rather than filtering the joined result afterward; unmatched left rows still make it through with NULLs, exactly as a LEFT JOIN should behave.

Worked example. customers(1),(2),(3). orders(order_id=100, customer_id=1, status='SHIPPED'), (order_id=101, customer_id=2, status='PENDING'). Customer 3 has no orders at all.

-- buggy: WHERE demotes this to an INNER JOIN
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'SHIPPED';
-- returns only (1, 100). Customers 2 and 3 vanish, even though the LEFT JOIN
-- was written specifically to keep customers with no matching order.

-- fixed: move the condition into ON
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'SHIPPED';
-- returns (1, 100), (2, NULL), (3, NULL). Every customer still appears.

Trade-offs and pitfalls. A quick check for this class of bug: run the query with and without the suspect WHERE clause and compare row counts against COUNT(*) on the left table; if the "LEFT JOIN" version returns fewer rows than the left table has, something downstream demoted it. The same bug hides in more places than a literal comparison: o.status IS NOT NULL, o.amount > 0, even a seemingly harmless AND o.region = 'US' in WHERE all have the identical effect. As a review habit, any filter that mentions a LEFT-JOINed table's column should be treated as suspicious until you've confirmed it's meant to belong in ON rather than WHERE.

Direct answer. Self-join the referrals table to itself on the SWAPPED referrer/referred columns, so each row is checked against a matching row where the two IDs are reversed, then use an inequality (not just a distinctness check) on the pair to report each mutual pair exactly once.

Structured elaboration. This differs from a hierarchy self-join in what relationship it's testing: a hierarchy self-join matches a "child" row's manager_id to a "parent" row's own id, a strictly asymmetric, one-directional relationship. A mutual-pair self-join instead matches r1.referrer = r2.referred AND r1.referred = r2.referrer, a symmetric relationship where, if the pair matches at all, it matches in BOTH directions simultaneously, which is exactly why you need the extra r1.referrer_id < r1.referred_id (or similar) condition: without it, a genuine mutual pair (A referred B, B referred A) would show up TWICE, once from each row's perspective.

Worked example. referrals(referrer_id, referred_id, created_at): (1, 2, t1), (2, 1, t2), (1, 3, t3) (person 1 referred both 2 and 3; person 2 also referred person 1 back).

SELECT r1.referrer_id, r1.referred_id
FROM referrals r1
JOIN referrals r2
  ON r1.referrer_id = r2.referred_id AND r1.referred_id = r2.referrer_id
WHERE r1.referrer_id < r1.referred_id;

Result: (1, 2), a single row representing the mutual pair (1,2). The (1,3) referral is correctly excluded, since person 3 never referred person 1 back, so it has no matching reverse row at all.

Trade-offs and pitfalls. On a large referrals table, this self-join needs a good index on (referrer_id, referred_id) (and ideally the reverse composite too, or a single index usable in both directions) or it degenerates into a full scan-per-row comparison; the join condition here can't rely on a simple single-column equality the way a hierarchy self-join can, since it's matching a composite, swapped pair. Watch for the inequality direction specifically: using != instead of < would still exclude self-pairs but would report every genuine mutual pair TWICE (once as (1,2) and once as (2,1)), which is the same "report each pair exactly once" concern that comes up in any self-join over a symmetric relationship.

Direct answer. NULL never equals anything, including another NULL, so a join's equality condition (or a UNION/INTERSECT/EXCEPT row comparison) silently treats two NULL keys as non-matching, which can drop rows you'd expect to see or distort a downstream aggregate that assumed every row got included.

Structured elaboration. This is the same three-valued-logic fact showing up in two related places. In a join predicate, a.key = b.key evaluates to unknown (not true) whenever either side is NULL, so that pair is never considered a match, full stop, regardless of join type. In a set operation, UNION/INTERSECT/EXCEPT compare whole rows for equality, and most engines treat two NULLs in the same column position as "the same" for the PURPOSE of deduplication or set comparison (this is a special-cased exception to normal SQL NULL semantics, and it's the opposite behavior from a join predicate, which is exactly why it surprises people). Separately, once you're past the join, any NULL that a LEFT JOIN introduces into the right-hand columns will silently exclude those rows from a naive SUM or AVG (SUM ignores NULLs, which is usually fine, but AVG's denominator is the count of NON-NULL values, which can quietly change what "average" means).

Worked example. left_tbl(key, val): (1,'a'), (NULL,'b'). right_tbl(key, val): (1,'x'), (NULL,'y').

-- the NULL-keyed row on each side never matches, even though both keys "look the same"
SELECT l.val, r.val FROM left_tbl l
JOIN right_tbl r ON l.key = r.key;
-- returns only ('a','x'); the two NULL-keyed rows are silently excluded

-- COALESCE lets you decide explicitly whether NULL keys should match each other
SELECT l.val, r.val FROM left_tbl l
JOIN right_tbl r ON COALESCE(l.key, -1) = COALESCE(r.key, -1);
-- now returns ('a','x') AND ('b','y')

For the aggregate side: if orders(customer_id, amount) is LEFT JOINed from customers and a customer has no matching order, amount is NULL for that row. AVG(amount) divides by the count of non-NULL amounts, not the count of customers, so a naive AVG silently excludes the zero-order customers from the denominator rather than treating them as zero.

Trade-offs and pitfalls. COALESCE is a blunt instrument: picking a sentinel value like -1 to stand in for NULL is only safe if that sentinel can never be a legitimate key value in the data, otherwise you've just created a false match. For aggregates, the fix is usually to be explicit about what you're dividing by: COALESCE the joined column to 0 before summing if "no match" should count as zero, and compute your denominator from the LEFT side's row count directly rather than trusting COUNT/AVG's default NULL-skipping behavior to mean what you want.

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. INTERSECT returns only the rows that appear in BOTH result sets. EXCEPT (MINUS in Oracle) returns the rows that appear in the FIRST result set but not the second. Both compare whole rows, need the same number of columns with compatible types on each side, and both dedup their output.

Structured elaboration. Think of them as set arithmetic over two same-shaped result sets rather than as joins: there's no join key to specify, the entire row IS the comparison unit. That makes them a natural fit for comparing two snapshots or two populations directly, without writing an explicit join condition.

Worked example. customers_2023(customer_id): 1, 2, 3. customers_2024(customer_id): 2, 3, 4.

-- customers present in BOTH years (returning customers)
SELECT customer_id FROM customers_2023
INTERSECT
SELECT customer_id FROM customers_2024;
-- returns 2, 3

-- customers present in 2023 but not 2024 (lost customers)
SELECT customer_id FROM customers_2023
EXCEPT
SELECT customer_id FROM customers_2024;
-- returns 1

Trade-offs and pitfalls. Both operations dedup by default (like UNION, not UNION ALL), so if you need to know HOW MANY TIMES a row appeared rather than just whether it appeared, INTERSECT/EXCEPT are the wrong tool and you need an explicit join or GROUP BY instead. NULLs in the compared columns are handled specially: unlike an ordinary equality comparison, most engines treat two NULLs in the same position as equal for the purposes of this row comparison, which is the opposite of how NULL behaves in a join predicate, and is a common source of confusion when you've internalized "NULL never equals NULL" from working with joins.