SQL Query Fundamentals Questions

Filtering to completed orders belongs in WHERE (a per-row condition, evaluated before aggregation); requiring at least 100 such orders per vendor belongs in HAVING (a per-group condition on the aggregate count), and mixing them up produces a subtly wrong number rather than an error.

Structured elaboration

SELECT v.vendor_id, v.name, AVG(o.amount) AS avg_order_value, COUNT(*) AS n
FROM orders o
JOIN vendors v ON v.vendor_id = o.vendor_id
WHERE o.status = 'completed'
GROUP BY v.vendor_id, v.name
HAVING COUNT(*) >= 100;

Putting the status filter in HAVING instead of WHERE would still produce a result, but the AVG and COUNT would be computed over ALL orders (completed and not), then filtered by a HAVING condition that's checking the wrong thing entirely; the two clauses aren't interchangeable substitutes even though both look like "filtering". This exact WHERE-then-HAVING combination (filter raw rows, then threshold the aggregate) is one of the most common realistic SQL patterns in business reporting: exclude bad/irrelevant rows first, then only surface groups with enough volume to be statistically meaningful.

Worked example

Given vendor 1 with orders of 100 (completed), 200 (completed), and 50 (refunded), and vendor 2 with a single 300 (completed) order: filtering to completed orders first, vendor 1's average is (100+200)/2 = 150 across 2 qualifying orders; vendor 2 has only 1 completed order. If the HAVING threshold were COUNT(*) >= 2, vendor 1 passes (2 completed orders) and vendor 2 is filtered out (only 1). The same pattern generalizes directly to the "at least 100 orders" real threshold in the question, just with a larger dataset.

Trade-offs and pitfalls

The same skill applies to several close variants of this pattern: minimum-order-count thresholds, minimum-transaction-count thresholds, and duplicate-detection HAVING counts are all the identical WHERE-then-HAVING shape with a different business label attached. Recognizing the shape matters more than memorizing any one instance of it.

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.

DELETE removes rows individually and can be filtered, rolled back, and will fire row-level triggers; TRUNCATE removes every row at once, is typically not filterable, is much faster since it deallocates storage pages rather than deleting row by row, and often can't be rolled back the same way (or fires no row-level triggers); DROP removes the table's structure entirely, not just its data.

Structured elaboration

• DELETE [FROM table WHERE ...]: a normal, logged, transactional operation. Can target a subset of rows via WHERE, participates in a transaction (rollback restores the deleted rows), and fires any row-level triggers defined on the table, once per deleted row.
• TRUNCATE TABLE: removes ALL rows (most engines don't support a WHERE clause on TRUNCATE at all), is minimally logged compared to DELETE (deallocating whole data pages rather than logging each row deletion), and is correspondingly much faster on a large table; in PostgreSQL it IS transactional and can be rolled back, but in some other engines it behaves as an auto-committing DDL (Data Definition Language: schema-altering commands like CREATE, ALTER, and DROP, as opposed to DML (Data Manipulation Language) commands like DELETE and INSERT that only touch row data)-like operation that cannot be rolled back once issued; row-level triggers typically do not fire for TRUNCATE, only certain engines' statement-level or TRUNCATE-specific triggers do.
• DROP TABLE: removes the table itself, structure, data, indexes, and constraints entirely; there's no table left to query afterward. On transactional behavior: DROP is DDL like TRUNCATE, and shares the same engine split, in PostgreSQL it runs inside a transaction block and can be rolled back like any other statement, while in some other engines (e.g., MySQL) it auto-commits immediately as DDL and cannot be undone once issued. On performance: DROP is at least as fast as TRUNCATE, typically faster, since it doesn't even need to keep an empty table structure around afterward, it just removes the storage and the catalog entry describing the table. On triggers: DROP fires no triggers at all, not row-level and not statement-level, because no row data is being touched, only the table's definition is being removed.
• Permissions: DELETE typically requires DELETE privilege on the table; TRUNCATE and DROP typically require a higher privilege level (often table-owner or a dedicated TRUNCATE/DROP grant), reflecting how much more destructive and irreversible-in-practice they are.

Worked example

Deleting all rows from a 10-million-row table with DELETE FROM t (no WHERE) works but is slow (logging each row individually) and, depending on engine and vacuum settings, may not immediately reclaim disk space; TRUNCATE TABLE t accomplishes the same "empty the table" goal far faster by deallocating storage directly, at the cost of losing per-row filtering and, on some engines, easy rollback.

Trade-offs and pitfalls

The practical decision rule: need to remove a SUBSET of rows, or need transactional rollback safety with certainty across all engines? Use DELETE. Need to empty an ENTIRE table fast and don't need row-level triggers to fire? TRUNCATE. Need the table to no longer exist at all, structure included? DROP. Confusing TRUNCATE for a "faster DELETE with a WHERE clause" is the most common practical mistake, since most engines simply don't support that.

PostgreSQL's ILIKE does case-insensitive pattern matching directly; for engines without it, wrapping both sides in LOWER() gets the same result.

Structured elaboration

-- PostgreSQL
SELECT email FROM customers WHERE email ILIKE '%@gmail.com' OR email ILIKE '%@yahoo.com';
-- Portable (no ILIKE)
SELECT email FROM customers WHERE LOWER(email) LIKE '%@gmail.com' OR LOWER(email) LIKE '%@yahoo.com';

Both domains need their own comparison, joined with OR: there's no single LIKE pattern that matches "ends in gmail.com or ends in yahoo.com" in one clause, since a pattern only expresses one shape at a time. Note the pattern literals themselves must already be lowercase in the second, portable form, since only the column side is being normalized by LOWER().

Worked example

Given emails 'a@Gmail.com', 'b@YAHOO.com', 'c@other.com': both queries correctly return the first two rows (matching regardless of how the domain's case varies) and exclude the third, since 'other.com' matches neither the gmail.com nor the yahoo.com branch of the OR.

Trade-offs and pitfalls

Wrapping the column in LOWER() (or any function) can prevent the engine from using a plain B-tree index on that column, since the index is built on the raw values, not their lowercased form; a functional index on LOWER(email) recovers the ability to seek instead of scan, at the cost of maintaining an extra index. This is worth mentioning but isn't required to answer the query itself correctly.

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).