SQL Query Fundamentals Questions

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.

A basic INNER JOIN combined with a WHERE filter on a joined-table column returns only rows that both match across tables and satisfy the filter, in the order the query is naturally read: join first (conceptually), then filter.

Structured elaboration

SELECT u.name, u.email, o.order_id, o.total_amount
FROM users u
JOIN orders o ON o.user_id = u.user_id
WHERE o.total_amount > 100;

Because this is an INNER JOIN (the default JOIN type), only users with at least one qualifying order appear at all; a user with zero orders, or only orders under $100, is excluded entirely, not shown with a NULL order. This is the correct behavior for "list users along with their qualifying orders", but would be the wrong choice for "list all users and flag which ones have a qualifying order" (which needs a LEFT JOIN instead).

Worked example

Given users Ann (order of $150) and Bo (order of $50): the query returns only Ann's row, since Bo's only order fails the > 100 filter and Bo has no other order to fall back on.

Trade-offs and pitfalls

The WHERE filter here is evaluated after the join produces a row, so filtering on o.total_amount this way only excludes joined rows that don't qualify; it does not, by itself, change whether unmatched left-side rows would appear (that's controlled entirely by the join type, INNER vs LEFT). Confusing these two independent decisions, join type and WHERE filter, is one of the most common sources of a subtly wrong query.

A distinct-count-per-period metric like Daily Active Users needs COUNT(DISTINCT user_id) specifically, not a plain COUNT, because a plain COUNT would count every event, over-counting any user who was active more than once in the same period.

Structured elaboration

SELECT DATE(occurred_at) AS day, COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY day;

COUNT(DISTINCT user_id) deduplicates within each day's group, so a user who logged three events on the same day still contributes exactly 1 to that day's count, correctly answering "how many distinct users were active", not "how many events happened". The GROUP BY-based alternative pushes the deduplication into an inner query instead of an aggregate function: SELECT day, COUNT(*) AS dau FROM (SELECT DISTINCT DATE(occurred_at) AS day, user_id FROM events) t GROUP BY day. The inner SELECT DISTINCT collapses each user down to at most one row per day first, so by the time the outer COUNT(*) runs there's nothing left to deduplicate, it's just counting already-unique rows.

Worked example

Given events for user 1 twice and user 2 once, all on 2025-01-01, and user 3 once on 2025-01-02: the query correctly returns DAU of 2 for 2025-01-01 (users 1 and 2, with user 1's repeat event not double-counted) and 1 for 2025-01-02 (user 3).

Trade-offs and pitfalls

Both forms are logically equivalent and, on the worked example below, return the identical numbers; which one an optimizer executes faster can differ by engine and data distribution, so it's worth trying both against real data rather than assuming one is always better.

COUNT(DISTINCT ...) is measurably more expensive than a plain COUNT at large scale, since the engine typically needs to sort or hash the values to deduplicate them; for very high-cardinality, very large-scale distinct counting (billions of events per day), an approximate distinct-count function (HyperLogLog-based, such as Postgres's hll extension or BigQuery's APPROX_COUNT_DISTINCT) trades a small, bounded error for dramatically better performance, a trade-off worth naming even though it's outside this topic's core scope.

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.

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.