SQL Joins and Set Operations Questions

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. Write the join condition so a NULL on the left side's key part is treated as an automatic match against ANY value on the right (l.key2 = r.key2 OR l.key2 IS NULL), and keep that wildcard logic in the join predicate itself rather than trying to fake it by coalescing NULL to a literal placeholder string, which risks colliding with a genuinely real value.

Structured elaboration. The two halves of this question are really two sides of the same judgment call about what NULL means in a composite key. Sometimes NULL genuinely means "matches anything" (a wildcard, a business rule saying "this row applies regardless of the second key part"), and an explicit OR-with-IS-NULL clause in the join condition expresses that intent directly. Other times NULL means "unknown, do not guess," and coalescing it to a literal sentinel like the string 'NULL' to force a match is dangerous specifically because that sentinel could ALSO be a legitimate value already present in the data (a SKU literally named "NULL" as a placeholder product code, say), silently merging two things that were never meant to be the same.

Worked example. left_table(id, key1, key2, value): (1, 'US', NULL, 'v1') (a wildcard row for all of key2 under 'US'), (2, 'US', 'east', 'v2'). right_table(key1, key2, price): ('US', 'east', 100), ('US', 'west', 120).

SELECT l.id, r.key2, r.price
FROM left_table l
JOIN right_table r
  ON l.key1 = r.key1
 AND (l.key2 = r.key2 OR l.key2 IS NULL)
ORDER BY l.id, r.key2;

Result: (1, 'east', 100), (1, 'west', 120), (2, 'east', 100). Row 1 (the wildcard, key2=NULL) correctly matches BOTH right-side rows since its key2 is NULL, while row 2 (key2='east') matches only the one exact right-side row it specifies.

Trade-offs and pitfalls. The danger case for the counterpoint: imagine a real SKU is legitimately coded as the literal string 'NULL' by an upstream system's bug or convention. If you'd instead written COALESCE(l.sku, 'NULL') = COALESCE(r.sku, 'NULL') to make actual NULLs match each other, that real SKU would now be silently treated as identical to every genuinely-missing SKU, merging unrelated rows. The wildcard pattern above avoids this entirely because it never manufactures a fake value to compare against; it just tests IS NULL directly. Before implementing either pattern, confirm with whoever owns the business rule which meaning NULL is actually supposed to carry here, since the two interpretations produce very different (and both silently plausible) results.

Direct answer. A FULL OUTER JOIN keeps every row from both sides, matched where possible and NULL-padded where not, which makes it the natural tool for reconciliation: it surfaces rows that exist on only one side just as readily as ones that mismatch on both.

Structured elaboration. Build the reconciliation with COALESCE to pick a display key regardless of which side it came from, then classify each row with a CASE expression that checks, in order: is the key missing from one side entirely (only_a / only_b), and if present on both, do the values actually match (match / mismatch).

Worked example. sales_a(date, revenue): ('2025-01-01', 100), ('2025-01-02', 80). sales_b(date, revenue): ('2025-01-01', 100), ('2025-01-03', 60).

SELECT COALESCE(a.date, b.date) AS date,
       a.revenue AS revenue_a, b.revenue AS revenue_b,
       COALESCE(a.revenue,0) - COALESCE(b.revenue,0) AS delta,
       CASE
         WHEN a.date IS NULL THEN 'only_b'
         WHEN b.date IS NULL THEN 'only_a'
         WHEN a.revenue = b.revenue THEN 'match'
         ELSE 'mismatch'
       END AS reconciliation_status
FROM sales_a a
FULL OUTER JOIN sales_b b ON a.date = b.date
ORDER BY date;

Result: ('2025-01-01', 100, 100, 0, 'match'), ('2025-01-02', 80, NULL, 80, 'only_a'), ('2025-01-03', NULL, 60, -60, 'only_b'). Every discrepancy type is visible in one pass: the shared date matches exactly, and each system's unique date is flagged distinctly rather than silently dropped.

Trade-offs and pitfalls. Not every engine supports FULL OUTER JOIN natively (older MySQL versions don't); you can emulate it as a LEFT JOIN UNION a RIGHT JOIN (or, more simply, a LEFT JOIN UNION an anti-joined right-side-only query), which produces the identical result at the cost of scanning each side twice. The pattern also extends past two sources: reconciling three or four systems at once is usually cleaner as a chain of FULL OUTER JOINs (A FULL OUTER JOIN B FULL OUTER JOIN C, using COALESCE to unify the key at each step) rather than trying to write one giant CASE expression across all combinations by hand, since the number of "only in X" cases grows combinatorially with the number of sources.

Direct answer. Either apply a transformation to one or both sides inside the join condition (truncating a timestamp to a date, normalizing case or whitespace, converting a timezone) so the values line up at query time, or normalize the mismatched representation ahead of time (a computed column, a cleaned staging table) so the join itself can stay a plain equality.

Structured elaboration. Joining on an expression (ON DATE_TRUNC('day', a.event_time) = c.day) works correctly but has a real performance cost: most engines can't use a plain index on the untransformed column to satisfy a condition wrapped in a function, since the index is built on the raw stored value, not on the transformed one. Normalizing the data ahead of time (storing a pre-truncated date column, pre-lowercasing an identifier, converting to a single canonical timezone at ingest) avoids that cost by making the join condition a plain equality again, at the cost of doing (and maintaining) that normalization work upstream instead of at query time.

Worked example. app_a(user_id, event_time, kind): (1, '2025-01-01 10:00:00', 'view'). calendar(day): '2025-01-01', '2025-01-02'.

SELECT a.user_id, a.kind, c.day
FROM app_a a
JOIN calendar c ON CAST(a.event_time AS DATE) = c.day;

Result: (1, 'view', '2025-01-01'). The timestamp 2025-01-01 10:00:00 correctly matches the calendar day 2025-01-01 once truncated to a plain date for the comparison.

Trade-offs and pitfalls. Joining on an expression is the right immediate fix and is fine at modest scale, but if this join runs frequently against a large, fast-growing table, the inability to use a plain index on the raw column becomes a real cost; many engines support a functional index (an index built directly on the expression, e.g. CREATE INDEX ON app_a (CAST(event_time AS DATE))) as a middle ground that restores efficient lookups without requiring you to add and maintain a separate normalized column. Timezone mismatches are a sharper version of the same problem: silently comparing a UTC timestamp to a naive local timestamp without an explicit conversion doesn't error, it just quietly matches the wrong rows near day boundaries, which is far more dangerous than the truncation case because the mismatch can be off by hours, not just formatting.

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.