SQL Joins and Set Operations Questions

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.

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

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