SQL Query Fundamentals Questions

The error means the engine can't decide which value of product_name to display for a category that has more than one; SQL requires every non-grouped, non-aggregated column in the SELECT list to have a single, unambiguous value per group, and by default it refuses to guess.

Structured elaboration

SELECT category, product_name FROM sales GROUP BY category groups rows by category, but if a category has multiple distinct product_name values, the engine has no rule for picking one (some engines, like MySQL in non-strict mode, will silently pick an arbitrary one instead of erroring, which is arguably worse than an error). Two genuinely different fixes depending on intent:

-- Pick one representative product per category (e.g., alphabetically first)
SELECT category, MIN(product_name) AS a_product FROM sales GROUP BY category;
-- List every distinct category/product pair, no aggregation at all
SELECT DISTINCT category, product_name FROM sales;

Worked example

Given sales(category, product_name) with rows ('a','p1'), ('a','p2'), ('b','p3'): the representative-row fix returns ('a','p1'), ('b','p3') (p1 chosen via MIN as an arbitrary but deterministic tie-breaker). The list-all-pairs fix returns all three rows unchanged, since there's no aggregation happening at all.

Trade-offs and pitfalls

The two fixes answer genuinely different business questions, not two syntaxes for the same thing: "give me one product per category" versus "show me every product-category combination that exists". Picking the wrong one produces a query that runs without error but silently answers a question nobody asked.

Parentheses change what OR groups with, and SQL's default precedence (AND binds tighter than OR) can silently produce the wrong result if you assume left-to-right reading.

Structured elaboration

Without parentheses, WHERE category = 'shirts' AND price < 20 OR on_sale = true is evaluated as (category = 'shirts' AND price < 20) OR (on_sale = true): any row that is on sale qualifies, category and price notwithstanding. Adding parentheses around the OR, category = 'shirts' AND (price < 20 OR on_sale = true), restricts the OR branch to shirts only. Neither form is "wrong" in isolation; the bug is writing one when you meant the other. NOT is the third operator the question names, and its precedence is the highest of the three: SQL evaluates NOT before AND, and AND before OR. So NOT category = 'shirts' AND price < 20 parses as (NOT category = 'shirts') AND (price < 20), not as NOT (category = 'shirts' AND price < 20); the NOT applies only to the single comparison immediately next to it, not to the whole AND expression, unless parentheses say otherwise.

Worked example

Using products(product_id, category, price, on_sale) with rows (1, shirts, 15, not on sale), (2, shirts, 25, on sale), (3, pants, 10, on sale), (4, shirts, 30, not on sale):

• Without parentheses: rows 1, 2, 3 match. Row 3 (pants) sneaks in purely because it's on sale.
• With parentheses: rows 1, 2 match. Row 3 is correctly excluded because it isn't a shirt.
• Adding NOT: WHERE NOT category = 'shirts' AND price < 20 matches only row 3 (pants, price 10). The NOT applies to category = 'shirts' alone (true for rows 1, 2, 4, so NOT makes it false for them), then ANDs that with price < 20, leaving only the one row that is both not-a-shirt and under $20. Wrapping the whole expression instead, WHERE NOT (category = 'shirts' AND price < 20), flips which rows are excluded: it matches rows 2, 3, and 4, everything except row 1 (the only row where category = 'shirts' AND price < 20 was true to begin with).

Trade-offs and pitfalls

The fix costs nothing and the failure mode is silent (no error, just wrong rows), which is what makes it dangerous in report queries nobody double-checks. As a habit: whenever AND and OR appear in the same WHERE clause, add explicit parentheses even where they aren't strictly required, purely for the next reader. The same silent-wrong-result risk applies to NOT: many people mentally read NOT a AND b as NOT (a AND b), but SQL does not; when NOT needs to negate a whole compound condition rather than just the term next to it, parenthesize it explicitly.

A strong candidate writes this directly:

SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

Structured elaboration

The clause order to reason about, and the order the database actually evaluates it in, are different things worth keeping straight:

