SQL Joins and Set Operations Questions

Direct answer. A hint is the right call for a critical query where you've already ruled out the underlying cause (bad statistics, a missing index) and confirmed, with evidence, that the optimizer is genuinely and repeatably choosing worse than an alternative you can specify; the maintenance risk is that the hint freezes today's decision even as the data, statistics, or engine version change, so a plan that was correct when you added the hint can silently become wrong later with no automatic recovery.

Structured elaboration. An optimizer picks a plan based on its current statistics and cost model; a hint overrides that decision with a fixed choice you specify. That's valuable exactly when you have specific knowledge the optimizer's general-purpose cost model can't capture (a business-logic guarantee about data shape that statistics won't reflect, or a case where the cost model itself is known to misjudge a particular pattern), and risky exactly because the hint doesn't get reconsidered as circumstances change the way a fresh cost-based decision would.

Before reaching for a hint: confirm statistics are current (a hint is frequently a workaround for a statistics problem that would resolve the issue more durably on its own), confirm the alternative plan you want to force is genuinely and consistently better (not just better for the one data snapshot you tested against), and consider whether a smaller, more surgical intervention (an index change, a query rewrite) would fix the root cause instead of papering over it. When a hint is genuinely the right call, treat it the same way you'd treat any other piece of code that encodes an assumption about current data: document why it's there, and revisit it periodically or when the underlying data characteristics are known to have shifted meaningfully.

Worked example. A reporting query whose optimal plan depends on a business fact the optimizer has no way to know (say, "this filter will always match under 1% of rows because of an application-level invariant, even though the column's general statistics don't obviously suggest that") is a reasonable candidate for a hint; a query that's merely slow because statistics happen to be stale THIS week is not, since refreshing statistics is the more durable fix and the hint would just hide that underlying problem until it resurfaces in a different, more confusing way later.

Trade-offs and pitfalls. A codebase that reaches for hints as a first resort, rather than a documented last resort, tends to accumulate a growing set of frozen decisions nobody remembers the reasoning behind, several of which quietly stop being correct as the data evolves; a lightweight guardrail (a comment explaining the specific evidence that justified the hint, plus periodic review) is cheap insurance against that decay.

Direct answer. An equi-join matches rows using equality on the join columns (a.key = b.key); a non-equi join matches using any other comparison, inequality, range membership, or BETWEEN, most commonly to place a value into the bucket or tier it falls within.

Structured elaboration. The clearest real-world case is tiering: given a transaction amount and a table of tiers each defined by a min and max bound, you're not looking for an EXACT match on amount, you're looking for the tier whose range CONTAINS that amount, which is inherently a range (non-equi) condition rather than an equality.

Worked example. pricing_tiers(tier_name, min_amount, max_amount): ('bronze', 0, 100), ('silver', 100, 500), ('gold', 500, 999999999).

SELECT t.amount, tier.tier_name
FROM transactions t
JOIN pricing_tiers tier
  ON t.amount >= tier.min_amount AND t.amount < tier.max_amount;

A transaction of amount=250 matches the 'silver' row because 250 falls between 100 and 500; there's no single equality condition that could express this relationship.

Trade-offs and pitfalls. Equi-joins are what B-tree indexes are built for: an index on the join column supports fast equality lookups directly. A non-equi (range) join can't use a simple equality lookup the same way; the optimizer typically has to do more work, for example scanning or index-range-scanning the smaller side of the comparison per row of the larger side, which scales worse than an equi-join as both tables grow. For a small, mostly-static lookup table like pricing tiers this rarely matters in practice, but the same non-equi pattern applied to two large, fast-growing tables (matching every event to the marketing campaign whose date range it falls within, say) is exactly where you'd want to think carefully about indexing strategy for the range columns, which is its own deeper topic.

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 join adds columns by relating two tables through a key; a set operation stacks or compares whole result sets that already share the same shape, adding no new columns at all. Reaching for the wrong one produces either a result with the wrong number of rows (a join where you meant a set operation) or a result missing the relationship you needed (a set operation where you meant a join).

