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

your codeSQL textrows changed
your code speaks SQL text; the database turns it into row changes

key terms

CRUD

Create, read, update, delete: the four verbs, four SQL statements.

INSERT, SELECT, UPDATE, DELETE

WHERE

The filter clause. On UPDATE or DELETE, no WHERE means every row in the table.

WHERE user_id = 7

GROUP BY

Collapses rows into groups so you can count, sum, or average each one.

count(*) per country

upsert

Insert if new, update if it already exists — atomically, in one statement.

ON CONFLICT DO UPDATE

how it works

  1. 1
    SELECT to see state

    Before any write, look at the rows your WHERE will touch. This is the rehearsal.

  2. 2
    INSERT in batches

    Multi-row VALUES, one statement. One round trip instead of a hundred.

  3. 3
    UPDATE with a key

    Filter by id or another unique column. A WHERE-less UPDATE rewrites every row.

  4. 4
    DELETE in two steps

    Run the condition as a SELECT first, read the rows, then swap the verb. Every time.

  5. 5
    Wrap 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

1-- look first: which rows die today?
2SELECT * FROM orders WHERE user_id = 7;
the rehearsal: see exactly what goes
3-- then swap the verb, same condition
4DELETE FROM orders WHERE user_id = 7;
same WHERE, now it is final
5
6INSERT INTO likes (user_id, post_id)
7VALUES (7, 42)
8ON CONFLICT DO NOTHING;
re-running is safe, no duplicate errors

side by side

one batched query

  • single network round trip
  • one parse, one plan
  • locks taken once, briefly
  • the optimizer sees the whole job
VS

a loop of queries

  • 100 round trips, 100 plans
  • each pays connection latency
  • longer transactions, more locks
  • the classic N+1 bug
1db validates2WAL logs it3row updates
what the database does with one write
⚠️ watch out

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

🎤 interview

WHERE vs HAVING?watch ↗

📚 tutorial

Pagination at the database levelwatch ↗

🔧 useful

The safe way to DELETEwatch ↗

💎 deep

Upsert in one statementwatch ↗

🧠 trivia

NULL is not equal to NULLwatch ↗

🔧 useful

One query of 100 rows beats 100 querieswatch ↗