SQL Joins and Set Operations Questions

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.

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. 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. INTERSECT returns only the rows that appear in BOTH result sets. EXCEPT (MINUS in Oracle) returns the rows that appear in the FIRST result set but not the second. Both compare whole rows, need the same number of columns with compatible types on each side, and both dedup their output.

Structured elaboration. Think of them as set arithmetic over two same-shaped result sets rather than as joins: there's no join key to specify, the entire row IS the comparison unit. That makes them a natural fit for comparing two snapshots or two populations directly, without writing an explicit join condition.

Worked example. customers_2023(customer_id): 1, 2, 3. customers_2024(customer_id): 2, 3, 4.

-- customers present in BOTH years (returning customers)
SELECT customer_id FROM customers_2023
INTERSECT
SELECT customer_id FROM customers_2024;
-- returns 2, 3

-- customers present in 2023 but not 2024 (lost customers)
SELECT customer_id FROM customers_2023
EXCEPT
SELECT customer_id FROM customers_2024;
-- returns 1

Trade-offs and pitfalls. Both operations dedup by default (like UNION, not UNION ALL), so if you need to know HOW MANY TIMES a row appeared rather than just whether it appeared, INTERSECT/EXCEPT are the wrong tool and you need an explicit join or GROUP BY instead. NULLs in the compared columns are handled specially: unlike an ordinary equality comparison, most engines treat two NULLs in the same position as equal for the purposes of this row comparison, which is the opposite of how NULL behaves in a join predicate, and is a common source of confusion when you've internalized "NULL never equals NULL" from working with joins.

Direct answer. Give every table an alias, and give every ambiguous column an explicit AS alias in the SELECT list, so the result set's column names are unambiguous no matter how many joined tables happen to share a name.

Structured elaboration. SQL resolves o.id versus s.id fine inside the query itself (the alias disambiguates it for the engine), but the OUTPUT column name is a different problem: if you write SELECT o.id, s.id, most engines will happily return two columns both literally named "id", and it's up to positional order (not name) to tell them apart downstream. That's fragile for anything reading the result by column name.

Worked example. orders(id, created_at, customer_id): (1, '2025-01-01', 1). shipments(id, created_at, order_id): (9, '2025-01-03', order_id=1).

SELECT o.id AS order_id, o.created_at AS order_created_at,
       s.id AS shipment_id, s.created_at AS shipment_created_at
FROM orders o
JOIN shipments s ON s.order_id = o.id;

Result: (order_id=1, order_created_at='2025-01-01', shipment_id=9, shipment_created_at='2025-01-03'). Every column name in the output is unique and self-describing.

Trade-offs and pitfalls. Without explicit aliasing, a BI tool or downstream script consuming the result by column NAME (rather than position) will typically just grab whichever "id" or "created_at" its driver happens to return first, silently reading the wrong one, with no error at all: the query succeeds, the numbers just quietly refer to the wrong table's column. This is worse than a query that fails outright, because nothing signals the mistake. Adopting a house convention (always alias every table, always alias every column that could collide) removes the ambiguity by construction rather than relying on every consumer downstream to guess correctly.