Structured elaboration. Two cases where a set operation is correct: combining monthly exports of the same report into one longer time series (UNION ALL: same columns, you just want more rows of the same shape), and comparing two snapshots of the same population to find what changed (EXCEPT/INTERSECT: again the same columns, but now you're asking a membership question rather than adding information). Two cases where a join is correct: enriching an orders table with the customer's name from a customers table (you need a NEW column, name, that orders doesn't have), and finding customers who have never placed an order (you're relating two DIFFERENT entities through a key, not stacking or comparing same-shaped rows).

Worked example. Using a join where a set operation was needed: if you wanted "combine this month's revenue rows with last month's revenue rows into one series" and instead wrote a JOIN on date between the two tables, you'd get one row per matching date pair with columns from BOTH months side by side, an entirely different (and probably useless) shape from the simple longer list you actually wanted; a UNION ALL is the one-line correct answer. Using a set operation where a join was needed: if you wanted "customers alongside their order count" and instead tried to UNION customers and orders together, the operation would fail outright (or produce nonsense) the moment the two tables don't have the identical column count and types, because UNION has no concept of "relate these through a key," only "stack these because they're the same shape."

Trade-offs and pitfalls. The duplicate and NULL handling differ sharply between the two: a join can silently multiply rows through an unexpected one-to-many relationship (fan-out), a failure mode that has nothing to do with NULLs at all, and a plain equality join predicate never matches two NULL keys to each other. A set operation instead dedups automatically (for UNION/INTERSECT/EXCEPT, though not UNION ALL) and, in most engines, treats two NULLs in the same column position as equal for that deduplication, the exact opposite NULL behavior from a join. Recognizing which shape a problem actually has, "I need to add information from elsewhere" versus "I need to stack or compare things that are already the same shape", is what should drive the choice, not which one happens to occur to you first.

Direct answer. Restructure once the query has enough distinct logical steps (aggregating one table, then joining that aggregate to another, then combining the results) that a reader can no longer hold the whole nested-subquery expression in their head at once; each CTE becomes one named, independently-checkable step.

Structured elaboration. The decision point isn't really about NUMBER of joins so much as number of distinct STAGES of computation. A query with several joins but one flat purpose (fetch these columns from these related tables) doesn't need restructuring. A query that computes one intermediate aggregate, joins it to a second intermediate aggregate, and then combines them is doing multiple logically separate things, and giving each its own CTE lets you name the stage, sanity-check its output in isolation (by running just that CTE as a standalone SELECT), and reason about correctness one piece at a time.

Worked example. sales(sale_id, customer_id, amount): (1,1,100), (2,1,50), (3,2,30). returns(return_id, sale_id, amount): (1, sale_id=2, amount=50) (sale 2 was returned).

WITH sale_totals AS (
  SELECT customer_id, SUM(amount) AS gross
  FROM sales GROUP BY customer_id
),
return_totals AS (
  SELECT s.customer_id, SUM(r.amount) AS returned
  FROM returns r JOIN sales s ON r.sale_id = s.sale_id
  GROUP BY s.customer_id
)
SELECT st.customer_id, st.gross, COALESCE(rt.returned,0) AS returned,
       st.gross - COALESCE(rt.returned,0) AS net
FROM sale_totals st
LEFT JOIN return_totals rt ON st.customer_id = rt.customer_id
ORDER BY st.customer_id;

Result: (1, 150, 50, 100), (2, 30, 0, 30). Customer 1's gross (150) minus their one return (50) nets to 100; customer 2 has no returns at all, so COALESCE keeps their net equal to their gross. Each CTE can be run standalone (SELECT * FROM sale_totals) to verify it in isolation before trusting the final combination.

Trade-offs and pitfalls. CTEs are a readability and maintainability win, not necessarily a performance one: some engines materialize a CTE's result once and reuse it, while others inline it back into the plan exactly as if it were a nested subquery, so restructuring for clarity doesn't automatically make the query faster (and, on engines that DO materialize eagerly, a CTE referenced only once can occasionally be slower than the equivalent inline subquery, because it forecloses certain plan choices). Check your engine's actual behavior (or just measure) before assuming a CTE rewrite is a performance improvement rather than purely an organizational one.