# SELECT, WHERE & Friends: Querying Basics
> How to read and change data with SQL: the SELECT query shape, filtering and sorting with WHERE/ORDER BY/LIMIT, and adding/changing/removing rows with INSERT/UPDATE/DELETE - without the career-defining accidents.
---
# SELECT, WHERE & Friends: Querying Basics
You've got a database. Somewhere in it are the rows you actually need - the user who can't log in,
the orders from last Tuesday, the one record someone asked you to fix "real quick." SQL is how you
ask for them. But staring at a blank query window, it's easy to feel like you're supposed to already
know the magic words.
There aren't magic words. There's a small, learnable shape that almost every query follows, and once
you can see that shape, you can reason your way to the data instead of guessing. This guide walks you
through it calmly: how to ask for data, how to narrow it down to exactly what you want, and how to
change it - including the one mistake that has ended a few people's afternoons (and we'll make sure it
never ends yours).
We'll use one small example table the whole way through - a `users` table - so you're always learning
the idea, not re-learning a new dataset.
## How to read this
- **Need to grab some data right now?** Start with [Phase 1: Asking for Data](01-asking-for-data.md) -
it's the core query shape and you'll be productive by the end of it.
- **Want it to finally make sense?** Read in order. Each phase builds on the last: ask for data,
then narrow it, then change it.
## The phases
1. **[Asking for Data: SELECT ... FROM](01-asking-for-data.md)** - the shape every query starts from,
picking columns vs. grabbing everything, and how to read the result set.
2. **[Filtering & Sorting: WHERE, ORDER BY, LIMIT](02-filtering-and-sorting.md)** - narrowing down to
the rows you want (`=`, `>`, `LIKE`, `IN`, `AND`/`OR`), ordering them, and taking only the top few.
Includes the `NULL` trap that confuses everyone.
3. **[Changing Data: INSERT, UPDATE, DELETE](03-changing-data.md)** - adding rows, modifying them, and
removing them - and the missing-`WHERE` accident that rewrites or erases your whole table, plus how
to never let it stick.
> This guide is about the day-one querying you'll reach for constantly. Joining data across multiple
> tables, grouping and counting with `GROUP BY`, and subqueries are their own topics - they live in
> follow-up guides like [SQL Joins, Explained](/guides/sql-joins-explained), so this one stays focused
> and finishable.
Related reading: [What a Database Actually Is](/guides/what-a-database-is) ·
[Relationships & Keys](/guides/relationships-and-keys) ·
[SQL Joins, Explained](/guides/sql-joins-explained)
---
# Asking for Data: SELECT ... FROM
A database is, at heart, a set of tables - and a table is just a grid: columns across the top
(the *kinds* of things you store), rows going down (one row per actual thing). A query is you
pointing at that grid and saying "give me *these* columns, from *that* table." Master that one sentence
and you've got the spine of every `SELECT` you'll ever write.
Let's meet the table we'll use for the whole guide. Picture a `users` table like this:
```text
id │ name │ email │ city │ age │ created_at
────┼────────────────┼───────────────────────┼─────────────┼─────┼────────────
1 │ Ada Lovelace │ ada@example.com │ London │ 36 │ 2026-01-04
2 │ Grace Hopper │ grace@example.com │ New York │ 41 │ 2026-01-09
3 │ Alan Turing │ alan@example.com │ London │ 29 │ 2026-02-15
4 │ Katherine J. │ kat@example.com │ Hampton │ 52 │ 2026-03-01
5 │ Linus T. │ linus@example.com │ Portland │ 33 │ 2026-03-22
```
📝 **Terminology.** A **column** is one labeled slot every row has (`name`, `age`). A **row** (also
called a **record**) is one complete entry - one user. A **table** is the whole grid. When you query,
you choose which columns you want back and the database hands you matching rows.
## The query shape: `SELECT ... FROM ...`
**What it actually is.** A `SELECT` query is a request with two essential parts: *what* you want
(the columns) and *where it lives* (the table). You read it almost like English: "SELECT name, email
FROM users" means "get me the name and email columns, from the users table."
**What it does in real life.** The database finds the table, walks its rows, and returns a fresh grid
containing only the columns you asked for. It doesn't change anything - `SELECT` only reads. You can
run it as many times as you like with zero risk.
**A real example.**
```sql
SELECT name, email
FROM users;
```
```text
name │ email
────────────────┼───────────────────────
Ada Lovelace │ ada@example.com
Grace Hopper │ grace@example.com
Alan Turing │ alan@example.com
Katherine J. │ kat@example.com
Linus T. │ linus@example.com
```
*What just happened:* You asked for two columns, `name` and `email`, from `users`. The database gave
back every row, but *only those two columns* - the `id`, `city`, `age`, and `created_at` values are
still in the table, you just didn't ask for them, so they're not in the result. The result you get
back is itself a little grid: this rows-and-columns answer is called the **result set**.
📝 **Terminology.** The grid a query returns is the **result set** - a temporary table of rows that
matched, built just to answer your question. It vanishes once you've read it; it doesn't live in the
database.
The semicolon `;` at the end marks where your statement finishes. Some tools require it, others are
forgiving - it's a good habit to always include it, especially once you start running several
statements at once.
## Picking columns vs. grabbing everything with `*`
**What it actually is.** The `*` (say "star") is a shortcut meaning "every column in this table." Instead
of listing column names, you let the database fill them all in for you.
**What it does in real life.** `SELECT *` returns every column for the matching rows - handy when you're
exploring a table and want to see what's even in there.
**A real example.**
```sql
SELECT *
FROM users;
```
```text
id │ name │ email │ city │ age │ created_at
────┼────────────────┼───────────────────────┼─────────────┼─────┼────────────
1 │ Ada Lovelace │ ada@example.com │ London │ 36 │ 2026-01-04
2 │ Grace Hopper │ grace@example.com │ New York │ 41 │ 2026-01-09
3 │ Alan Turing │ alan@example.com │ London │ 29 │ 2026-02-15
4 │ Katherine J. │ kat@example.com │ Hampton │ 52 │ 2026-03-01
5 │ Linus T. │ linus@example.com │ Portland │ 33 │ 2026-03-22
```
*What just happened:* The `*` told the database "don't make me name them - give me all the columns."
You got the entire table back, every column, every row. Same rows as before; you just widened what
came back.
⚠️ **Gotcha.** `SELECT *` is great for poking around by hand, but reach for named columns in real code
(an app, a script, a saved report). Two reasons that bite later: (1) it pulls back columns you don't
need, including possibly large or sensitive ones, which is wasteful; and (2) if someone later adds or
reorders columns in the table, code that relied on `SELECT *` and the old column order can quietly
break. Naming the columns you want is a small act of kindness to future-you.
💡 **Key point.** Every `SELECT` answers the question "*which columns*, from *which table*?" List the
columns to be precise, or use `*` to grab them all while exploring. Either way, you get back a result
set - a grid of rows - and the underlying table is untouched.
Try the shape yourself on a tiny built-in `authors` table:
```sql runnable
SELECT name, country
FROM authors;
```
*What just happened:* You asked for two columns, `name` and `country`, from `authors`, and got every
row back - but only those two columns. The `id` is still in the table; you just didn't ask for it.
## Reading the result set
When a query comes back, read it the same way every time: the **header row** at the top tells you which
columns you got and in what order; each row underneath is one matching record. If your `SELECT` listed
columns in a particular order, the result honors that order - `SELECT email, name` puts `email` first,
even though it sits second in the table. The database gives you the columns *in the order you asked*,
not the order they're stored.
```sql
SELECT email, name
FROM users;
```
```text
email │ name
───────────────────────┼────────────────
ada@example.com │ Ada Lovelace
grace@example.com │ Grace Hopper
alan@example.com │ Alan Turing
kat@example.com │ Katherine J.
linus@example.com │ Linus T.
```
*What just happened:* Same data, same rows - but because you wrote `email` first in the `SELECT`, the
result set leads with `email`. The order of names in your `SELECT` list controls the order of columns
in the answer.
One thing you've surely noticed: every example so far returned *all five rows*. That's because we
haven't told the database to narrow things down yet. Asking for "all the rows" is fine on a five-row
table; on a table with millions of rows, you'll want to filter. That's exactly what the next phase is
about.
## Recap
1. A query asks "**which columns**, from **which table**?" - that's the `SELECT ... FROM` shape.
2. `SELECT name, email FROM users;` returns only the columns you name, for every row.
3. `SELECT *` returns **every column** - perfect for exploring, but name your columns in real code.
4. What comes back is a **result set**: a temporary grid of rows. `SELECT` only reads; it never
changes the table.
5. The column order in your result follows the order you listed them in the `SELECT`.
You can now ask any table for any of its columns. Next, you'll learn to ask for only the *rows* you
care about - by far the most useful skill in everyday SQL.
---
# Filtering & Sorting: WHERE, ORDER BY, LIMIT
Returning every row was fine when the table had five of them. Real tables don't. You almost never want
*all* the users - you want the ones in London, or over 40, or the single most recently created account.
This phase is where SQL gets genuinely useful: you describe the rows you want, and the database finds
them for you.
We'll keep working with the same `users` table from Phase 1:
```text
id │ name │ email │ city │ age │ created_at
────┼────────────────┼───────────────────────┼─────────────┼─────┼────────────
1 │ Ada Lovelace │ ada@example.com │ London │ 36 │ 2026-01-04
2 │ Grace Hopper │ grace@example.com │ New York │ 41 │ 2026-01-09
3 │ Alan Turing │ alan@example.com │ London │ 29 │ 2026-02-15
4 │ Katherine J. │ kat@example.com │ Hampton │ 52 │ 2026-03-01
5 │ Linus T. │ linus@example.com │ Portland │ 33 │ 2026-03-22
```
## `WHERE` - keep only the rows that match
**What it actually is.** `WHERE` is a filter - the difference between "all users" and "users I care
about." You give it a condition, a true-or-false test, and the database checks every row against it,
keeping only the rows where the test comes out true.
**A real example.**
```sql
SELECT name, city
FROM users
WHERE city = 'London';
```
```text
name │ city
────────────────┼────────
Ada Lovelace │ London
Alan Turing │ London
```
*What just happened:* The database tested each row's `city` against `'London'`. Rows 1 and 3 passed, the
rest failed, so they're not in the result. Note the single quotes around `'London'` - text values go in
**single quotes** in SQL, numbers don't: you'd write `WHERE age = 36`, no quotes.
📝 **Terminology.** A **condition** (or *predicate*) is the true/false test in a `WHERE`. `city =
'London'` is true for some rows, false for others; `WHERE` keeps the true ones.
### Comparison operators: `=`, `>`, `<`, and friends
The most common tests compare a column to a value:
| Operator | Means | Example |
|---|---|---|
| `=` | equals | `WHERE age = 41` |
| `<>` or `!=` | not equal to | `WHERE city <> 'London'` |
| `>` | greater than | `WHERE age > 40` |
| `<` | less than | `WHERE age < 30` |
| `>=` | greater than or equal | `WHERE age >= 36` |
| `<=` | less than or equal | `WHERE age <= 33` |
```sql
SELECT name, age
FROM users
WHERE age > 40;
```
```text
name │ age
────────────────┼─────
Grace Hopper │ 41
Katherine J. │ 52
```
*What just happened:* The test `age > 40` was true only for Grace (41) and Katherine (52), so those are
the two rows you got. Everyone 40 or younger was filtered out.
Try a filter yourself on the built-in `authors` table:
```sql runnable
SELECT name, country
FROM authors
WHERE country = 'USA';
```
*What just happened:* The database tested each row's `country` against `'USA'` and kept only the
matches - Grace Hopper and Dennis Ritchie. Note the single quotes: text values go in `'single quotes'`.
### `LIKE` - match part of a text value
**What it actually is.** `LIKE` is for "contains" or "starts with" style matching on text, using `%` as
a wildcard meaning "any run of characters (including none)."
**A real example.**
```sql
SELECT name, email
FROM users
WHERE email LIKE '%@example.com';
```
```text
name │ email
────────────────┼───────────────────────
Ada Lovelace │ ada@example.com
Grace Hopper │ grace@example.com
Alan Turing │ alan@example.com
Katherine J. │ kat@example.com
Linus T. │ linus@example.com
```
*What just happened:* `%@example.com` means "anything, followed by `@example.com`." Every address ends
that way, so every row matched. `'A%'` means "starts with capital A"; `'%lan%'` means "contains `lan`
anywhere." The `%` is the workhorse here.
⚠️ **Gotcha.** `LIKE` matching is often case-sensitive - but whether it is depends on your database and
its settings. In some setups `'a%'` won't match `Ada`. If a `LIKE` returns fewer rows than you expect,
case is the first thing to check.
### `IN` - match any value from a list
**What it actually is.** `IN` is a tidy shorthand for "equals any of these." Instead of stringing
together `city = 'London' OR city = 'Portland'`, you write the list once.
**A real example.**
```sql
SELECT name, city
FROM users
WHERE city IN ('London', 'Portland');
```
```text
name │ city
────────────────┼──────────
Ada Lovelace │ London
Alan Turing │ London
Linus T. │ Portland
```
*What just happened:* `IN ('London', 'Portland')` matched any row whose `city` is either of those two -
the same result as `city = 'London' OR city = 'Portland'`, just shorter and easier to read as the list
grows.
### Combining conditions with `AND` / `OR`
**What it actually is.** `AND` means "both must be true"; `OR` means "at least one must be true." You
chain conditions together to describe more specific rows.
**A real example.**
```sql
SELECT name, city, age
FROM users
WHERE city = 'London' AND age < 35;
```
```text
name │ city │ age
────────────────┼─────────┼─────
Alan Turing │ London │ 29
```
*What just happened:* A row had to pass *both* tests - in London *and* under 35. Ada is in London but
she's 36, so she failed the second test. Only Alan satisfied both.
⚠️ **Gotcha.** When you mix `AND` and `OR` in one `WHERE`, `AND` binds tighter than `OR` - so
`A OR B AND C` reads as `A OR (B AND C)`, often *not* what you meant. When in doubt, add parentheses:
`(A OR B) AND C`. They cost nothing and remove all ambiguity.
## The `NULL` trap - use `IS NULL`, never `= NULL`
This one confuses *everybody* the first time, so let's name it clearly.
📝 **Terminology.** `NULL` is SQL's way of saying "no value here - unknown / not set." It is **not** zero,
and **not** an empty string. It's the absence of a value.
Here's the part that trips people: in SQL, `NULL` is not equal to anything - not even to another `NULL`.
The reasoning: `NULL` means "unknown," and "is one unknown thing equal to another unknown thing?" can't
truthfully be answered *yes*. So any comparison *with* `NULL` using `=` comes out "unknown," which `WHERE`
treats as not-a-match.
That means this **does not work** the way it looks:
```sql
-- WRONG: this returns no rows, even if some emails are missing
SELECT name
FROM users
WHERE email = NULL;
```
```text
name
──────
(0 rows)
```
*What just happened:* `email = NULL` is never true (it's "unknown" for every row), so `WHERE` kept
nothing - zero rows even if missing emails exist. No error, just silently empty, which is exactly why
this bites people.
To actually test for missing values, SQL gives you `IS NULL` (and `IS NOT NULL`):
```sql
-- RIGHT: this finds rows where email has no value
SELECT name
FROM users
WHERE email IS NULL;
```
*What just happened:* `IS NULL` is the proper test for "this value is absent." Use `IS NULL` to find
missing values and `IS NOT NULL` to find present ones. (Our sample `users` all have emails, so this
returns no rows here - but on a table with gaps, this is how you find them.)
💡 **Key point.** Never compare to `NULL` with `=`, `<>`, `>`, etc. - those always come out "unknown."
Use `IS NULL` / `IS NOT NULL`. The day a query mysteriously returns nothing, ask yourself: "am I
accidentally comparing against NULL?"
## `ORDER BY` - put the rows in order
**What it actually is.** Without `ORDER BY`, the database is free to hand back matching rows in *any*
order it finds convenient - you can't rely on it. `ORDER BY` lets you say "sort the result by this
column."
**A real example.**
```sql
SELECT name, age
FROM users
ORDER BY age DESC;
```
```text
name │ age
────────────────┼─────
Katherine J. │ 52
Grace Hopper │ 41
Ada Lovelace │ 36
Linus T. │ 33
Alan Turing │ 29
```
*What just happened:* `ORDER BY age DESC` sorted by `age`, highest first. `DESC` means descending
(big → small); `ASC` means ascending (small → big) and is the default if you write neither - so
`ORDER BY age` alone would put Alan (29) at the top.
⚠️ **Gotcha.** If you want a dependable order, say so with `ORDER BY`. Rows coming back "in order"
without it is luck, not a guarantee, and that luck can change when the data or the database does.
## `LIMIT` - take only the first few rows
**What it actually is.** `LIMIT` caps how many rows come back. Paired with `ORDER BY`, it answers "the
top N" questions - the newest order, the five highest scores, the oldest account.
**A real example.**
```sql
SELECT name, created_at
FROM users
ORDER BY created_at DESC
LIMIT 3;
```
```text
name │ created_at
────────────────┼────────────
Linus T. │ 2026-03-22
Katherine J. │ 2026-03-01
Alan Turing │ 2026-02-15
```
*What just happened:* You sorted by `created_at` newest-first, then `LIMIT 3` kept only the first
three - the three most recently created users. Without the `ORDER BY`, "the first 3" would be
meaningless; `LIMIT` takes the first rows *of whatever order you've established*, so it almost always
travels with `ORDER BY`.
📝 **Terminology note.** `LIMIT` is what PostgreSQL, MySQL, and SQLite use. SQL Server uses `TOP`
(`SELECT TOP 3 ...`); Oracle has its own syntax (`FETCH FIRST 3 ROWS ONLY`). Same idea everywhere, the
keyword differs - we'll use `LIMIT` throughout.
## The order of the clauses
These pieces always go in the same order. The database expects this sequence, and writing them out of
order is a syntax error:
```mermaid
flowchart LR
S["SELECT columns
(what you want)"] --> F["FROM table
(where it lives)"]
F --> W["WHERE condition
(which rows to keep)"]
W --> O["ORDER BY column
(how to sort them)"]
O --> L["LIMIT n
(how many to take)"]
```
Not every query needs every clause, but when they appear together, this is the order: pick the table,
filter to the rows you want, sort them, then take the top few.
## Recap
1. **`WHERE`** keeps only rows where a condition is true. Text values go in `'single quotes'`; numbers
don't.
2. Compare with `=`, `<>`, `>`, `<`, `>=`, `<=`; match text patterns with **`LIKE`** and `%`; match a
list with **`IN`**; combine with **`AND`** / **`OR`** (parenthesize when you mix them).
3. **`NULL` is not equal to anything** - use **`IS NULL`** / **`IS NOT NULL`**, never `= NULL`.
4. **`ORDER BY`** sorts the result (`ASC` default, `DESC` for reverse); without it, order isn't
guaranteed.
5. **`LIMIT n`** takes the first `n` rows - pair it with `ORDER BY` to get a meaningful "top N."
6. The clauses go in a fixed order: `SELECT → FROM → WHERE → ORDER BY → LIMIT`.
You can now read exactly the data you want. Next comes the other half of SQL - *changing* it - where the
stakes go up and one missing word can rewrite an entire table. We'll make sure it never catches you.
## Try it yourself
This runs real SQLite in your browser against a tiny `authors` table - edit and run it:
```sql runnable
SELECT name, country FROM authors WHERE country = 'UK' ORDER BY name;
```
---
# Changing Data: INSERT, UPDATE, DELETE
So far everything you've run has been safe - `SELECT` only reads, so the worst that happens is you get
the wrong rows back and try again. This phase is different. `INSERT`, `UPDATE`, and `DELETE` *change* the
table. Used carefully they're completely routine; used carelessly, one of them can ruin an afternoon (or
worse). So we'll learn the commands *and* the habit that keeps you safe - in the same breath.
Same `users` table as before:
```text
id │ name │ email │ city │ age │ created_at
────┼────────────────┼───────────────────────┼─────────────┼─────┼────────────
1 │ Ada Lovelace │ ada@example.com │ London │ 36 │ 2026-01-04
2 │ Grace Hopper │ grace@example.com │ New York │ 41 │ 2026-01-09
3 │ Alan Turing │ alan@example.com │ London │ 29 │ 2026-02-15
4 │ Katherine J. │ kat@example.com │ Hampton │ 52 │ 2026-03-01
5 │ Linus T. │ linus@example.com │ Portland │ 33 │ 2026-03-22
```
## The cheat-card: stay safe while changing data
Before the details, here's the whole survival kit. If you remember nothing else from this phase,
remember this:
| Want to... | Use | The one rule |
|---|---|---|
| Add a new row | `INSERT INTO ... VALUES ...` | Match your columns to your values, in order. |
| Change existing rows | `UPDATE ... SET ... WHERE ...` | **Write the `WHERE` first.** No `WHERE` = changes *every* row. |
| Remove rows | `DELETE FROM ... WHERE ...` | **Write the `WHERE` first.** No `WHERE` = deletes *every* row. |
| Try before you trust | Wrap in a transaction | `BEGIN;` → run it → check → `COMMIT;` or `ROLLBACK;` |
The thread running through all of it: with `UPDATE` and `DELETE`, the `WHERE` clause is not optional
decoration - it's the seatbelt. Now let's go command by command.
## `INSERT` - add a new row
**What it actually is.** `INSERT` adds one (or more) brand-new rows to a table - a new user signs up, a
new order is placed. You tell it which columns you're filling and what values to put in them. It's the
gentlest of the three: it only adds, so it can't overwrite or erase existing rows.
**A real example.**
```sql
INSERT INTO users (name, email, city, age, created_at)
VALUES ('Margaret H.', 'margaret@example.com', 'Boston', 45, '2026-06-19');
```
```text
INSERT 0 1
```
*What just happened:* You added one new user. The first part lists the columns you're filling; `VALUES`
gives the matching values *in the same order* - `name` gets `'Margaret H.'`, `email` gets the address,
and so on. The `INSERT 0 1` reply (PostgreSQL's wording) confirms one row was inserted.
Notice we didn't set `id`. Many tables generate `id` automatically (an auto-incrementing key), so you
leave it out and let the database assign the next number - see [Relationships & Keys](/guides/relationships-and-keys)
for how that's set up.
⚠️ **Gotcha.** The columns list and the `VALUES` list must line up - same count, same order. If you
swap two values, SQL won't catch it as long as the *types* fit: putting a city where a name goes is a
perfectly valid string, so the database happily stores `'Boston'` as someone's name. Listing the column
names explicitly (rather than relying on table order) makes these mix-ups far less likely.
## `UPDATE` - change rows that already exist
**What it actually is.** `UPDATE` modifies values in rows already in the table - someone moves city,
fixes a typo, changes their email. You say which column(s) to change, what to change them to, and -
critically - *which rows*. Everything you don't name stays as it was.
**A real example.**
```sql
UPDATE users
SET city = 'Cambridge'
WHERE id = 3;
```
```text
UPDATE 1
```
*What just happened:* You changed exactly one row - Alan Turing, `id = 3` - setting his `city` to
`'Cambridge'`. His other columns are untouched. The `UPDATE 1` reply is a number worth reading: expected
one row and it says `UPDATE 1`, good. Says `UPDATE 5`? Stop and look.
You can change several columns at once by separating them with commas:
```sql
UPDATE users
SET city = 'Cambridge', age = 30
WHERE id = 3;
```
```text
UPDATE 1
```
*What just happened:* Same single row, two columns updated together. The `SET` list can be as long as
you like; `WHERE` still decides *which* rows it applies to.
### ⚠️ The career-defining gotcha: `UPDATE` with no `WHERE`
Here's the one that has its own genre of horror stories. Look closely at what's missing:
```sql
-- DANGER: no WHERE clause
UPDATE users
SET city = 'Cambridge';
```
```text
UPDATE 6
```
*What just happened:* With no `WHERE` to narrow it down, the `UPDATE` applied to **every single row**.
*Everyone* now lives in Cambridge - Ada, Grace, Alan, Katherine, Linus, and Margaret, all overwritten in
one stroke. `UPDATE 6` is the database calmly telling you that you just changed six rows. There's no "are
you sure?" prompt - SQL did exactly what you told it to.
This is not a rare or beginner-only mistake - experienced people have wiped production tables this way,
usually while moving fast. The fix is a *habit*, not a feature:
💡 **Key point - the WHERE-first habit.** When writing an `UPDATE` or `DELETE`, type the `WHERE` clause
*before* you type the `SET` (or before you run anything). Make narrowing the rows the first thing you do,
not the last thing you remember. A second habit that pairs with it: run a `SELECT` with the *same*
`WHERE` first to see exactly which rows you're about to touch.
```sql
-- Look before you leap: see which rows the WHERE matches
SELECT id, name, city
FROM users
WHERE id = 3;
```
```text
id │ name │ city
────┼─────────────┼───────────
3 │ Alan Turing │ London
```
*What just happened:* This is a dry run. You've confirmed `WHERE id = 3` matches exactly the one row you
mean to change - Alan - *before* running the `UPDATE`. Swap the `SELECT ...` for `UPDATE users SET ...`
keeping the identical `WHERE`, and you change precisely what you just previewed.
## `DELETE` - remove rows
**What it actually is.** `DELETE` removes whole rows from a table - a user closes their account, a record
was created by mistake, old data gets cleaned out. Same shape as `UPDATE`: a `WHERE` decides which rows
go.
**A real example.**
```sql
DELETE FROM users
WHERE id = 5;
```
```text
DELETE 1
```
*What just happened:* The row with `id = 5` (Linus T.) is gone - removed from the table. `DELETE 1`
confirms one row was deleted. As with `UPDATE`, read that number: your sanity check on how much you just
removed.
### ⚠️ The same trap, sharper: `DELETE` with no `WHERE`
`DELETE` carries the identical gotcha as `UPDATE`, except the consequence is even more final - you're not
overwriting data, you're erasing it:
```sql
-- DANGER: no WHERE clause
DELETE FROM users;
```
```text
DELETE 6
```
*What just happened:* With no `WHERE`, `DELETE` removed **every row in the table**. The `users` table is
now empty - all six users gone. The table structure (the columns) still exists, but it holds nothing, and
once committed, those rows aren't coming back unless you have a backup. The single most expensive line in
this guide, which is exactly why the cheat-card puts "write the `WHERE` first" in bold.
## Your safety net: transactions
Habits prevent most accidents. Transactions catch the rest - here's the idea that makes changing data far
less scary.
📝 **Terminology.** A **transaction** is a group of changes the database treats as one all-or-nothing
unit. You open it with `BEGIN`, make your changes, then either `COMMIT` (make them permanent) or
`ROLLBACK` (undo everything since the `BEGIN`, as if it never happened).
**A real example.**
```sql
BEGIN;
DELETE FROM users
WHERE city = 'London';
-- Check the damage before committing:
SELECT count(*) FROM users;
```
```text
count
───────
4
```
*What just happened:* You opened a transaction, ran a `DELETE`, then peeked at the table. You expected to
remove the two London users (Ada and Alan), leaving four, and `count(*)` says 4. That looks right, so
you'd make it permanent:
```sql
COMMIT;
```
But suppose the count had been wrong - say it showed `0`, meaning you'd accidentally deleted everyone.
Inside a transaction, you're not stuck:
```sql
ROLLBACK;
```
*What just happened:* `ROLLBACK` undid every change since `BEGIN`. The deleted rows snap back as if the
`DELETE` never ran - the seatbelt working: a transaction gives you a chance to *look* before your change
becomes permanent, and a way to back out if it's wrong.
💡 **Key point.** For any `UPDATE` or `DELETE` you're even slightly unsure about, wrap it in a
transaction: `BEGIN`, run it, `SELECT` to verify, then `COMMIT` if it's right or `ROLLBACK` if it's not.
It turns "oh no" into "phew." Transactions do more - they're how databases keep data consistent even when
many things happen at once, a topic of its own; see [Transactions & ACID](/guides/transactions-and-acid)
for the full picture.
⚠️ **Gotcha.** A transaction only protects you *before you commit*. Once you run `COMMIT`, the change is
permanent and `ROLLBACK` can't help. Many tools also run in "autocommit" mode by default - every
statement commits instantly unless you explicitly `BEGIN` first. When the change matters, type `BEGIN`
first.
## Recap
1. **`INSERT INTO ... VALUES ...`** adds new rows - line your columns up with your values, in order.
2. **`UPDATE ... SET ... WHERE ...`** changes existing rows; **`DELETE FROM ... WHERE ...`** removes
them.
3. **The big one:** an `UPDATE` or `DELETE` with **no `WHERE` hits every row** - rewriting or erasing the
whole table, with no confirmation prompt.
4. **Write the `WHERE` first**, and `SELECT` with the same `WHERE` to preview which rows you'll touch.
Read the affected-row count the database reports back.
5. **Wrap risky changes in a transaction:** `BEGIN` → change → `SELECT` to check → `COMMIT` or
`ROLLBACK`. It only protects you until you commit.
You can now read data with `SELECT`, narrow it with `WHERE`, sort and limit it, and change it safely with
`INSERT`, `UPDATE`, and `DELETE` - the everyday core of SQL, the same handful of shapes you'll use for
years. When you're ready to pull data from more than one table at a time, head to
[SQL Joins, Explained](/guides/sql-joins-explained).