SQL Joins and Set Operations Questions

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. Move the per-row scalar computation out of the SELECT list and into a JOIN with a GROUP BY, so the database computes the aggregate once per outer-table row via a single pass, instead of re-scanning the inner table separately for every outer row.

Structured elaboration. A correlated scalar subquery in the SELECT list runs once PER ROW of the outer query, each time re-scanning (or re-probing an index on) the inner table for that one row's value. A LEFT JOIN followed by GROUP BY instead lets the engine compute the whole join and its aggregation together, typically as a single hash or merge pass over both tables, which scales much better as the outer table grows, because the cost stops being "outer rows times inner-table lookup cost" and becomes closer to "size of both tables, scanned once each."

Worked example. customers(1),(2),(3). orders(order_id=100, customer_id=1), (101, customer_id=1), (102, customer_id=2).

-- correlated subquery: one subquery execution per customer row
SELECT c.customer_id,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders_count
FROM customers c;

-- equivalent join + aggregation: one pass over both tables
SELECT c.customer_id, COUNT(o.order_id) AS orders_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

Both return the identical result: (1, 2), (2, 1), (3, 0). Note the LEFT JOIN (not INNER) and COUNT(o.order_id) (not COUNT()) are both required to correctly get 0 for customer 3, who has no orders at all; getting either one wrong (an INNER JOIN, or COUNT() instead of COUNT(order_id)) would silently drop customer 3 or overcount.

Trade-offs and pitfalls. The rewrite is only correct if you're careful about two things the original subquery handled implicitly: use LEFT JOIN (not INNER) if rows with zero matches should still appear, and use COUNT(specific_column) rather than COUNT(*) so that a customer with zero orders (whose joined columns are all NULL) counts as 0 rather than 1. The correlated-subquery form remains readable and perfectly fine for a handful of rows or a one-off analysis; the join rewrite earns its complexity specifically at scale, where the per-row subquery cost compounds.

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. Use an INNER JOIN: it naturally keeps exactly the employees who have a matching row in departments, and drops the rest, which is exactly "only employees with a department assigned."

Structured elaboration. The choice of join type should follow directly from the business rule, not the other way around. Here the rule is "only show employees who have a department," which is precisely an INNER JOIN's default behavior: it requires a match on both sides. If the rule had instead been "show every employee, and their department if they have one," a LEFT JOIN would be the correct choice, and you'd typically wrap the department column in COALESCE to show something like 'Unassigned' instead of a bare NULL.

Worked example. employees(1,'Alice',10), (2,'Bob',20), (3,'Cara',NULL). departments(10,'Engineering'), (20,'Sales').

SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.name;

Result: (Alice, Engineering), (Bob, Sales). Cara is excluded because her dept_id is NULL and NULL never equals anything, including another NULL, so she has no match to join against.

Trade-offs and pitfalls. The pitfall is doing this the other way around: writing a LEFT JOIN and then adding WHERE d.dept_id IS NOT NULL to filter out the unassigned employees. That works, but it's needless indirection: an INNER JOIN already expresses the intent directly and lets the optimizer reason about it more simply. Watch for the reverse mistake too: if a later requirement changes to "show all employees including unassigned ones," swapping to LEFT JOIN is a one-line change, but forgetting to also swap any WHERE clause that assumed non-NULL department columns is a classic way to silently reintroduce the INNER JOIN behavior.

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.