SQL Query Fundamentals Questions

IN (subquery), EXISTS (correlated subquery: correlated means the subquery reaches back into the outer query's current row, here v.customer_id = o.customer_id ties each check to whichever order row is currently being evaluated, so conceptually it re-checks for a match once per outer row, unlike the IN subquery above, which runs once, independently, and produces one fixed list that every outer row is then compared against), and an explicit JOIN can all express "rows that have a related match", and for a simple existence check like this they're logically equivalent; the choice between them is mostly about readability and how the optimizer handles duplicates.

Structured elaboration

-- IN
SELECT order_id FROM orders WHERE customer_id IN (SELECT customer_id FROM vip_customers);
-- EXISTS
SELECT order_id FROM orders o WHERE EXISTS (SELECT 1 FROM vip_customers v WHERE v.customer_id = o.customer_id);
-- JOIN
SELECT DISTINCT o.order_id FROM orders o JOIN vip_customers v ON v.customer_id = o.customer_id;

IN and EXISTS both naturally return each qualifying order exactly once, even if vip_customers somehow had a duplicate customer_id row, because neither actually joins the row data, they just test for existence. A plain JOIN, by contrast, would return a duplicate order row for each matching vip_customers row if that table had duplicates, requiring an explicit DISTINCT to correct, since JOIN genuinely combines row data rather than testing existence.

Worked example

Given orders for customer 1 ($50) and customer 2 ($60), with only customer 1 in vip_customers: all three approaches correctly return only order 1. If vip_customers instead had two rows for customer_id 1 (a data-quality duplicate), the JOIN version would return order 1 twice unless DISTINCT is added, while IN and EXISTS would still correctly return it once.

Trade-offs and pitfalls

For large tables, most modern optimizers rewrite IN, EXISTS, and JOIN-with-DISTINCT to nearly identical execution plans when the intent is a pure existence check, so the choice is mostly stylistic; EXISTS is often considered the clearest expression of "does a related row exist" specifically because it can't accidentally introduce duplicate rows the way a raw JOIN can.

DISTINCT deduplicates whole rows (or whole selected column sets); it can't compute anything, which is exactly where GROUP BY with an aggregate becomes necessary rather than optional.

Structured elaboration

"What is the difference between GROUP BY and DISTINCT" has a simple case where they overlap: for a plain single-column distinct list with no aggregate involved, SELECT DISTINCT category FROM sales and SELECT category FROM sales GROUP BY category return the identical set of rows. For example, given categories 'electronics', 'toys', 'electronics', both return exactly the two rows 'electronics' and 'toys', with no computed value attached to either. And there's a case where they clearly don't overlap: any time you need a computed value per group, not just the group's identity. SELECT DISTINCT category FROM sales and SELECT category, COUNT(*) FROM sales GROUP BY category both group the same way internally, but only the second one can attach a count, sum, or average to each category. There is no DISTINCT-based way to also report "how many sales rows are in each category" without introducing GROUP BY (or an equivalent subquery/window function) somewhere.

Worked example

Wanting "each distinct product category, and how many products are in it" requires GROUP BY: SELECT category, COUNT(*) AS n FROM products GROUP BY category. SELECT DISTINCT category, COUNT(*) isn't valid syntax in most engines when mixing DISTINCT with an aggregate this way, and even where it parses, it doesn't mean what you'd want.

Trade-offs and pitfalls

A quick test for which tool fits: if the question has the word "how many" or any other aggregate verb (total, average, most recent) attached to each group, GROUP BY is required; if it's purely "list the unique values of X", DISTINCT is suf ficient and simpler.

Correct daily aggregation across time zones for a global dashboard starts with storing every timestamp in UTC, then converting to a target display time zone only at the point of aggregation, being careful that daylight saving transitions can make some local days 23 or 25 hours long.

Structured elaboration

A dependable strategy:

1. Store in UTC. Every timestamp column should be UTC internally (or a genuine TIMESTAMP WITH TIME ZONE type that normalizes to UTC); never store "local time" without also storing which time zone it's local to.
2. Convert at aggregation time, not at storage time: date_trunc('day', created_at AT TIME ZONE 'America/Los_Angeles') groups rows by their LOCAL calendar day in that specific time zone, computed fresh from the UTC-stored value.
3. Account for daylight saving transitions. A "day" in a DST-observing time zone can be 23 or 25 hours long on the transition dates; a naive fixed-offset conversion (UTC-8 always) will misclassify some rows near the DST boundary, while using the time zone's actual rules (via AT TIME ZONE 'America/Los_Angeles' rather than a hardcoded - interval '8 hours') handles the transition correctly.

SELECT date_trunc('day', created_at AT TIME ZONE 'America/Los_Angeles') AS local_day,
       SUM(amount) AS daily_revenue
FROM orders
GROUP BY local_day
ORDER BY local_day;

Worked example

Consider an order placed at 2024-03-11 07:30:00 UTC, the day after the US 2024 spring-forward transition (clocks in America/Los_Angeles jumped from 2:00am to 3:00am, PST to PDT, on 2024-03-10, so by 2024-03-11 the zone is already on PDT, UTC-7). The correct conversion, date_trunc('day', '2024-03-11 07:30:00' AT TIME ZONE 'America/Los_Angeles'), resolves the timestamp's local time using PDT's actual UTC-7 offset: 2024-03-11 07:30:00 UTC becomes 2024-03-11 00:30:00 local, landing correctly in the 2024-03-11 bucket. A naive fixed-offset conversion that assumes PST (UTC-8) year-round, '2024-03-11 07:30:00 UTC' - interval '8 hours', instead computes 2024-03-10 23:30:00, misclassifying the exact same order into the 2024-03-10 bucket, a full day off from where it actually belongs in local time. The one-hour DST gap is enough to flip which calendar day an order near local midnight gets attributed to, and it only shows up on the handful of days per year straddling a transition, which is exactly what makes it easy to miss in testing and painful in production.

Trade-offs and pitfalls

A dashboard that needs to show "today's revenue" simultaneously and correctly for users in multiple time zones fundamentally cannot show one universal number; either pick one canonical reporting time zone company-wide and be explicit about it in the UI, or let each viewer see their own local-day aggregation, computed fresh per viewer rather than pre-aggregated once and reused.

Both forms filter to the same rows; the difference is readability and, in a few dialects, how the optimizer treats a long list.

Structured elaboration

WHERE category IN ('electronics', 'appliances', 'furniture') and WHERE category = 'electronics' OR category = 'appliances' OR category = 'furniture' are logically equivalent. IN is shorter and scales better as the list grows; a 20-value OR chain is genuinely hard to scan. Most optimizers treat IN over a short literal list identically to the OR chain (both typically become an index range scan or a series of equality lookups), so there's no reliable performance edge for large literal lists, though very large IN lists (thousands of values) can behave differently across engines and are sometimes better served by a temp table or VALUES join.

Worked example

Given products(product_id, name, category) with categories electronics, appliances, furniture, and toys, both forms return the first three products and exclude the toys row.

Trade-offs and pitfalls

IN is the standard choice for a fixed list. Watch for the NULL trap, though it's sharper for NOT IN than for plain IN: column NOT IN (subquery) silently returns ZERO rows for the ENTIRE query if the subquery can return even one NULL, because x <> NULL evaluates to UNKNOWN for every row's comparison against that NULL, and NOT IN needs every comparison in its OR-chain to be definitively TRUE to include a row; one UNKNOWN poisons the whole chain to UNKNOWN (never TRUE), so nothing is ever returned, not even values that clearly aren't in the list. Plain column IN (subquery), by contrast, is not similarly poisoned: a NULL in the list only affects rows that don't match any real value anyway, and those rows are excluded either way (UNKNOWN and FALSE are both "not included" in a WHERE clause), so IN with a NULL in the list returns exactly the same rows as IN without one. The guard, when it matters, is the same either way: filter the subquery explicitly (... WHERE customer_id IS NOT NULL) or use NOT EXISTS/EXISTS, which never has this problem because it tests row existence rather than comparing against a list of values. For a dynamically-sized list supplied by an application, prefer parameterized array binding or a values join over string-concatenating an IN list.

When the subquery in NOT IN (SELECT ...) returns even a single NULL, the entire NOT IN comparison becomes UNKNOWN for every outer row, silently returning zero results. NOT EXISTS doesn't have this problem.

Structured elaboration

user_id NOT IN (2, NULL) conceptually expands to user_id != 2 AND user_id != NULL. The second comparison is UNKNOWN for every row (per the NULL-equality rule above), and TRUE AND UNKNOWN is UNKNOWN, not TRUE. So no row can ever satisfy the whole predicate once the list contains a NULL, no matter what user_id is. NOT EXISTS avoids this because it's checking for the existence of a matching row, not comparing values directly against a NULL, so a NULL in the subquery's rows simply fails to match rather than poisoning the entire comparison.

Worked example

Given deactivated_users(2, NULL) and users(1, 2, 3): WHERE user_id NOT IN (SELECT user_id FROM deactivated_users) returns zero rows for all three users, including users 1 and 3 who clearly aren't deactivated. The corrected version, WHERE NOT EXISTS (SELECT 1 FROM deactivated_users d WHERE d.user_id = u.user_id), correctly returns users 1 and 3.

Trade-offs and pitfalls

This is a genuinely dangerous bug because it fails silently: no error, just an empty or wrong result set, and it only shows up once the subquery's source table happens to accumulate a NULL, which might be well after the query was written and tested. The safest habit is to default to NOT EXISTS for any "not in this other set" logic, and treat NOT IN (subquery) as something to double-check for NULLability every time, or guard it explicitly with WHERE user_id NOT IN (SELECT user_id FROM deactivated_users WHERE user_id IS NOT NULL).