SQL Joins and Set Operations Questions

Direct answer. Start by comparing actual counts against expected ones (row count of the join versus row count of the left table, and distinct-count of the join key versus its expected cardinality) to confirm inflation is really happening, then isolate WHICH join is responsible by re-running the query with joins removed one at a time, and finally check whether the culprit is the join TYPE (a genuine one-to-many relationship you didn't account for) or the join KEY itself (joining on the wrong column, or two columns at different grain).

Structured elaboration. A practical investigation sequence:

1. Confirm the LEFT table's own row count with a plain SELECT COUNT(*) FROM left_table, and compare it to the row count after each join is added, one at a time. The join where the count first jumps beyond the left table's count is your culprit.
2. For that join's key, check whether it's actually unique on the "one" side you assumed it was: SELECT key, COUNT(*) FROM right_table GROUP BY key HAVING COUNT(*) > 1. Any key with a count greater than 1 is duplicating every left row that matches it.
3. If the key looks unique but you're still seeing duplication, check whether you joined on the WRONG column, one that happens to have many-to-many overlap for reasons unrelated to your intended relationship, for example joining on shipping_country when you meant customer_id.
4. If step 2 or 3 confirms a genuine one-to-many or many-to-many relationship, decide whether to pre-aggregate the "many" side before joining, or whether DISTINCT/COUNT(DISTINCT ...) at the end is the right fix (it usually isn't, see the remediation trade-offs below).
5. For an ongoing pipeline rather than a one-off investigation, two habits catch this class of bug earlier: a checksum or hash-aggregation comparison (hash or sum a stable business key across the pre-join and post-join result sets and confirm they agree) run automatically after any join-heavy transformation, and, on the query itself, a match-cardinality column computed with a window function (labeling each left row as having had zero, one, or multiple right-side matches) so a reviewer can see the shape of the join directly in the output rather than only in an aggregate count.

Worked example. customers(1). orders(100, customer_id=1, total=50), (101, customer_id=1, total=30). order_items(item_id=1, order_id=100), (item_id=2, order_id=100), (item_id=3, order_id=101) (order 100 has two line items).

-- naive: joins orders to order_items, which duplicates each order once per item
SELECT c.customer_id, SUM(o.total) AS inflated_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id;
-- returns (1, 130.00): order 100's total (50) got counted TWICE because it has two line items

Running SELECT order_id, COUNT(*) FROM order_items GROUP BY order_id HAVING COUNT(*) > 1 immediately surfaces order 100 as a duplicating key, confirming the order_items join is the culprit and that orders-to-order_items is genuinely one-to-many.

Trade-offs and pitfalls. It's tempting to reach for SELECT DISTINCT or COUNT(DISTINCT column) the moment you see inflated numbers, and that can mask the real cause rather than fix it: DISTINCT on the WRONG grain can silently drop legitimately different rows (two real orders that happen to look identical on the columns you selected) while still leaving duplication elsewhere in the result untouched. The systematic count-comparison approach above tells you definitively which join and which key are responsible before you touch the query, rather than guessing at a fix and hoping the numbers happen to come out right.

Direct answer. A RIGHT JOIN keeps every row from the right-hand table and NULL-pads the left side where there's no match, the exact mirror of a LEFT JOIN; swapping which table is written first and changing RIGHT JOIN to LEFT JOIN produces an identical result, which is why many teams simply avoid RIGHT JOIN and standardize on LEFT JOIN for everything.

Structured elaboration. Because RIGHT JOIN and LEFT JOIN are true mirrors of each other, any RIGHT JOIN can be rewritten by physically swapping the two table references and changing the keyword, with no change in meaning at all. Teams that ban RIGHT JOIN do it for pure readability and consistency: a codebase where every join is either INNER or LEFT is easier to scan (a reader always knows "the important, must-appear table comes first"), and RIGHT JOIN's rarity means readers spend an extra beat parsing it every time it shows up, even though it's perfectly valid SQL.

Worked example. employees(employee_id, name, dept_id): (1, 'Alice', 10). departments(dept_id, dept_name): (10, 'Eng'), (20, 'Sales').

-- RIGHT JOIN: keep every department, even ones with no employees
SELECT e.name, d.dept_name
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_id;
-- returns (Alice, Eng), (NULL, Sales)

-- identical result via LEFT JOIN, with the tables swapped
SELECT e.name, d.dept_name
FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id
ORDER BY d.dept_id;
-- returns (Alice, Eng), (NULL, Sales)

Both queries return the identical two rows: Sales has no employees, so it appears with a NULL name either way.

Trade-offs and pitfalls. There's no correctness or performance difference between the two forms, so this is purely a house-style convention, not a technical constraint; some optimizers may even normalize a RIGHT JOIN into the equivalent LEFT JOIN internally before planning it. The main practical risk with RIGHT JOIN isn't the join itself, it's that a query mixing LEFT and RIGHT JOINs across a longer chain becomes genuinely hard for a reader to trace, since they can no longer assume "the must-preserve table is always written first"; standardizing on LEFT JOIN everywhere removes that ambiguity by convention rather than by any property of the SQL itself.

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