Core SQL: DDL, DML and Logical Query Order

In one line

DDL (CREATE, ALTER, DROP, TRUNCATE) defines structure; DML (INSERT, UPDATE, DELETE, SELECT) works with rows. A SELECT is evaluated logically as FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT, which explains why aliases can't be used in WHERE and why HAVING exists.

0/6 · 0%

Think of it like this

Cooking from a recipe. You gather ingredients (FROM), discard bad ones (WHERE), group them into bowls (GROUP BY), drop bowls you don't need (HAVING), plate them (SELECT), arrange them (ORDER BY), and serve only the first few plates (LIMIT).

Key ideas

01
WHERE filters rows before grouping; HAVING filters groups after aggregation. WHERE count(*) > 5 is an error; HAVING count(*) > 5 is correct.
02
SELECT aliases exist only after SELECT is evaluated, so WHERE total > 100 fails when total is an alias; ORDER BY can use it because it runs later.
03
TRUNCATE vs DELETE: TRUNCATE removes all rows by swapping in empty files (fast, minimal WAL, takes an ACCESS EXCLUSIVE lock, resets storage), while DELETE removes rows one by one (row-level WAL, fires row triggers, leaves dead tuples for VACUUM). Both are transactional in PostgreSQL.
04
INSERT ... ON CONFLICT (key) DO UPDATE is PostgreSQL's upsert; RETURNING gives back generated IDs and updated values without a second query. PostgreSQL 15+ also has standard MERGE.
05
Always write UPDATE and DELETE with a WHERE you've tested as a SELECT first, inside a transaction when doing it by hand.

Code & diagrams

core.sqlsql

-- top customers by revenue this year, only those with 5+ orders
SELECT c.id, c.name, count(*) AS orders, sum(o.total) AS revenue
FROM customer c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= date_trunc('year', now())
GROUP BY c.id, c.name
HAVING count(*) >= 5
ORDER BY revenue DESC
LIMIT 10;

-- upsert with RETURNING
INSERT INTO product_stock (product_id, qty) VALUES (42, 10)
ON CONFLICT (product_id) DO UPDATE SET qty = product_stock.qty + EXCLUDED.qty
RETURNING product_id, qty;
--  product_id | qty
-- ------------+-----
--          42 |  35

query-order.mermaiddiagram

Rendering diagram…

Interview problem

The problem

Fix three broken queries

(a) SELECT customer_id, sum(total) AS t FROM orders WHERE t > 1000 GROUP BY customer_id; (b) SELECT customer_id, name, count(*) FROM orders GROUP BY customer_id; (c) DELETE FROM orders WHERE status = 'test' ran in production and deleted 2M rows. What's wrong and how do you prevent (c)?

When it breaks

One huge DELETE of millions of rows

What you see

A long transaction holds row locks, generates massive WAL (replicas lag), and leaves millions of dead tuples; autovacuum struggles for hours.

Fix & prevent

Delete in batches (DELETE ... WHERE id IN (SELECT id ... LIMIT 10000)) with pauses, or partition by time and drop old partitions (Phase 9.2).

Explain it without notes

Why can you use a SELECT alias in ORDER BY but not in WHERE?

Practice

Write a query returning each product's total units sold, including products never sold (0).

Trade-offs

↔
Upserts and RETURNING save round trips but hide more logic in SQL; keep them readable.

Done when you can

I can write grouped, filtered and upsert queries and explain logical query order.

Command Palette