SQL Joins and Set Operations Questions

Direct answer. JOIN ... USING(column) requires the join column to have the identical name on both sides and produces a SINGLE, unqualified column of that name in the output, whereas JOIN ... ON a.column = b.column keeps both sides' columns separately in the result, each still needing its table qualifier (or an alias) to reference unambiguously afterward.

Structured elaboration. USING is really syntactic sugar for the common case where both tables happen to name their shared key identically; it saves you writing out the equality condition, and as a bonus it collapses the two now-redundant columns into one in the output, which you can then reference by its bare name in SELECT, WHERE, or ORDER BY without any table prefix at all. ON is more general (the columns being compared don't need matching names, or even a single column, since ON can express arbitrarily complex conditions) but leaves both sides' columns present and separately addressable in the result.

Worked example. orders(product_id, order_id): (100, 1). products(product_id, name): (100, 'Widget').

SELECT product_id, name
FROM orders JOIN products USING (product_id)
ORDER BY product_id;

Result: (100, 'Widget'). Note the SELECT and ORDER BY both reference product_id unqualified; with USING, there's exactly one product_id column in the result, not two, so no table-alias qualification is needed or even permitted for that specific column.

Trade-offs and pitfalls. USING only helps when the column names genuinely match, which won't be true if the two tables were designed independently (a foreign key column named differently from the primary key it references is common, and USING simply can't apply there; you're back to ON). Support and exact behavior for NATURAL JOIN (an even more implicit cousin of USING that joins on every identically-named column automatically) varies more across dialects, and most style guides discourage it for the same reason the comma-style join is discouraged: it's implicit about what it's doing, and a schema change (someone adds a new identically-named column to either table) can silently change which columns a NATURAL JOIN uses without anyone touching the query itself.

Direct answer. An ORDER BY and LIMIT inside one branch of a UNION only constrains and orders THAT branch's rows before they're combined; the standard doesn't guarantee that ordering survives into the final combined result, and a LIMIT applied to the whole UNION (rather than to each branch individually) limits the FINAL combined row count, not each branch's contribution to it.

Structured elaboration. Conceptually, a UNION (or UNION ALL) of several SELECTs, each with its own ORDER BY/LIMIT, computes each branch's ordered, limited subset first, and only then concatenates (and, for plain UNION, deduplicates) those already-materialized subsets, with NO overall ordering guarantee unless you add one more ORDER BY to the outermost query wrapping the whole UNION. So "top M rows per branch" is exactly what per-branch ORDER BY/LIMIT computes, correctly, but if you actually wanted "top M rows out of everything combined," you need the LIMIT on the OUTSIDE, applied once to the unioned result as a whole, not inside each branch.

Worked example. Getting the top 2 rows from EACH of two source tables (a genuine "top M per branch" need, e.g. featuring the two priciest items from each of two product categories in a merchandising feed):

(SELECT product_id, price FROM category_a ORDER BY price DESC LIMIT 2)
UNION ALL
(SELECT product_id, price FROM category_b ORDER BY price DESC LIMIT 2);

This correctly returns up to 4 rows: the top 2 from category_a and, separately, the top 2 from category_b. If you instead wanted the overall top 2 rows across BOTH categories combined, you'd need to wrap the union and apply the LIMIT once, outside:

SELECT product_id, price FROM (
  SELECT product_id, price FROM category_a
  UNION ALL
  SELECT product_id, price FROM category_b
) combined
ORDER BY price DESC
LIMIT 2;

Trade-offs and pitfalls. The parentheses around each per-branch SELECT above aren't just stylistic; without them, some engines will parse the ORDER BY/LIMIT as applying to the ENTIRE union rather than to just the branch immediately preceding it, which silently changes the query's meaning to something other than what was intended. Because of this ambiguity across dialects, it's worth explicitly testing which interpretation your specific engine applies (or simply always parenthesizing each per-branch SELECT) before relying on per-branch limiting in production.

Direct answer. A semi-join answers "does at least one matching row exist," returning each left-side row at most once. An anti-join answers the opposite: "does NO matching row exist." Neither one pulls any columns from the other side, which is what makes them cheaper and safer than a regular join for a pure existence check.

Structured elaboration. A regular INNER JOIN, by contrast, returns one row per MATCH, not per left-side row: if a customer has five qualifying transactions, an INNER JOIN returns that customer five times, and you'd need a DISTINCT (or a GROUP BY) afterward to get back to one row per customer. EXISTS expresses the semi-join directly: the database can stop scanning the right side the instant it finds one match, because it never needs to know how many matches there are or fetch any of their columns.

Worked example. Consider customers(customer_id) and transactions(customer_id, amount), and the question "which customers have at least one transaction over $500?"

-- semi-join via EXISTS: one row per qualifying customer, regardless of match count
SELECT c.customer_id
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM transactions t
  WHERE t.customer_id = c.customer_id AND t.amount > 500
);

-- the join-then-distinct alternative, doing more work for the same answer
SELECT DISTINCT c.customer_id
FROM customers c
JOIN transactions t ON t.customer_id = c.customer_id AND t.amount > 500;

