SQL Joins and Set Operations Questions

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. 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 self-join treats one table as if it were two: you join employees to itself, once playing the role of the employee and once playing the role of that employee's manager, matching manager_id on one side to employee_id on the other.

Structured elaboration. A LEFT JOIN (not INNER) is essential here, because the top of the org has manager_id = NULL, and an INNER JOIN would silently drop the top-level employee (or anyone else with no manager) since NULL matches nothing. The pattern generalizes: give the table two different aliases in the FROM clause, and join those aliases to each other on whatever relationship the rows encode (here, "reports to").

Worked example. employees(1,'Alice',manager_id=NULL), (2,'Bob',manager_id=1), (3,'Cara',manager_id=1), (4,'Dee',manager_id=2).

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Result: (Alice, NULL), (Bob, Alice), (Cara, Alice), (Dee, Bob).

Extending one level up (manager's manager) is just a second self-join on the same table with a third alias:

SELECT e.name AS employee_name, m.name AS manager_name, gm.name AS manager_of_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
LEFT JOIN employees gm ON m.manager_id = gm.employee_id
ORDER BY e.employee_id;

Result: (Alice, NULL, NULL), (Bob, Alice, NULL), (Cara, Alice, NULL), (Dee, Bob, Alice). Dee's manager is Bob, and Bob's manager is Alice, so Dee's manager-of-manager column correctly resolves to Alice.

Trade-offs and pitfalls. Chaining self-joins works cleanly for a fixed, small number of levels (one or two), because each level is just one more LEFT JOIN. It stops scaling the moment the number of levels is unknown or the hierarchy can be arbitrarily deep, at which point you need a recursive CTE instead, which walks the hierarchy level by level until it runs out of rows rather than hard-coding a fixed number of self-joins. The self-join approach is also easy to get wrong on the join direction: joining e.manager_id = m.employee_id gets you the employee's manager; joining the other way around gets you the manager's direct reports, which is a different (equally valid) question.

Direct answer. Join the raw table to a subquery that computes, per key, the maximum timestamp, matching back on BOTH the key and that maximum timestamp, so only the single latest row per key survives the join.

Structured elaboration. The join-based approach computes the "what's the latest timestamp per key" question as its own aggregate subquery first (a GROUP BY key with MAX(timestamp)), then joins the raw table back to that result on both the key AND the timestamp matching the computed maximum, which naturally keeps only the winning row(s) per key. This produces the identical logical answer to a ROW_NUMBER() OVER (PARTITION BY key ORDER BY timestamp DESC) approach filtered to rn = 1, just expressed through aggregation and a join instead of a window function.

Worked example. raw_customers(customer_id, email, created_at): (1, 'a@x.com', t1), (2, 'a@x.com', t2 later), (3, 'b@x.com', t3).

-- join-based: aggregate the max timestamp per key, then join back to it
SELECT rc.customer_id, rc.email, rc.created_at
FROM raw_customers rc
JOIN (
  SELECT email, MAX(created_at) AS max_created
  FROM raw_customers
  GROUP BY email
) latest ON rc.email = latest.email AND rc.created_at = latest.max_created;

-- ROW_NUMBER() alternative, for comparison
SELECT customer_id, email, created_at FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
  FROM raw_customers
) t WHERE rn = 1;

Both return the identical result: (2, 'a@x.com', t2), (3, 'b@x.com', t3), correctly keeping the later of the two rows sharing 'a@x.com'.

Trade-offs and pitfalls. The join-based approach has one correctness wrinkle the window-function version doesn't: if two rows for the SAME key happen to share the exact same maximum timestamp (a genuine tie), the join-based version returns BOTH of them, since both match the aggregate's max_created equally, whereas ROW_NUMBER() with a stable ORDER BY tie-break returns exactly one, deterministically. If ties are possible in your data, you'd need to add an explicit secondary tie-breaker (an id column) to the join's matching condition to get single-row-per-key behavior back. Performance-wise, the two approaches often compile to comparably efficient plans on a well-indexed key/timestamp pair, so the choice mostly comes down to whether your engine and team are more comfortable with the aggregate-and-join idiom or the window-function idiom, and whether tie-handling needs to be airtight.

Direct answer. Write the join condition so a NULL on the left side's key part is treated as an automatic match against ANY value on the right (l.key2 = r.key2 OR l.key2 IS NULL), and keep that wildcard logic in the join predicate itself rather than trying to fake it by coalescing NULL to a literal placeholder string, which risks colliding with a genuinely real value.

Structured elaboration. The two halves of this question are really two sides of the same judgment call about what NULL means in a composite key. Sometimes NULL genuinely means "matches anything" (a wildcard, a business rule saying "this row applies regardless of the second key part"), and an explicit OR-with-IS-NULL clause in the join condition expresses that intent directly. Other times NULL means "unknown, do not guess," and coalescing it to a literal sentinel like the string 'NULL' to force a match is dangerous specifically because that sentinel could ALSO be a legitimate value already present in the data (a SKU literally named "NULL" as a placeholder product code, say), silently merging two things that were never meant to be the same.

Worked example. left_table(id, key1, key2, value): (1, 'US', NULL, 'v1') (a wildcard row for all of key2 under 'US'), (2, 'US', 'east', 'v2'). right_table(key1, key2, price): ('US', 'east', 100), ('US', 'west', 120).

SELECT l.id, r.key2, r.price
FROM left_table l
JOIN right_table r
  ON l.key1 = r.key1
 AND (l.key2 = r.key2 OR l.key2 IS NULL)
ORDER BY l.id, r.key2;

Result: (1, 'east', 100), (1, 'west', 120), (2, 'east', 100). Row 1 (the wildcard, key2=NULL) correctly matches BOTH right-side rows since its key2 is NULL, while row 2 (key2='east') matches only the one exact right-side row it specifies.

Trade-offs and pitfalls. The danger case for the counterpoint: imagine a real SKU is legitimately coded as the literal string 'NULL' by an upstream system's bug or convention. If you'd instead written COALESCE(l.sku, 'NULL') = COALESCE(r.sku, 'NULL') to make actual NULLs match each other, that real SKU would now be silently treated as identical to every genuinely-missing SKU, merging unrelated rows. The wildcard pattern above avoids this entirely because it never manufactures a fake value to compare against; it just tests IS NULL directly. Before implementing either pattern, confirm with whoever owns the business rule which meaning NULL is actually supposed to carry here, since the two interpretations produce very different (and both silently plausible) results.