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. Placing a filter on the right-hand table's column in WHERE, rather than in the join's ON clause, forces that column to be non-NULL to pass the filter, which throws away every left row that had no match (and therefore had NULL there), functionally converting your LEFT JOIN back into an INNER JOIN.

Structured elaboration. A LEFT JOIN's whole purpose is to preserve unmatched left rows by filling the right side with NULLs. WHERE runs AFTER the join has already produced its rows (including the NULL-padded ones), so a condition like WHERE o.status = 'SHIPPED' evaluates to unknown for any row where o.status is NULL, and WHERE discards anything that isn't TRUE. The fix is to move the condition into the ON clause instead, so it only constrains WHICH right-side rows are allowed to match in the first place, rather than filtering the joined result afterward; unmatched left rows still make it through with NULLs, exactly as a LEFT JOIN should behave.

Worked example. customers(1),(2),(3). orders(order_id=100, customer_id=1, status='SHIPPED'), (order_id=101, customer_id=2, status='PENDING'). Customer 3 has no orders at all.

-- buggy: WHERE demotes this to an INNER JOIN
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'SHIPPED';
-- returns only (1, 100). Customers 2 and 3 vanish, even though the LEFT JOIN
-- was written specifically to keep customers with no matching order.

-- fixed: move the condition into ON
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'SHIPPED';
-- returns (1, 100), (2, NULL), (3, NULL). Every customer still appears.

Trade-offs and pitfalls. A quick check for this class of bug: run the query with and without the suspect WHERE clause and compare row counts against COUNT(*) on the left table; if the "LEFT JOIN" version returns fewer rows than the left table has, something downstream demoted it. The same bug hides in more places than a literal comparison: o.status IS NOT NULL, o.amount > 0, even a seemingly harmless AND o.region = 'US' in WHERE all have the identical effect. As a review habit, any filter that mentions a LEFT-JOINed table's column should be treated as suspicious until you've confirmed it's meant to belong in ON rather than WHERE.

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 CROSS JOIN pairs every row from one table with every row from another, with no join condition at all: the result has (rows in table A) times (rows in table B) rows.

Structured elaboration. It's the one join type that never filters anything. Whereas INNER/LEFT/RIGHT/FULL all revolve around a matching condition, CROSS JOIN has no notion of "matching" because there's nothing to match; it's a pure combinatorial product. That makes it the right tool specifically when you want every combination of two independent dimensions, and the wrong tool (usually by accident) any other time.

Worked example. colors('red'), ('blue'). sizes('S'), ('M'), ('L').

SELECT c.color, s.size
FROM colors c CROSS JOIN sizes s
ORDER BY c.color, s.size;

Result (2 x 3 = 6 rows): (blue,L), (blue,M), (blue,S), (red,L), (red,M), (red,S). Every color appears with every size, which is exactly what a product feed generating one SKU per color/size combination needs.

Trade-offs and pitfalls. The size of a CROSS JOIN's output is multiplicative, so it's fine at small scale (a handful of colors times a handful of sizes) but dangerous between two large tables: 10,000 rows crossed with 10,000 rows is 100 million rows. It's also the join type most often produced by ACCIDENT, when a JOIN is written without a condition (or a comma-separated FROM list is missing a WHERE predicate linking the tables). A deliberate CROSS JOIN should be named as such in the SQL so a reviewer immediately knows it's intentional, and should be sanity-checked against the expected row count (rows_A * rows_B) before shipping.

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.