SQL Joins and Set Operations Questions

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. NULL never equals anything, including another NULL, so a join's equality condition (or a UNION/INTERSECT/EXCEPT row comparison) silently treats two NULL keys as non-matching, which can drop rows you'd expect to see or distort a downstream aggregate that assumed every row got included.

Structured elaboration. This is the same three-valued-logic fact showing up in two related places. In a join predicate, a.key = b.key evaluates to unknown (not true) whenever either side is NULL, so that pair is never considered a match, full stop, regardless of join type. In a set operation, UNION/INTERSECT/EXCEPT compare whole rows for equality, and most engines treat two NULLs in the same column position as "the same" for the PURPOSE of deduplication or set comparison (this is a special-cased exception to normal SQL NULL semantics, and it's the opposite behavior from a join predicate, which is exactly why it surprises people). Separately, once you're past the join, any NULL that a LEFT JOIN introduces into the right-hand columns will silently exclude those rows from a naive SUM or AVG (SUM ignores NULLs, which is usually fine, but AVG's denominator is the count of NON-NULL values, which can quietly change what "average" means).

Worked example. left_tbl(key, val): (1,'a'), (NULL,'b'). right_tbl(key, val): (1,'x'), (NULL,'y').

-- the NULL-keyed row on each side never matches, even though both keys "look the same"
SELECT l.val, r.val FROM left_tbl l
JOIN right_tbl r ON l.key = r.key;
-- returns only ('a','x'); the two NULL-keyed rows are silently excluded

-- COALESCE lets you decide explicitly whether NULL keys should match each other
SELECT l.val, r.val FROM left_tbl l
JOIN right_tbl r ON COALESCE(l.key, -1) = COALESCE(r.key, -1);
-- now returns ('a','x') AND ('b','y')

For the aggregate side: if orders(customer_id, amount) is LEFT JOINed from customers and a customer has no matching order, amount is NULL for that row. AVG(amount) divides by the count of non-NULL amounts, not the count of customers, so a naive AVG silently excludes the zero-order customers from the denominator rather than treating them as zero.

Trade-offs and pitfalls. COALESCE is a blunt instrument: picking a sentinel value like -1 to stand in for NULL is only safe if that sentinel can never be a legitimate key value in the data, otherwise you've just created a false match. For aggregates, the fix is usually to be explicit about what you're dividing by: COALESCE the joined column to 0 before summing if "no match" should count as zero, and compute your denominator from the LEFT side's row count directly rather than trusting COUNT/AVG's default NULL-skipping behavior to mean what you want.

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. A CROSS JOIN pairs every row from one table with every row from another, with no join condition at all: the result has (rows in table A) times (rows in table B) rows.

Structured elaboration. It's the one join type that never filters anything. Whereas INNER/LEFT/RIGHT/FULL all revolve around a matching condition, CROSS JOIN has no notion of "matching" because there's nothing to match; it's a pure combinatorial product. That makes it the right tool specifically when you want every combination of two independent dimensions, and the wrong tool (usually by accident) any other time.

Worked example. colors('red'), ('blue'). sizes('S'), ('M'), ('L').

SELECT c.color, s.size
FROM colors c CROSS JOIN sizes s
ORDER BY c.color, s.size;

Result (2 x 3 = 6 rows): (blue,L), (blue,M), (blue,S), (red,L), (red,M), (red,S). Every color appears with every size, which is exactly what a product feed generating one SKU per color/size combination needs.

Trade-offs and pitfalls. The size of a CROSS JOIN's output is multiplicative, so it's fine at small scale (a handful of colors times a handful of sizes) but dangerous between two large tables: 10,000 rows crossed with 10,000 rows is 100 million rows. It's also the join type most often produced by ACCIDENT, when a JOIN is written without a condition (or a comma-separated FROM list is missing a WHERE predicate linking the tables). A deliberate CROSS JOIN should be named as such in the SQL so a reviewer immediately knows it's intentional, and should be sanity-checked against the expected row count (rows_A * rows_B) before shipping.

Direct answer. INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table and pads unmatched right-side columns with NULL. RIGHT JOIN is the mirror image. FULL OUTER JOIN keeps unmatched rows from both sides. CROSS JOIN pairs every row on one side with every row on the other, with no matching condition at all.

Structured elaboration. For customers and orders joined on customer_id:

• INNER JOIN: one row per matching (customer, order) pair. A customer with zero orders disappears entirely; a customer with three orders appears three times.
• LEFT JOIN: every customer appears at least once. A customer with zero orders appears once with all order columns NULL. A customer with three orders still appears three times (once per order), it just never drops the zero-order customers.
• RIGHT JOIN: same idea, but it guarantees every order appears, padding missing customer columns instead.
• FULL OUTER JOIN: guarantees every customer AND every order appears, even orders whose customer_id doesn't exist in customers (an orphan row you'd otherwise never see).
• CROSS JOIN: with no condition at all, row count is simply (rows in customers) times (rows in orders). It's rarely what you want between two unrelated fact-sized tables.

Worked example. customers(1, 'Alice'), (2, 'Bob'). orders(101, customer_id=1), (102, customer_id=1), (103, customer_id=3) (note: customer_id 3 doesn't exist in customers).

• INNER JOIN on customer_id: 2 rows (both Alice's orders). Bob and the orphan order 103 vanish.
• LEFT JOIN (customers LEFT JOIN orders): 3 rows. Alice appears twice (101, 102), Bob appears once with order columns NULL. Order 103 never appears because it isn't reachable from the left side.
• FULL OUTER JOIN: 4 rows. Adds a row for order 103 with customer columns NULL, surfacing the orphan.

Trade-offs and pitfalls. The most common mistake is treating LEFT JOIN as "the safe default" without checking multiplicity: if the right side isn't unique per left key, a LEFT JOIN still duplicates the left row once per match, which silently inflates any SUM or COUNT computed downstream. RIGHT JOIN is functionally redundant (swap the table order and it's a LEFT JOIN), so most style guides ban it for readability rather than correctness. CROSS JOIN is legitimate when you deliberately want every combination (generating a date-times-category grid), but an accidental one from a missing join predicate is one of the most common production SQL bugs.