SQL Joins and Set Operations Questions

Direct answer. Move the per-row scalar computation out of the SELECT list and into a JOIN with a GROUP BY, so the database computes the aggregate once per outer-table row via a single pass, instead of re-scanning the inner table separately for every outer row.

Structured elaboration. A correlated scalar subquery in the SELECT list runs once PER ROW of the outer query, each time re-scanning (or re-probing an index on) the inner table for that one row's value. A LEFT JOIN followed by GROUP BY instead lets the engine compute the whole join and its aggregation together, typically as a single hash or merge pass over both tables, which scales much better as the outer table grows, because the cost stops being "outer rows times inner-table lookup cost" and becomes closer to "size of both tables, scanned once each."

Worked example. customers(1),(2),(3). orders(order_id=100, customer_id=1), (101, customer_id=1), (102, customer_id=2).

-- correlated subquery: one subquery execution per customer row
SELECT c.customer_id,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders_count
FROM customers c;

-- equivalent join + aggregation: one pass over both tables
SELECT c.customer_id, COUNT(o.order_id) AS orders_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

Both return the identical result: (1, 2), (2, 1), (3, 0). Note the LEFT JOIN (not INNER) and COUNT(o.order_id) (not COUNT()) are both required to correctly get 0 for customer 3, who has no orders at all; getting either one wrong (an INNER JOIN, or COUNT() instead of COUNT(order_id)) would silently drop customer 3 or overcount.

Trade-offs and pitfalls. The rewrite is only correct if you're careful about two things the original subquery handled implicitly: use LEFT JOIN (not INNER) if rows with zero matches should still appear, and use COUNT(specific_column) rather than COUNT(*) so that a customer with zero orders (whose joined columns are all NULL) counts as 0 rather than 1. The correlated-subquery form remains readable and perfectly fine for a handful of rows or a one-off analysis; the join rewrite earns its complexity specifically at scale, where the per-row subquery cost compounds.

Direct answer. With a chain of LEFT JOINs, a filter placed on the LAST table's column (even one that looks harmless) can retroactively demote an EARLIER join's outer-join semantics if that filter ends up in the WHERE clause rather than the corresponding ON clause, because WHERE evaluates after the whole chain has already produced its NULL-padded rows.

Structured elaboration. The subtlety with three or more chained outer joins is that "reordering" isn't really about which JOIN keyword comes first in the text, it's about where a downstream filter lives relative to the chain. A LEFT JOIN B LEFT JOIN C is evaluated left to right: first A LEFT JOIN B produces a NULL-padded intermediate result, then THAT result is LEFT JOINed to C. If you then filter on a column from C in the WHERE clause, any row where B (and therefore C) had no match gets excluded, even though the FIRST join (A to B) was written specifically to preserve A's unmatched rows. The fix is the same principle as the two-table case: put the filter in C's ON clause, not in WHERE, so it only constrains which C rows are eligible to match, without discarding upstream rows that legitimately have no C match at all.

Worked example. a_tbl(id): 1, 2. b_tbl(id, a_id): (10, a_id=1). c_tbl(id, b_id, flag): (100, b_id=10, flag='X'). Row a_id=2 has no matching b row at all.

-- buggy: filtering c.flag in WHERE demotes the WHOLE chain back toward an inner join
SELECT a.id, b.id AS b_id, c.id AS c_id
FROM a_tbl a
LEFT JOIN b_tbl b ON a.id = b.a_id
LEFT JOIN c_tbl c ON b.id = c.b_id
WHERE c.flag = 'X';
-- returns only (1, 10, 100). Row a.id=2 vanishes entirely, even though the
-- LEFT JOIN to b_tbl was written specifically to keep A-rows with no B match.

-- fixed: move the flag condition into c's own ON clause
SELECT a.id, b.id AS b_id, c.id AS c_id
FROM a_tbl a
LEFT JOIN b_tbl b ON a.id = b.a_id
LEFT JOIN c_tbl c ON b.id = c.b_id AND c.flag = 'X'
ORDER BY a.id;
-- returns (1, 10, 100) AND (2, NULL, NULL). Row a.id=2 is preserved, as intended.

