SQL Joins and Set Operations Questions

Direct answer. Compare the query's actual row count to what the join's cardinality SHOULD produce (a rough estimate from each table's row count and the expected match ratio), then rebuild the query one join at a time, checking the row count after each addition, to find the exact join where it blows up.

Structured elaboration. A genuine data issue (an unexpectedly large but legitimate result) and an accidental Cartesian product look similar from a distance (the query returns way more rows than expected) but are structurally different: a Cartesian product's row count is the PRODUCT of the tables involved, which is usually an unmistakably huge and suspicious number once you compute it, whereas a real data issue tends to produce a large but still roughly additive or proportional count. Progressive construction (start from one table, add each join and re-check COUNT(*) after every addition) isolates exactly which join introduced the multiplication, because the jump will be dramatic and specific to one join, not gradual across several.

Worked example. users(1),(2). orders(order_id=10, user_id=1), (order_id=11, user_id=2). Someone intended to also filter to products relevant to each order but forgot the join predicate:

-- buggy: products isn't joined on anything, so it's an implicit Cartesian product
SELECT u.user_id, o.order_id, p.product_id
FROM users u, orders o, products p
WHERE u.user_id = o.user_id;
-- with 2 users, 2 orders, and 2 products, this returns 4 rows, not 2:
-- (1,10,100), (1,10,200), (2,11,100), (2,11,200)
-- every (user, order) pair got paired with EVERY product, because nothing
-- constrains which product goes with which order

Checking SELECT COUNT(*) FROM users u JOIN orders o ON u.user_id = o.user_id alone (without products) confirms the expected 2 rows, isolating the products table as the source of the multiplication the moment it's added back in.

Trade-offs and pitfalls. This bug is especially dangerous because it doesn't error, it just silently returns more rows than intended, often still looking plausible if the actual multiplier happens to be small. As a CI/code-review guardrail: add a data test that asserts the row count of a join-heavy query stays within some multiple of its primary table's row count (or an EXPECT-based test with a small fixture and a known expected row count), and treat any comma-separated FROM list or any JOIN whose ON clause got dropped during a refactor as an automatic review flag.

Direct answer. Write out each source's SELECT explicitly, renaming columns to a common set of names and adding any column that's missing on one side as an explicit NULL (or a sensible default) cast to the right type, so every branch of the UNION ALL produces an identically-shaped row.

Structured elaboration. UNION ALL requires the same number of columns, in the same order, with compatible types, across every branch, it does NOT reconcile mismatched column names or missing columns for you. The fix is mechanical: pick one canonical column list, and for each source, alias its columns to match, and explicitly supply a typed NULL for any column that source doesn't have at all.

Worked example. logs_v1(event_time, event_type): ('2025-01-01 10:00:00', 'view'). logs_v2(ts, type, user_id): ('2025-01-01 11:00:00', 'click', 5) (logs_v2 has an extra user_id column that logs_v1 lacks entirely).

SELECT event_time, event_type, CAST(NULL AS INTEGER) AS user_id FROM logs_v1
UNION ALL
SELECT ts AS event_time, type AS event_type, user_id FROM logs_v2
ORDER BY event_time;

Result: ('2025-01-01 10:00:00', 'view', NULL), ('2025-01-01 11:00:00', 'click', 5). logs_v1's rows correctly show a NULL user_id (since that source never captured it), and both sources' columns are now under one consistent set of names.

Trade-offs and pitfalls. The choices you make for a column that only exists on one side matter: a bare NULL correctly communicates "this source never captured this," and is usually the honest choice; a manufactured default (0, or an empty string) can look like real data to anyone querying the combined view later and should only be used if it's genuinely meaningful, not just to avoid a NULL. It's also worth explicitly casting the NULL to the target type (as shown above), since some engines will otherwise infer a type for an untyped NULL that doesn't match the corresponding column in the other branch, causing a type-mismatch error or an unwanted implicit cast across the whole UNION.

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. 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. A LATERAL join (or CROSS APPLY) lets the subquery on its right-hand side reference columns from the row currently being processed on the left, which a plain JOIN's subquery cannot do; that's exactly what you need to pull "the most recent N related rows per parent row" without a separate window-function pass.

Structured elaboration. An ordinary JOIN's right side is evaluated once, independently of the left row; it has no way to say "give me only the top 3 shipments for THIS SPECIFIC order," because it can't see which order it's currently being joined to while it's being planned. LATERAL removes that restriction: the subquery on the right can reference the current left row's columns directly, so you can write "for this customer, give me their single most recent invoice" as an ordinary correlated subquery shape, but one that's still allowed to return multiple rows and multiple columns like a real join.

Worked example. customers(customer_id): 1, 2. invoices(invoice_id, customer_id, invoice_date): (1, 1, '2025-01-01'), (2, 1, '2025-02-01'), (3, 2, '2025-01-15').

SELECT c.customer_id, i.invoice_id, i.invoice_date
FROM customers c,
LATERAL (
  SELECT invoice_id, invoice_date
  FROM invoices
  WHERE customer_id = c.customer_id
  ORDER BY invoice_date DESC
  LIMIT 1
) i
ORDER BY c.customer_id;

Result: (1, 2, '2025-02-01'), (2, 3, '2025-01-15'). Each customer correctly gets only their single most recent invoice, with the LIMIT 1 applying independently, per customer, inside the LATERAL subquery.

Trade-offs and pitfalls. A window-function alternative (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY invoice_date DESC), then filter to rn = 1) computes the SAME answer, but does so by first ranking ALL invoices across ALL customers in one pass and then filtering, whereas LATERAL with a LIMIT conceptually short-circuits per customer. Which one performs better in practice depends heavily on the engine, the index available on (customer_id, invoice_date), and whether you need just the top 1 versus top N with additional per-row logic; LATERAL tends to shine when the per-parent-row logic is genuinely complex (not just a simple rank-and-filter) or when only a small top-N is needed and a good index lets each LATERAL subquery execution be cheap. For a straightforward single flat top-N-per-group with no extra logic, the window-function form is usually the more idiomatic first choice, and LATERAL is the escape hatch once the per-row logic gets more elaborate than ROW_NUMBER can express cleanly.