SQL Query Fundamentals Questions

The same NOT EXISTS/NOT IN and LEFT JOIN...IS NULL patterns apply in the opposite entity direction here (products with no orders, rather than customers with no orders), and the choice between the two approaches carries over identically.

Structured elaboration

-- NOT EXISTS / NOT IN
SELECT p.product_id FROM products p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.product_id);
-- LEFT JOIN ... IS NULL
SELECT p.product_id FROM products p
LEFT JOIN orders o ON o.product_id = p.product_id
WHERE o.product_id IS NULL;

Both correctly answer "which products have never been ordered". NOT EXISTS tends to read more directly as "there is no order for this product"; LEFT JOIN...IS NULL is sometimes preferred when the query also needs other columns from the (absent) matching row for context, or when a team's SQL style already leans on LEFT JOIN elsewhere for consistency.

Worked example

Given products(1, 2) and orders containing one row referencing product 1: both queries correctly return product 2 as the only never-ordered product.

Trade-offs and pitfalls

Avoid the raw NOT IN (subquery) form unless the orders table's product_id column is guaranteed NOT NULL (many order tables enforce a NOT NULL foreign key on product_id specifically, since an order without a product rarely makes business sense). This is a genuinely narrower risk than the general anti-join caution: on a table keyed to a required entity like product_id, NOT IN is comparatively safer than on a table where the joined-on column can be legitimately NULL, for example a customer_id left NULL by a guest checkout on a customers-to-orders anti-join, where NOT IN's NULL-poisoning risk (one NULL in the subquery's results silently zeroes out the entire result set) is much more plausible to actually hit. Either way, NOT EXISTS sidesteps the question entirely, since it never depends on whether the subquery's column can be NULL.

GROUP BY 1, 2 (positional) is terser but fragile under refactoring; repeating the full expression is explicit but verbose and error-prone to keep in sync; a CTE (Common Table Expression) with a named alias is usually the most maintainable middle ground.

Structured elaboration

-- Positional: fragile if SELECT list columns get reordered
SELECT region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2;
-- Repeated expression: verbose, and easy to accidentally diverge from the SELECT list
SELECT region, DATE_TRUNC('month', order_date) AS month, SUM(amount)
FROM orders GROUP BY region, DATE_TRUNC('month', order_date);
-- CTE with alias: compute once, group by the readable name
WITH base AS (
  SELECT region, DATE_TRUNC('month', order_date) AS month, amount FROM orders
)
SELECT region, month, SUM(amount) FROM base GROUP BY region, month;

Positional GROUP BY is standard SQL and works everywhere, but if someone later reorders or adds a column to the SELECT list, the grouping silently changes to match different columns, a genuinely dangerous refactoring hazard. Repeating the expression avoids that but means the transformation logic is duplicated and can drift if only one copy is edited. A CTE materializes the computed expression once under a name, so both the SELECT list and the GROUP BY reference the same alias with no duplication.

Worked example

Given orders(region, order_date, amount) with rows (US, 2024-01-05, 100), (US, 2024-01-20, 50), (EU, 2024-01-10, 30), (US, 2024-02-01, 40): the original query, SELECT region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2, correctly returns three rows: EU/January 30, US/January 150, US/February 40.

Now suppose a teammate inserts customer_segment as a new first column in the SELECT list (every row here happens to be segment 'B2C') and forgets to update the GROUP BY: SELECT customer_segment, region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2. The GROUP BY clause still says 1, 2, but those ordinals now point at customer_segment and region, not region and the month expression. Because the third column (the month) is no longer part of the grouping, engines diverge sharply on what happens: strict engines like PostgreSQL raise an error (every non-aggregated SELECT column must appear in GROUP BY), while others (SQLite, and MySQL outside strict mode) silently pick an arbitrary month value per group and merge everything else. On the latter, the result silently collapses to two rows, B2C/EU 30 and B2C/US 190, with the January 150 and February 40 for US silently summed together into one bucket, no error and no warning that two different months got merged.

Trade-offs and pitfalls

For a one-off, throwaway query, positional GROUP BY is fine and common in ad hoc analysis. For anything that will live in a dashboard, a scheduled report, or a codebase, prefer the CTE-with-alias pattern: it reads clearly and is safe under later edits to the SELECT list.

BETWEEN is inclusive on both ends: amount BETWEEN 50 AND 500 means amount >= 50 AND amount <= 500.

Structured elaboration

The two forms are exactly equivalent:

-- BETWEEN
SELECT transaction_id FROM transactions WHERE amount BETWEEN 50 AND 500;
-- Explicit form
SELECT transaction_id FROM transactions WHERE amount >= 50 AND amount <= 500;

BETWEEN reads more naturally for a closed range and is the standard idiom; the explicit form is sometimes preferred when the range needs to be half-open (e.g., excluding the upper bound), since BETWEEN has no built-in way to express that.

Worked example

With amounts 49.99, 50.00, 300, 500.00, and 500.01: the query returns the middle three rows (50.00, 300, 500.00). 49.99 and 500.01 are correctly excluded, confirming both bounds are inclusive.

Trade-offs and pitfalls

On floating-point columns, a boundary value that "looks like" 500 might actually be stored as 499.9999999 due to representation error, silently falling outside an inclusive BETWEEN. This is one of several reasons DECIMAL/NUMERIC types are preferred over floating point for money. BETWEEN on TIMESTAMP columns has its own, more common trap: a date-only upper bound like occurred_at BETWEEN '2024-03-01' AND '2024-03-31' is interpreted as '2024-03-31 00:00:00', midnight at the very start of that day, so every transaction later that same day (say, 2024-03-31 18:00:00) is strictly greater than the upper bound and gets silently excluded, even though it clearly belongs in "March 2024" by any reasonable reading. The fix is the same half-open pattern used for numeric ranges: express the upper bound as the start of the NEXT period, exclusive, e.g. occurred_at >= '2024-03-01' AND occurred_at < '2024-04-01', which never depends on knowing the column's exact time precision.

Applying De Morgan's laws, NOT (status = 'shipped' OR status = 'delivered') becomes status != 'shipped' AND status != 'delivered' (or, more robustly with NULLs in mind, status NOT IN ('shipped', 'delivered')).

Structured elaboration

De Morgan's laws state: NOT (A OR B) = (NOT A) AND (NOT B), and symmetrically NOT (A AND B) = (NOT A) OR (NOT B). Applied here: NOT (status = 'shipped' OR status = 'delivered') distributes the NOT across the OR, flipping it to AND, and negates each comparison: status != 'shipped' AND status != 'delivered'.

Worked example

For a row with status = 'shipped': the original form is NOT (TRUE OR FALSE) = NOT TRUE = FALSE. The rewritten form is FALSE AND TRUE = FALSE. Both agree the row is excluded, as expected since it IS shipped.

For a row with status = 'processing' (neither shipped nor delivered): both status != 'shipped' AND status != 'delivered' and status NOT IN ('shipped', 'delivered') correctly evaluate to TRUE, including the row. For a row with status = NULL: both forms evaluate to UNKNOWN (NULL != 'shipped' is UNKNOWN, not TRUE), so the row is silently excluded, even though it isn't actually shipped or delivered either. The NULL-inclusive rewrite, (status != 'shipped' AND status != 'delivered') OR status IS NULL, correctly includes both the 'processing' row and the NULL row, while still excluding 'shipped' and 'delivered'.

Trade-offs and pitfalls

Removing a top-level NOT often makes a predicate easier for a human to scan (double negatives are hard to reason about quickly) and, on some optimizers, easier to reason about for index usage, since a bare AND of positive-form comparisons composes more predictably with other WHERE clauses than a NOT wrapped around a compound expression. The rewrite is purely logical, though: if status can be NULL, both the original and the rewritten form silently exclude those rows under three-valued logic, since NULL != 'shipped' evaluates to UNKNOWN, not TRUE, and a WHERE clause only keeps rows where the predicate is TRUE. To include NULLs explicitly, OR in an IS NULL check: (status != 'shipped' AND status != 'delivered') OR status IS NULL (or the NOT IN equivalent, status NOT IN ('shipped', 'delivered') OR status IS NULL).

At least four distinct reasons a filter that "should" match can return zero rows: a type/format mismatch (customer_id stored as text with leading zeros or whitespace, compared to a bare integer), a NULL-related predicate elsewhere in the query silently dropping rows, an implicit filter from a JOIN turning into an INNER join, or simply querying the wrong schema, table, or environment (a replica lagging behind, or a staging database that looks identical to production).

Structured elaboration

A systematic debugging checklist, roughly in order of how cheap each check is:

1. Confirm you're querying the right place. SELECT current_database(), check the connection's schema search path, and confirm you're not accidentally pointed at a read replica that hasn't caught up yet.
2. Isolate the WHERE clause. Run SELECT * FROM orders WHERE customer_id = 12345 with no other joins or filters. If that alone returns nothing, the problem is in the predicate or the data, not elsewhere in the query.
3. Check for a type/format mismatch. SELECT customer_id, typeof(customer_id) FROM orders LIMIT 5 (or the engine's equivalent) to confirm the column isn't storing '12345 ' (trailing space) or '012345' as text, which won't equality-match a bare integer literal.
4. Check if a JOIN elsewhere in the real query silently filtered the row out. A LEFT JOIN followed by a WHERE clause on the joined table's columns behaves like an INNER JOIN (a separate, common trap).
5. Confirm the row genuinely exists in this exact table, not a similarly-named one, and that no row-level security policy or view definition is filtering it out invisibly.

Worked example

Given orders(customer_id TEXT) with a single row '12345 ' (trailing space): WHERE customer_id = '12345' returns zero rows, while WHERE TRIM(customer_id) = '12345' correctly finds it, confirming a whitespace mismatch as one concrete instance of reason #3.

Trade-offs and pitfalls

The instinct to immediately rewrite the query is usually premature; the fastest path is isolating variables one at a time (drop joins, drop extra filters, check raw data) rather than guessing. This checklist format is itself a useful thing to state out loud in an interview: it signals systematic debugging instinct rather than one lucky guess.