Trade-offs and pitfalls. The practical fix for readability, once a chain gets to three or more joins, is often to restructure the later stages as a derived table or CTE that pre-filters C on its own terms BEFORE joining it to the chain, rather than relying on everyone downstream to remember which conditions belong in which ON clause. That also makes the intended semantics explicit at a glance, instead of implicit in clause placement that's easy to get subtly wrong during a later edit.

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. A hint is the right call for a critical query where you've already ruled out the underlying cause (bad statistics, a missing index) and confirmed, with evidence, that the optimizer is genuinely and repeatably choosing worse than an alternative you can specify; the maintenance risk is that the hint freezes today's decision even as the data, statistics, or engine version change, so a plan that was correct when you added the hint can silently become wrong later with no automatic recovery.

Structured elaboration. An optimizer picks a plan based on its current statistics and cost model; a hint overrides that decision with a fixed choice you specify. That's valuable exactly when you have specific knowledge the optimizer's general-purpose cost model can't capture (a business-logic guarantee about data shape that statistics won't reflect, or a case where the cost model itself is known to misjudge a particular pattern), and risky exactly because the hint doesn't get reconsidered as circumstances change the way a fresh cost-based decision would.

Before reaching for a hint: confirm statistics are current (a hint is frequently a workaround for a statistics problem that would resolve the issue more durably on its own), confirm the alternative plan you want to force is genuinely and consistently better (not just better for the one data snapshot you tested against), and consider whether a smaller, more surgical intervention (an index change, a query rewrite) would fix the root cause instead of papering over it. When a hint is genuinely the right call, treat it the same way you'd treat any other piece of code that encodes an assumption about current data: document why it's there, and revisit it periodically or when the underlying data characteristics are known to have shifted meaningfully.

Worked example. A reporting query whose optimal plan depends on a business fact the optimizer has no way to know (say, "this filter will always match under 1% of rows because of an application-level invariant, even though the column's general statistics don't obviously suggest that") is a reasonable candidate for a hint; a query that's merely slow because statistics happen to be stale THIS week is not, since refreshing statistics is the more durable fix and the hint would just hide that underlying problem until it resurfaces in a different, more confusing way later.

Trade-offs and pitfalls. A codebase that reaches for hints as a first resort, rather than a documented last resort, tends to accumulate a growing set of frozen decisions nobody remembers the reasoning behind, several of which quietly stop being correct as the data evolves; a lightweight guardrail (a comment explaining the specific evidence that justified the hint, plus periodic review) is cheap insurance against that decay.

Direct answer. Qualify every column in the composite ON condition with its table alias and AND the individual column comparisons together, then separately validate uniqueness on each side with a GROUP BY over the full composite key before trusting that the join can't silently multiply rows.

Structured elaboration. A composite-key join is just an ordinary equi-join with more than one equality condition ANDed together in the ON clause; there's nothing exotic about the join mechanics themselves. The part that actually needs deliberate verification is an assumption people often make WITHOUT checking: that the composite key is genuinely unique on at least one side. If it isn't (duplicate rows sharing the same (order_id, line_number) pair, say, from a data-quality issue), the join will fan out exactly like any other one-to-many join would, just less obviously, because the key LOOKS like it should be unique from its name and shape.

Worked example. order_items(order_id, line_number, sku): (1,1,'A'), (1,2,'B'), (2,1,'C'). line_discounts(order_id, line_number, discount_pct): (1,1,10), (2,1,5).

SELECT oi.order_id, oi.line_number, oi.sku, ld.discount_pct
FROM order_items oi
LEFT JOIN line_discounts ld
  ON oi.order_id = ld.order_id AND oi.line_number = ld.line_number
ORDER BY oi.order_id, oi.line_number;

Result: (1, 1, 'A', 10), (1, 2, 'B', NULL), (2, 1, 'C', 5). Line item (1,2) correctly gets no discount (NULL), since no line_discounts row matches that exact composite key.

To validate uniqueness before trusting the join:

SELECT order_id, line_number, COUNT(*) AS n
FROM line_discounts
GROUP BY order_id, line_number
HAVING COUNT(*) > 1;

An empty result here confirms the composite key is genuinely unique on the line_discounts side, so the join above cannot fan out order_items.

Trade-offs and pitfalls. Composite keys are more prone to a subtle mistake than single-column keys: forgetting to qualify one of the columns (writing line_number = ld.line_number without the oi. prefix, in a query where BOTH tables happen to have a column of that exact name) can silently resolve to the wrong table's column, or error with an ambiguity complaint depending on the engine and context, either of which is easy to miss in a longer query with several joins. Partially-NULL composite keys are also worth calling out explicitly: if just one part of the pair is NULL on either side, the whole composite equality fails (same three-valued-logic behavior as any other NULL join key), which can silently drop rows that a reader assumed would still match on their non-NULL parts.