SQL Joins and Set Operations Questions

Direct answer. Self-join the entity's ranges against each other with a strict less-than on the id to avoid comparing a range to itself and to avoid reporting every pair twice, and use the standard interval-overlap condition (each range's start is before the other's end) with an explicit decision about touching endpoints and a sentinel far-future value standing in for a still-open range.

Structured elaboration. Two ranges [start1, end1) and [start2, end2) overlap exactly when start1 < end2 AND start2 < end1; whether touching endpoints (one range's end equals the other's start) count as overlapping depends on whether you treat the interval as half-open (this is the usual, and usually correct, choice, matching how most calendar and billing systems define "adjacent but not overlapping") or fully closed. A still-open range (no end timestamp yet, meaning "ongoing") needs its end treated as unbounded, effectively infinitely far in the future, rather than as NULL, which would otherwise make every overlap comparison against it silently evaluate to unknown and drop it from consideration entirely.

Worked example. sessions(session_id, user_id, session_start, session_end): (1, user 1, 10:00, 11:00), (2, user 1, 10:30, 10:45) (fully inside session 1's window), (3, user 1, 12:00, NULL) (still ongoing).

SELECT s1.session_id AS session_a, s2.session_id AS session_b
FROM sessions s1
JOIN sessions s2
  ON s1.user_id = s2.user_id
 AND s1.session_id < s2.session_id
 AND s1.session_start < COALESCE(s2.session_end, TIMESTAMP '9999-12-31')
 AND s2.session_start < COALESCE(s1.session_end, TIMESTAMP '9999-12-31')
ORDER BY s1.session_id, s2.session_id;

Result: (1, 2). Session 2 (10:30-10:45) genuinely falls inside session 1's window (10:00-11:00), so the pair is correctly reported exactly once, as (1,2) not also as (2,1). Session 3 (still open, starting at 12:00, after session 1 has ended at 11:00) correctly does NOT overlap with session 1: its start (12:00) is not before session 1's COALESCEd end (11:00), so the condition fails as it should.

Trade-offs and pitfalls. The s1.session_id < s2.session_id condition is doing two jobs at once: preventing a session from being compared to itself, and ensuring each genuinely overlapping pair is reported exactly once rather than twice (once from each session's perspective). Get the sentinel value for an open-ended range wrong (using a NULL directly in the comparison instead of COALESCing it to a concrete future timestamp) and every still-ongoing session will silently be excluded from every overlap check, which is the same class of NULL-comparison trap that shows up throughout join predicates generally. The half-open convention (touching endpoints do NOT count as overlapping) is a judgment call worth stating explicitly in code or documentation, since a fully-closed convention (touching endpoints DO count) is an equally defensible choice for a different business rule, and the query's boundary operators (< versus <=) need to match whichever one you actually intend.

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. 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. With a chain of LEFT JOINs, a filter placed on the LAST table's column (even one that looks harmless) can retroactively demote an EARLIER join's outer-join semantics if that filter ends up in the WHERE clause rather than the corresponding ON clause, because WHERE evaluates after the whole chain has already produced its NULL-padded rows.

Structured elaboration. The subtlety with three or more chained outer joins is that "reordering" isn't really about which JOIN keyword comes first in the text, it's about where a downstream filter lives relative to the chain. A LEFT JOIN B LEFT JOIN C is evaluated left to right: first A LEFT JOIN B produces a NULL-padded intermediate result, then THAT result is LEFT JOINed to C. If you then filter on a column from C in the WHERE clause, any row where B (and therefore C) had no match gets excluded, even though the FIRST join (A to B) was written specifically to preserve A's unmatched rows. The fix is the same principle as the two-table case: put the filter in C's ON clause, not in WHERE, so it only constrains which C rows are eligible to match, without discarding upstream rows that legitimately have no C match at all.

Worked example. a_tbl(id): 1, 2. b_tbl(id, a_id): (10, a_id=1). c_tbl(id, b_id, flag): (100, b_id=10, flag='X'). Row a_id=2 has no matching b row at all.

-- buggy: filtering c.flag in WHERE demotes the WHOLE chain back toward an inner join
SELECT a.id, b.id AS b_id, c.id AS c_id
FROM a_tbl a
LEFT JOIN b_tbl b ON a.id = b.a_id
LEFT JOIN c_tbl c ON b.id = c.b_id
WHERE c.flag = 'X';
-- returns only (1, 10, 100). Row a.id=2 vanishes entirely, even though the
-- LEFT JOIN to b_tbl was written specifically to keep A-rows with no B match.

-- fixed: move the flag condition into c's own ON clause
SELECT a.id, b.id AS b_id, c.id AS c_id
FROM a_tbl a
LEFT JOIN b_tbl b ON a.id = b.a_id
LEFT JOIN c_tbl c ON b.id = c.b_id AND c.flag = 'X'
ORDER BY a.id;
-- returns (1, 10, 100) AND (2, NULL, NULL). Row a.id=2 is preserved, as intended.

Trade-offs and pitfalls. The practical fix for readability, once a chain gets to three or more joins, is often to restructure the later stages as a derived table or CTE that pre-filters C on its own terms BEFORE joining it to the chain, rather than relying on everyone downstream to remember which conditions belong in which ON clause. That also makes the intended semantics explicit at a glance, instead of implicit in clause placement that's easy to get subtly wrong during a later edit.

Direct answer. Work through the join chain systematically, checking each hypothesis with a targeted SQL query and its expected-versus-actual result: schema/type mismatches on the join keys, an unexpectedly restrictive predicate, NULLs in a join key, a timezone or granularity mismatch on a date/timestamp key, and stale or late-arriving data on one side.

Structured elaboration. A reasonable order to work through:

1. Schema and key-type mismatch: confirm the columns you're joining on actually mean the same thing and are the same type (an INT id joined against a TEXT id, or a code stored with leading zeros on one side and not the other, silently matches nothing).
2. Key distribution: run SELECT COUNT(*) FROM left_table and SELECT COUNT(DISTINCT key) FROM right_table WHERE key IN (SELECT key FROM left_table) to see how many left-side keys have ANY match at all on the right, isolating whether the loss is a data-coverage problem rather than a query bug.
3. NULLs in the join key: check SELECT COUNT(*) FROM left_table WHERE key IS NULL; a NULL key can never match anything, and if a meaningful fraction of rows have one, that alone explains missing output.
4. Implicit casts: if the join key is being silently cast (a numeric column joined to a text column, for instance), test the join predicate in isolation on a handful of known-matching rows to confirm the comparison behaves as expected.
5. Timezone or truncation mismatch: for a date/timestamp key, check whether one side stores a truncated date and the other a full timestamp, or whether one is UTC and the other local time; a boundary-hugging row can fall on the wrong side of midnight and never match.
6. Data freshness: confirm both sides were loaded from the SAME point in time; a join across tables refreshed on different schedules can lose rows simply because one side hasn't caught up yet.

Worked example. Suppose orders(order_id, customer_id, order_date) joins to customers(customer_id) and the result has noticeably fewer rows than orders itself. Step 2 above, run as SELECT COUNT(*) AS total, COUNT(DISTINCT customer_id) AS distinct_keys FROM orders versus SELECT COUNT(*) FROM customers WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders), might show orders has 10,000 distinct customer_ids but only 9,400 of them exist in customers at all: that 600-row gap, not a bug in the join syntax, fully explains the missing rows, and step 3 (checking orders.customer_id for NULLs) would separately confirm whether any of that gap is NULL-key rows versus genuinely orphaned foreign keys pointing at deleted customers.

Trade-offs and pitfalls. The systematic version of this check is always cheaper than guessing: computing the row count expected from each step (how many rows COULD survive this join, given the data as it actually is) before running the real query tells you whether the "missing" rows were ever really joinable in the first place, versus lost to a query bug you introduced. It's also worth distinguishing "missing rows" from "duplicated rows" as investigation targets: the checks above are about UNDER-counting; a query that's OVER-counting (returning more than expected) points to a completely different class of cause (fan-out through a one-to-many relationship) and needs the opposite kind of check.