CRUD Queries
The four verbs of touching data.
▶ the lesson · narrated sketch video · tap for sound
the intro
CRUD is create, read, update, delete — the INSERT, SELECT, UPDATE, DELETE of SQL. The syntax is simple; the discipline is not. Always SELECT before DELETE, batch your writes, and learn GROUP BY properly because every report needs it.
fun fact ✦
The classic resume-destroyer: running DELETE without WHERE. The WHERE clause is your seatbelt.
the docs
the deep dive
the ideas behind the video — concepts, diagrams, a walkthrough and the traps to avoid
The four statements fit on a cheat sheet; the danger is what they do at scale. One forgotten WHERE turns an update into a full-table rewrite. One loop of inserts turns a fast import into a hundred network trips. CRUD mastery is mostly discipline, not syntax.
simple syntax, serious consequences
key terms
CRUD
Create, read, update, delete: the four verbs, four SQL statements.
INSERT, SELECT, UPDATE, DELETEWHERE
The filter clause. On UPDATE or DELETE, no WHERE means every row in the table.
WHERE user_id = 7GROUP BY
Collapses rows into groups so you can count, sum, or average each one.
count(*) per countryupsert
Insert if new, update if it already exists — atomically, in one statement.
ON CONFLICT DO UPDATEhow it works
- 1SELECT to see state
Before any write, look at the rows your WHERE will touch. This is the rehearsal.
- 2INSERT in batches
Multi-row VALUES, one statement. One round trip instead of a hundred.
- 3UPDATE with a key
Filter by id or another unique column. A WHERE-less UPDATE rewrites every row.
- 4DELETE in two steps
Run the condition as a SELECT first, read the rows, then swap the verb. Every time.
- 5Wrap related writes
Two changes that must succeed together go in one transaction: both commit or neither does.
the code, explained
the destructive-statement ritual, plus a safe insert
side by side
one batched query
- single network round trip
- one parse, one plan
- locks taken once, briefly
- the optimizer sees the whole job
a loop of queries
- 100 round trips, 100 plans
- each pays connection latency
- longer transactions, more locks
- the classic N+1 bug
An UPDATE or DELETE with no WHERE touches every row in the table, and autocommit makes it stick instantly. Some teams configure their client to demand confirmation before a bare UPDATE or DELETE; at minimum, rehearse every destructive statement with SELECT first.
quick answers
Why is my query slow?
Usually a missing index on the WHERE or JOIN column, or OFFSET pagination on a big table. Run EXPLAIN ANALYZE before guessing.
Can I recover a deleted row?
Only from backups, or if the transaction never committed. Assume no. This is why SELECT-before-DELETE is a ritual.
How do I paginate properly?
OFFSET is fine for small pages. For deep pages use keyset pagination: WHERE created_at < last_seen ORDER BY created_at DESC LIMIT 20.
quick hits about CRUD Queries
short answers · interviews, tips, trivia & hidden gems