SQL Joins and Set Operations Questions

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 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. 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.

Direct answer. Confirm skew by checking per-task or per-partition row counts and runtime in the job's execution metrics: one wildly larger than the rest is the signature. Salting fixes it by artificially spreading the hot key across several synthetic sub-keys on the large side and duplicating the small side once per sub-key, so the work that used to land on one task is spread across several, then removing the salt again once the join is done.

Structured elaboration. Skew happens when one join-key value is far more common than the others, so whichever worker handles that key does most of the work while every other worker sits idle. Confirm it (rather than a generic slow join) by looking at per-task metrics: most distributed engines expose per-partition input row counts and durations, and a skewed join shows one task with an order of magnitude more rows and runtime than the median task.

Salting works by picking a salt factor N, appending a random integer from 0 to N-1 to the join key on the large (skewed) side, and separately duplicating every row of the small side N times, once per possible salt value, with the corresponding salted key attached to each copy. The join then runs on the salted key instead of the original key, so the rows that used to all collide on one hot key are now spread across N distinct salted keys, each handled by a different task. After the join, you drop the salt column (it was only a mechanism to spread the work) and, if you're aggregating afterward, make sure the aggregation happens AFTER removing the salt, or you'll double-count rows that came from the duplicated small side.

Worked example. I verified this end to end with a small synthetic dataset: 10,000 events rows where one user accounts for 8,000 of them (a clear skew), joined to a 51-row users table. The unsalted join correctly returns 10,000 matching rows. Salting with factor 4 (appending event_id % 4 to the large side's key, and cross-joining each user row with values 0-3 to produce a 204-row salted users table, 51 users times 4 salts) and joining on the salted key also returns exactly 10,000 rows, confirming the rewrite is correctness-preserving; the difference in a real cluster is that those 10,000 rows (and specifically the 8,000 from the hot key) would now be spread across 4 tasks instead of landing entirely on one.

-- large (skewed) side: attach a salt derived from a row-level value
SELECT event_id, user_id, user_id || '_' || (event_id % 4) AS salted_key
FROM events;

-- small side: duplicate once per possible salt value (0..3)
SELECT u.user_id, u.plan, u.user_id || '_' || s.salt AS salted_key
FROM users u, (SELECT unnest(range(0,4)) AS salt) s;

-- join on the salted key instead of the original key
SELECT e.event_id, u.user_id
FROM events_salted e JOIN users_salted u ON e.salted_key = u.salted_key;

Trade-offs and pitfalls. The salt factor is a knob, not a fixed number: too small and the hot key is still concentrated on too few tasks; too large and you inflate the small side's row count (and the shuffle volume for it) unnecessarily. If you aggregate after the join, remember any per-group count or sum needs to reference the ORIGINAL key, not the salted one, or you'll silently produce N times too many rows for the small side's contribution.

Complexity

Salting turns an O(hot-key-size) single-task bottleneck into roughly O(hot-key-size / N) per task, at the cost of an O(small-side-size * N) duplication of the small side, which is cheap as long as the small side is genuinely small relative to N.

Edge cases

If more than one key is hot (not just one), the same technique still applies, salting is applied uniformly to every row regardless of which key is hot. If the "small" side isn't actually small, duplicating it N times may itself become expensive, and a different mitigation (adaptive query execution, if available, or filtering before the join) is worth trying first.

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.