Both return the same set of customer_ids. The EXISTS version never has to materialize or deduplicate the (potentially many) matching transaction rows; the JOIN + DISTINCT version does the work of producing every match and then throwing most of it away.

Trade-offs and pitfalls. The performance gap widens as the number of matches per left-row grows: a customer with 10,000 qualifying transactions costs the JOIN version 10,000 rows to produce and then collapse, versus one early-exit check for EXISTS. The same semi-join pattern generalizes to combined conditions across multiple tables, for example excluding a customer if they match EITHER of two separate exclusion tables, which is naturally expressed as two EXISTS (or NOT EXISTS) clauses ORed or ANDed together rather than as extra joins that would otherwise multiply rows across both tables at once.

Direct answer. Join on a non-equi condition that checks the fact's date against the dimension's valid_from and valid_to range, treating a NULL valid_to as "still current" (an open-ended upper bound), and add a tie-breaking rule for the rare case where two candidate ranges legitimately overlap.

Structured elaboration. The core condition is: fact.event_date >= dim.valid_from AND (fact.event_date < dim.valid_to OR dim.valid_to IS NULL). The OR-with-NULL clause is what correctly treats an open-ended "still current" record as covering everything from its valid_from onward, with no upper limit. If overlapping ranges are possible in the data (a data-entry error, or a deliberate priority scheme like promotions that can legitimately stack), you additionally need a deterministic way to pick ONE winner: order candidates by valid_from descending (most recent wins) or by an explicit priority column, and take the top one, typically via a window function or a LIMIT 1 correlated subquery.

Worked example. orders(order_id, product_id, order_date): (1, 100, '2025-02-15'), (2, 100, '2025-04-01'). price_history(product_id, price, valid_from, valid_to): (100, 10.00, '2025-01-01', '2025-03-01'), (100, 12.00, '2025-03-01', NULL) (the second row is still current, no end date).

SELECT o.order_id, o.order_date, p.price
FROM orders o
JOIN price_history p
  ON o.product_id = p.product_id
 AND o.order_date >= p.valid_from
 AND (o.order_date < p.valid_to OR p.valid_to IS NULL)
ORDER BY o.order_id;

Result: (1, '2025-02-15', 10.00), (2, '2025-04-01', 12.00). Order 1 correctly picks up the OLDER price (its date falls before the 2025-03-01 boundary), and order 2 correctly falls into the still-current, open-ended price row via the OR p.valid_to IS NULL clause.

Trade-offs and pitfalls. The most common bug in this pattern is an off-by-one at the boundary: deciding whether valid_to is inclusive or exclusive (here, exclusive: valid_to is the moment the NEXT record takes over) needs to be consistent with how the valid_from of the next row is set, or you'll either double-count or leave a gap at the exact boundary date. If overlapping ranges are possible and you skip the tie-breaking step, the join can silently return MULTIPLE price rows for a single order, quietly fanning out your result set exactly like a one-to-many join would.

Direct answer. EXCEPT (comparing the new snapshot against the old one) directly returns the rows that are new or changed, which is exactly the set you need to process for an incremental load, without touching or reprocessing anything that stayed the same.

Structured elaboration. EXCEPT compares whole rows, so a row that changed in ANY column between the two snapshots will show up as "different" and get selected, since its full row no longer matches anything in the old snapshot. That's precisely what you want for change detection: you don't need to know WHICH column changed to know THAT the row changed. For null-safety, if any of the compared columns can be NULL, you don't need special handling for EXCEPT itself (most engines already treat two NULLs in the same column position as equal for this row-comparison purpose, unlike an ordinary equality condition), but it's worth explicitly verifying that behavior for your specific engine before relying on it.

Worked example. old_snapshot(id, col1, col2): (1,'a',10), (2,'b',20). new_snapshot(id, col1, col2): (1,'a',10) (unchanged), (2,'b',99) (col2 changed), (3,'c',30) (a brand new row).

SELECT * FROM new_snapshot
EXCEPT
SELECT * FROM old_snapshot
ORDER BY id;

Result: (2, 'b', 99), (3, 'c', 30). Row 1 is correctly excluded (it's identical in both snapshots), while both the genuinely-changed row (2) and the genuinely-new row (3) are returned as needing to be loaded.

Trade-offs and pitfalls. This approach gives you "new or changed" as one undifferentiated set; if your load logic needs to treat inserts and updates differently (an upsert that behaves differently depending on which one it is), you'd typically follow this EXCEPT with a check against the OLD snapshot's key alone to classify each returned row as new (key absent from old_snapshot) versus changed (key present, but the row differs). It's also worth noting EXCEPT alone doesn't tell you about DELETIONS (rows present in old_snapshot but entirely gone from new_snapshot); that's the mirror query, old_snapshot EXCEPT new_snapshot, run separately. Idempotency comes from the fact that re-running this same comparison against the same two snapshots always produces the identical result set, so a retried or repeated load doesn't produce duplicate or inconsistent processing.