• FROM picks the table.
• WHERE filters rows before anything else happens (status = 'active').
• ORDER BY sorts the filtered rows.
• LIMIT trims the sorted result to the first N rows.

Selecting only the columns you need (id, name, email instead of *) keeps the result narrow and avoids breaking callers if the table later gains columns.

Worked example

Given rows (1,'Ann',...,'active','2025-01-05'), (2,'Bo',...,'inactive','2025-01-06'), (3,'Cy',...,'active','2025-02-01'), (4,'Di',...,'active','2025-03-01'), the query returns Di, Cy, Ann in that order: the inactive row (Bo) is filtered out first, then the three active rows are sorted newest-first.

Trade-offs and pitfalls

LIMIT without ORDER BY gives you an arbitrary 100 rows, not "the most recent 100"; the two clauses have to travel together. If created_at can be NULL, decide up front whether those rows should sort first or last (see NULLS LAST). At real scale, LIMIT 100 with no index on (status, created_at) still requires scanning and sorting every active row before truncating, so this is the point where an index recommendation is a reasonable follow-up, not a requirement of a correct answer.

An upsert inserts a new row when the key doesn't exist yet, and updates the existing row's other columns when it does, all as one atomic statement rather than a separate check-then-insert-or-update round trip.

Structured elaboration

-- PostgreSQL / SQLite
INSERT INTO users (id, email, name, updated_at)
VALUES (1, 'a@x.com', 'Ann Updated', now())
ON CONFLICT (id) DO UPDATE
SET name = excluded.name, updated_at = excluded.updated_at;

ON CONFLICT (id) names the constraint (here, the primary key) that would otherwise cause a duplicate-key error; DO UPDATE SET ... = excluded.column uses the special excluded pseudo-table to reference the values that were being inserted, applying them as an UPDATE instead of failing. SQL Server's equivalent is the MERGE statement, which explicitly handles matched (update) and not-matched (insert) branches in one statement, a more verbose but conceptually similar tool.

Worked example

Given an existing row (1, 'a@x.com', 'Ann', '2025-01-01') and an upsert attempt with the same id=1 but name='Ann Updated' and a new updated_at: the row is updated in place to (1, 'a@x.com', 'Ann Updated', '2025-02-01'), with no duplicate row created and no error raised for the id conflict.

Trade-offs and pitfalls

Upserts are especially valuable for idempotent data loads: if the same load job runs twice (a retry after a network failure, for instance), an upsert-based load produces the same final state either way, where a plain INSERT would either error on the second run (duplicate key) or, worse, silently succeed and create duplicate rows if there's no unique constraint to catch it.

WHERE phone = NULL always evaluates to UNKNOWN, never TRUE, because NULL means "unknown value", not a value equal to itself. The correct predicate is WHERE phone IS NULL.

Structured elaboration

The = operator compares two known values. NULL isn't a value in that sense; it's the absence of one. Asking "is this unknown value equal to NULL" is itself an unanswerable question, so SQL returns UNKNOWN rather than TRUE or FALSE, and a WHERE clause only keeps rows where the predicate is TRUE. IS NULL and IS NOT NULL are special-cased operators built specifically to test for NULL, sidestepping the equality-comparison problem entirely. The second query, for rows where phone IS NOT NULL, is the direct mirror image: SELECT * FROM t WHERE phone IS NOT NULL;.

Worked example

Given t(phone) with rows (NULL) and ('555-1234'): WHERE phone = NULL returns zero rows (matching neither row, even the one that IS NULL), while WHERE phone IS NULL correctly returns the NULL row, and WHERE phone IS NOT NULL correctly returns the ('555-1234') row.

Trade-offs and pitfalls

This is one of the first NULL gotchas most people hit, and it generalizes: any comparison operator (=, !=, <, >) involving a NULL operand evaluates to UNKNOWN, not TRUE or FALSE, which is why NOT IN subqueries and != comparisons involving NULL have their own, more serious versions of the same underlying issue (see the three-valued-logic and NOT IN questions).