SQL Joins and Set Operations Questions

Direct answer. NOT IN returns NULL (not TRUE or FALSE) for every comparison once the subquery's result set contains even a single NULL, and NULL propagates through NOT IN's implicit ANDed comparisons in a way that makes the whole condition unsatisfiable, so the query returns zero rows even though "obviously" some should qualify.

Structured elaboration. x NOT IN (a, b, NULL) is shorthand for x <> a AND x <> b AND x <> NULL. The last comparison, x <> NULL, evaluates to NULL (unknown) for every value of x, not FALSE, and ANDing anything with NULL can never produce TRUE. So the entire NOT IN expression becomes NULL for every row, and a WHERE clause only keeps rows where the condition is TRUE, which is now none of them. NOT EXISTS and LEFT JOIN ... IS NULL don't have this trap because they check for the presence of a matching ROW, not equality against a list that might contain NULL.

Worked example. customers(1),(2),(3). orders(order_id=100, customer_id=1), (order_id=101, customer_id=NULL) (an order that failed to record its customer).

-- broken: returns ZERO rows, because orders.customer_id contains a NULL
SELECT customer_id FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- correct: NOT EXISTS ignores the NULL row entirely and returns the right answer
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

The first query returns no rows at all. The second correctly returns (2,) and (3,), the customers who never placed an order.

Trade-offs and pitfalls. The safest default is NOT EXISTS: it's immune to NULLs in the subquery by construction and its correlated form generally lets the optimizer use an index on the inner table's join column efficiently, including on large, partitioned tables. LEFT JOIN ... WHERE right.key IS NULL is logically equivalent and often just as fast, and some engineers prefer its readability, but it's easy to get wrong if additional filters accidentally end up in the WHERE clause instead of the ON clause (which reintroduces the WHERE-vs-ON join-demotion bug). NOT IN is only safe when you can prove, structurally, that the subquery's column is NEVER NULL, which is a fragile guarantee to rely on in a query that will be copy-pasted and modified later, so most style guides ban NOT IN against a subquery outright.

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

Direct answer. A LATERAL join (or CROSS APPLY) lets the subquery on its right-hand side reference columns from the row currently being processed on the left, which a plain JOIN's subquery cannot do; that's exactly what you need to pull "the most recent N related rows per parent row" without a separate window-function pass.

Structured elaboration. An ordinary JOIN's right side is evaluated once, independently of the left row; it has no way to say "give me only the top 3 shipments for THIS SPECIFIC order," because it can't see which order it's currently being joined to while it's being planned. LATERAL removes that restriction: the subquery on the right can reference the current left row's columns directly, so you can write "for this customer, give me their single most recent invoice" as an ordinary correlated subquery shape, but one that's still allowed to return multiple rows and multiple columns like a real join.

Worked example. customers(customer_id): 1, 2. invoices(invoice_id, customer_id, invoice_date): (1, 1, '2025-01-01'), (2, 1, '2025-02-01'), (3, 2, '2025-01-15').

SELECT c.customer_id, i.invoice_id, i.invoice_date
FROM customers c,
LATERAL (
  SELECT invoice_id, invoice_date
  FROM invoices
  WHERE customer_id = c.customer_id
  ORDER BY invoice_date DESC
  LIMIT 1
) i
ORDER BY c.customer_id;

Result: (1, 2, '2025-02-01'), (2, 3, '2025-01-15'). Each customer correctly gets only their single most recent invoice, with the LIMIT 1 applying independently, per customer, inside the LATERAL subquery.

Trade-offs and pitfalls. A window-function alternative (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY invoice_date DESC), then filter to rn = 1) computes the SAME answer, but does so by first ranking ALL invoices across ALL customers in one pass and then filtering, whereas LATERAL with a LIMIT conceptually short-circuits per customer. Which one performs better in practice depends heavily on the engine, the index available on (customer_id, invoice_date), and whether you need just the top 1 versus top N with additional per-row logic; LATERAL tends to shine when the per-parent-row logic is genuinely complex (not just a simple rank-and-filter) or when only a small top-N is needed and a good index lets each LATERAL subquery execution be cheap. For a straightforward single flat top-N-per-group with no extra logic, the window-function form is usually the more idiomatic first choice, and LATERAL is the escape hatch once the per-row logic gets more elaborate than ROW_NUMBER can express cleanly.

Direct answer. Replace the comma-separated FROM list and its WHERE-clause join condition with an explicit JOIN ... ON, which makes the join relationship visible at the FROM clause itself rather than buried among unrelated filter conditions in WHERE.

Structured elaboration. The old comma style puts the tables to combine and the condition that relates them in two completely different places (FROM lists the tables, WHERE happens to contain the predicate that relates them), and SQL has no way to tell "this WHERE condition is the join key" apart from "this WHERE condition is an ordinary filter." If someone editing the query later removes or mis-edits that one WHERE clause (thinking it's just a filter, since nothing marks it as load-bearing), the query silently becomes a full Cartesian product between the two tables instead of erroring.

Worked example. customers(customer_id, name): (1, 'Alice'). orders(order_id, customer_id, amount): (100, 1, 150).

-- old style: the join condition is indistinguishable from an ordinary filter
SELECT c.customer_id, o.order_id
FROM customers c, orders o
WHERE c.customer_id = o.customer_id AND o.amount > 100;

-- explicit JOIN: the relationship is visible in the FROM clause itself
SELECT c.customer_id, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100;

Both return the identical result here: (1, 100). The explicit version makes it obvious, just from reading FROM/JOIN, which condition is the relationship between the tables and which (the amount filter) is an independent business rule.

Trade-offs and pitfalls. The silent-Cartesian-product risk is the main practical reason most style guides ban the comma style outright, not just a stylistic preference: with an explicit JOIN, forgetting the ON clause entirely is at minimum a jarring omission that stands out on review (and, in most engines, doesn't even parse without one, unless you deliberately wrote CROSS JOIN), whereas losing one WHERE condition among several others reads as a small, easy-to-miss diff. The two forms are logically equivalent when written correctly, so this is entirely a defensive-coding argument, not a performance or correctness difference between the two syntaxes as such.