# I can't remember SQL syntax

## Reading a SELECT

| SQL | English reading |
| --- | --- |
| `SELECT name FROM users` | from the `users` table, give me `name` for every row |
| `SELECT *` | every column |
| `SELECT name, email` | just these two columns |
| `SELECT DISTINCT country FROM users` | each `country` value once, no duplicates |
| `SELECT * FROM users u` | call `users` "`u`" for the rest of this query |
| `SELECT price AS cost` | output this column under the name `cost` |
| `SELECT COUNT(*) FROM users` | how many rows are in `users` |

## WHERE — which rows to keep

| SQL | English reading |
| --- | --- |
| `WHERE age = 18` | keep only rows where `age` is exactly 18 |
| `WHERE age != 18` | keep rows where `age` is anything but 18 |
| `WHERE age >= 18` | 18 or older |
| `WHERE is_verified AND is_active` | both must be true |
| `WHERE role = 'owner' OR role = 'admin'` | either one is enough |
| `WHERE NOT is_archived` | keep rows where `is_archived` is false |
| `WHERE role IN ('owner', 'admin')` | `role` is one of this list |
| `WHERE role NOT IN ('member')` | `role` is none of this list |
| `WHERE age BETWEEN 18 AND 30` | 18 to 30, endpoints included |
| `WHERE name LIKE 'A%'` | `name` starts with `A` |
| `WHERE name LIKE '%z'` | ends with `z` |
| `WHERE name LIKE '%an%'` | contains `an` |
| `WHERE name LIKE 'A_'` | `A` then exactly one more character |
| `WHERE name ILIKE 'a%'` | starts with `a`, ignoring upper/lowercase |

## NULL — the "no value" trap

| SQL | English reading |
| --- | --- |
| `NULL` | unknown — no value at all (not zero, not "") |
| `WHERE name IS NULL` | keep rows whose `name` is empty |
| `WHERE name IS NOT NULL` | keep rows that have a `name` |
| `WHERE name = NULL` | ✗ always matches nothing — `= NULL` is never true; use `IS NULL` |
| `COALESCE(name, 'Anon')` | `name`, or `'Anon'` when `name` is NULL (first non-null wins) |

## Sorting & paging

| SQL | English reading |
| --- | --- |
| `ORDER BY created_at` | sort by `created_at`, oldest first |
| `ORDER BY created_at DESC` | newest first |
| `ORDER BY last_name, first_name` | by `last_name`, then `first_name` to break ties |
| `LIMIT 10` | at most 10 rows |
| `LIMIT 10 OFFSET 20` | skip 20, then take 10 (page 3) |
| `ORDER BY created_at DESC LIMIT 1` | the single newest row |

## INSERT / UPDATE / DELETE

| SQL | English reading |
| --- | --- |
| `INSERT INTO users (email, name) VALUES ('mara@x.com', 'Mara')` | add one user with that email and name (other columns take their defaults) |
| `INSERT INTO users (email) VALUES ('a@x.com'), ('b@x.com')` | add two users at once |
| `UPDATE users SET name = 'Mara' WHERE id = 5` | change `name` to 'Mara', only in the matching row |
| `UPDATE users SET email_verified = true` | ✗ verify **every** user — no `WHERE`, no mercy |
| `DELETE FROM users WHERE id = 5` | remove the matching row |
| `DELETE FROM users` | ✗ empty the whole `users` table |
| `DELETE FROM users WHERE id = 5 RETURNING *` | delete it, and hand back the row you just removed |

## JOINs — combining two tables (multiple readings)

Example: `users` and their `documents` (`documents.owner_id` points at `users.id`; a user can own many documents, or none).

Every join answers one question: **a row found no match — does it survive?** The join word names who survives. A survivor with no partner gets `NULL` in the other table's columns.

| SQL | English reading |
| --- | --- |
| `users JOIN documents ON documents.owner_id = users.id` | only matched user–document pairs survive  
· "for each document, attach its owner"  
· no match → dropped, from either table |
| `users LEFT JOIN documents ON documents.owner_id = users.id` | **every user survives**; a user with no documents still appears, with `NULL` in the document columns  
· "all users, plus their docs where they exist" |
| `users RIGHT JOIN documents ON documents.owner_id = users.id` | **every document survives**; a document with no matching user gets `NULL` in the user columns  
· mirror of LEFT — same as `documents LEFT JOIN users` |
| `users FULL JOIN documents ON documents.owner_id = users.id` | **everybody survives**, from both tables; `NULL` wherever either side has no match |
| `users CROSS JOIN documents` | every user paired with every document — all combinations, no matching |
| `employees e JOIN employees m ON e.manager_id = m.id` | join a table to itself: each employee beside their manager |
| `users LEFT JOIN documents ON documents.owner_id = users.id WHERE documents.id IS NULL` | the users who own **no** documents — LEFT keeps them all, then keep only the ones whose document side came back `NULL` |

Memory hook: `ON` decides who matches · the join word decides who survives without a match · `NULL` fills the missing side.

Reminder: `OUTER` is an optional word — `LEFT JOIN` = `LEFT OUTER JOIN`.

## GROUP BY & aggregates (multiple examples)

`GROUP BY` collapses rows that share a value into one row per group; the aggregate describes each group.

| SQL | English reading |
| --- | --- |
| `GROUP BY team_id` | make one output row per distinct `team_id` |
| `SELECT team_id, COUNT(*) ... GROUP BY team_id` | how many rows in each team |
| `SELECT team_id, SUM(amount) ... GROUP BY team_id` | total `amount` per team |
| `SELECT team_id, AVG(score) ... GROUP BY team_id` | average `score` per team |
| `SELECT team_id, MAX(created_at) ... GROUP BY team_id` | the latest `created_at` per team |
| `COUNT(email)` | count rows where `email` isn't NULL (vs `COUNT(*)` = all rows) |
| `COUNT(DISTINCT user_id)` | how many **different** users |
| `GROUP BY team_id, role` | one row per (team, role) combination |
| `GROUP BY team_id HAVING COUNT(*) > 5` | keep only the **groups** with more than 5 rows (filter after grouping) |
| `WHERE ...` vs `HAVING ...` | `WHERE` filters rows **before** grouping; `HAVING` filters groups **after** aggregating |

## CREATE TABLE — column rules (constraints)

| SQL | English reading |
| --- | --- |
| `name text` | a `name` column holding any string |
| `age integer` | a whole number |
| `is_active boolean` | true / false |
| `id uuid` | a long random identifier |
| `created_at timestamptz` | a moment in time (with timezone) |
| `NOT NULL` | this cell can never be empty |
| `DEFAULT false` | if no value is given, use `false` |
| `DEFAULT now()` | if not given, stamp the current time |
| `PRIMARY KEY` | the row's unique name-tag — unique + not null + fast to find |
| `UNIQUE` | no two rows may share this value (multiple `NULL`s are still allowed) |
| `UNIQUE (team_id, user_id)` | no two rows may share this **pair** (each user only once per team) |
| `CHECK (age >= 0)` | reject any row where this isn't true |
| `team_id uuid REFERENCES teams(id)` | `team_id` must be a real `teams.id` — a foreign key |

## Foreign keys — ON DELETE (multiple readings)

A foreign key lives on the row that **points**. Example: a `team_members` row points at a `teams` row through `team_id`. `ON DELETE` decides what happens to **me, the pointing row,** when the row I point to is deleted.

| SQL | English reading |
| --- | --- |
| `team_members.team_id REFERENCES teams(id)` | a `team_members` row must point at a real `teams` row — never at nothing |
| `... ON DELETE CASCADE` | delete a team → also delete every `team_members` row that points at it  
· "when the row I point to is deleted, delete me too" |
| `teams.created_by_id REFERENCES users(id) ON DELETE SET NULL` | delete the creator → keep the team, set its `created_by_id` to `NULL`  
· "when the row I point to is deleted, keep me — just blank my pointer" |
| `... ON DELETE RESTRICT` | refuse to delete a `teams` row while any `team_members` row still points at it  
· "you can't delete what I still point to" |
| `... ON DELETE NO ACTION` | same effect as RESTRICT — block the delete (checked at the end of the statement) |

## Indexes

| SQL | English reading |
| --- | --- |
| `CREATE INDEX ON team_members (user_id)` | keep a lookup shortcut so "find by `user_id`" is fast (no full-table scan) |
| `CREATE UNIQUE INDEX ON team_members (team_id, user_id)` | a shortcut **and** a rule: `(team_id, user_id)` must be unique |
| `CREATE INDEX ON events (user_id, created_at)` | shortcut for looking up by `user_id`, or by `user_id` **then** `created_at` (column order matters) |

## Transactions

| SQL | English reading |
| --- | --- |
| `BEGIN;` | start a transaction — hold the next changes together |
| `COMMIT;` | make all of them land at once |
| `ROLLBACK;` | undo everything since `BEGIN` — as if none of it happened |
| `BEGIN; … COMMIT;` | do all of these as one indivisible unit: all, or nothing |

And now for the meat of the article. If you want a more in depth explanation, below is a blog post where I get into the details of it.

# How SQL works

SQL looks like a wall of shouting keywords.

```plaintext
SELECT ... FROM ... WHERE ... JOIN ... ON ... GROUP BY ... FOREIGN KEY ... CASCADE
```

The usual tutorial hands you that wall and starts defining the bricks. Which is not an explanation. It's a glossary.

So let me give you the one idea the whole thing rests on, and then everything above turns into plain sentences.

Here it is:

```plaintext
A database is a pile of grids.
SQL is how you talk to the grids.
```

That's it. That is the whole mental model. A grid is a table — rows and columns, like a spreadsheet with rules. SQL is the language for making grids, putting rows in them, asking questions about them, and connecting one grid to another.

Every keyword below is just a word in that conversation.

I'll use one running example the whole way: a small app with **users**, **teams**, and the memberships that connect them. Three people — Mara, Sam, Theo. One team — *Design crew*. Watch them move through every idea.

* * *

## A table is a grid

Picture the `users` table as a literal grid.

```plaintext
id       | email             | name  | email_verified
---------+-------------------+-------+---------------
u_mara   | mara@example.com  | Mara  | true
u_sam    | sam@example.com   | Sam   | false
u_theo   | theo@example.com  | Theo  | true
```

Columns are the headings: `id`, `email`, `name`, `email_verified`.

Rows are the entries: one per person.

A **cell** is where a row meets a column — Mara's email is one cell.

(Real ids are long random strings like `cc512230-de30-44c7-a197-fd6a88cb3f3c`. I'm writing `u_mara` so the examples stay readable. More on why ids look like that later.)

That's the whole shape of a database. Grids of rows. Everything else is talking to them.

* * *

## Making a grid: CREATE TABLE

Before a grid can hold rows, you declare its columns. That's `CREATE TABLE`.

```sql
CREATE TABLE users (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email          text NOT NULL,
  name           text,
  email_verified boolean NOT NULL DEFAULT false,
  created_at     timestamp with time zone NOT NULL DEFAULT now()
);
```

Read it as a sentence: "make a grid called `users`, with these columns."

Each line inside the parentheses is one column, and it has three parts:

```plaintext
email          text          NOT NULL
^ the name     ^ the type    ^ the rules
```

The **name** is the heading. The **type** is what kind of value the cell may hold. The **rules** are promises the database will enforce.

Let me name the types you'll actually use:

*   `text` — a string of any length. `'mara@example.com'`.
    
*   `boolean` — `true` or `false`.
    
*   `integer` — a whole number. `42`.
    
*   `timestamp with time zone` — a moment in time that knows its timezone.
    
*   `uuid` — a long random identifier.
    

And the rules:

*   `NOT NULL` — this cell can never be empty. (`NULL` is SQL's word for "no value at all." `NOT NULL` forbids it. More on `NULL` soon — it's sneakier than it looks.)
    
*   `DEFAULT <value>` — if nobody supplies this cell, fill it with this. `email_verified` defaults to `false`; `created_at` defaults to `now()` (the current time, stamped automatically).
    
*   `PRIMARY KEY` — this column is the row's unique name-tag. No two rows may share one, and it's the fast way to find a single row.
    

One line that trips people up:

```sql
id uuid PRIMARY KEY DEFAULT gen_random_uuid()
```

`uuid` is the **type** — what the cell holds. `gen_random_uuid()` is a **function** — it produces a fresh random id each time it runs. So the column *holds* a uuid, and its *default* is "call this function to make one." That's why we never invent ids by hand — insert a row, and the database mints its `id`. Random uuids almost never collide, so two servers can both create rows and never clash. That's the whole reason to prefer them over a counter like `1, 2, 3`.

* * *

## Putting a row in: INSERT

The grid exists but it's empty. `INSERT` adds a row.

```sql
INSERT INTO users (email, name) VALUES ('mara@example.com', 'Mara');
```

"Into the `users` grid, in the `email` and `name` columns, put these values."

Notice what I *didn't* write: no `id`, no `email_verified`, no `created_at`. I left them out on purpose, and the defaults filled them in — a fresh uuid, `false`, and the current time. The row that lands is complete:

```plaintext
id       | email             | name  | email_verified | created_at
---------+-------------------+-------+----------------+---------------------
u_mara   | mara@example.com  | Mara  | false          | 2026-07-08 09:14:...
```

You can insert several rows at once:

```sql
INSERT INTO users (email, name) VALUES
  ('sam@example.com',  'Sam'),
  ('theo@example.com', 'Theo');
```

Now the grid has three rows. Let's ask for them back.

* * *

## Asking for it back: SELECT ... FROM ... WHERE

This is the sentence you'll write more than any other. It has three parts, and each answers one question.

```sql
SELECT email, name          -- which COLUMNS do I want?
FROM users                  -- from which GRID?
WHERE email_verified = true -- which ROWS?
```

Read it top to bottom:

```plaintext
SELECT   →  which columns
FROM     →  which grid
WHERE    →  which rows
```

`SELECT *` means "every column" (the `*` is "all"). `SELECT email, name` means just those two.

`FROM users` picks the grid.

`WHERE email_verified = true` is the filter. Only rows where that's true come back.

So that query returns Mara and Theo (verified), not Sam (not verified):

```plaintext
email             | name
------------------+------
mara@example.com  | Mara
theo@example.com  | Theo
```

Change the question by changing the `WHERE`. Want just Sam?

```sql
SELECT * FROM users WHERE email = 'sam@example.com';
```

The database walks the grid, keeps the rows the `WHERE` approves of, and hands back the columns the `SELECT` asked for. That loop — filter rows, pick columns — is 80% of SQL.

* * *

## WHERE is where the thinking happens

The `WHERE` clause is a yes/no test run against every row. The row stays if the test is true.

You have the comparisons you'd expect:

```sql
WHERE created_at > '2026-01-01'          -- after a date
WHERE name = 'Mara'                      -- exactly equal
WHERE name != 'Mara'                     -- not equal
```

And you can combine tests with `AND` and `OR`:

```sql
SELECT * FROM users
WHERE email_verified = true
  AND created_at > '2026-01-01';
```

"Verified **and** created this year." A row must pass both.

`OR` means either is enough:

```sql
WHERE name = 'Mara' OR name = 'Sam';
```

Now, the sneaky one. `NULL` — the "no value at all" from earlier — does not behave like a value.

Here's the trap:

```sql
-- ✗ this returns NOTHING, even for rows where name really is empty
SELECT * FROM users WHERE name = NULL;
```

You'd expect it to find the rows with no name. It finds none.

Why? Because `NULL` means *unknown*, and "is this unknown thing equal to unknown?" isn't `true` — it's itself unknown. So the row fails the test. `= NULL` can never be true for anyone.

The fix is a special operator that asks the question directly:

```sql
-- ✓ the right way to ask "is this cell empty?"
SELECT * FROM users WHERE name IS NULL;
```

`IS NULL` and `IS NOT NULL` are how you test for emptiness. Reach for `=` and you'll silently get nothing back. This bites everyone once.

* * *

## Sorting and limiting: ORDER BY, LIMIT

Rows come back in no guaranteed order unless you ask for one. `ORDER BY` sorts them.

```sql
SELECT name, created_at FROM users
ORDER BY created_at DESC;
```

`DESC` = descending, newest first. `ASC` = ascending, oldest first (and it's the default).

`LIMIT` caps how many rows come back:

```sql
SELECT name FROM users
ORDER BY created_at DESC
LIMIT 1;
```

"The single most recently created user." Sort newest-first, then take one.

That pairing — `ORDER BY` then `LIMIT` — is how you get "the latest," "the top 10," "the most recent 5."

* * *

## Changing a row: UPDATE (and the WHERE you must never forget)

`UPDATE` changes cells in rows that already exist.

```sql
UPDATE users
SET email_verified = true
WHERE email = 'sam@example.com';
```

"In the `users` grid, set `email_verified` to true, **for the row where** email is Sam's."

The `SET` says what to change. The `WHERE` says *which rows* — and it is the most important word in the statement.

Here's the mistake that has ruined real production databases:

```sql
-- ✗ NO WHERE — this verifies EVERY user in the table
UPDATE users SET email_verified = true;
```

No `WHERE` means "every row." You meant to update Sam. You just marked all three million users as verified, in one keystroke, with no undo.

The rule burns itself into you fast: **an UPDATE without a WHERE hits everything.** Write the `WHERE` first.

* * *

## Removing a row: DELETE

`DELETE` throws rows away. Same lesson, sharper.

```sql
DELETE FROM users WHERE email = 'theo@example.com';
```

"Remove the row where email is Theo's." Theo is gone.

And the same landmine:

```sql
-- ✗ NO WHERE — this empties the entire table
DELETE FROM users;
```

No `WHERE`, no survivors. Every row, gone.

So for both `UPDATE` and `DELETE`, the `WHERE` is not optional decoration. It's the difference between "change one thing" and "change everything." A good habit: write the `WHERE` before you write the `SET` or the `DELETE`, so the target exists before the action does.

* * *

## Rows that point at other rows: the foreign key

So far, one grid at a time. But real data connects.

A membership connects a user to a team. So there's a third grid, `team_members`, that sits between `users` and `teams`. Each row says "this user is in this team, with this role":

```sql
CREATE TABLE team_members (
  id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id uuid NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role    text NOT NULL DEFAULT 'member'
);
```

Look at `team_id`. On its own it's just a uuid sitting in a cell. Nothing yet forces it to match a *real* team. The `REFERENCES teams(id)` is what forces it. That is a **foreign key**.

Read it in plain English:

```plaintext
the team_members.team_id column
must always REFERENCE a real teams.id
— you can't have a membership pointing at a team that doesn't exist.
```

That's the whole idea of a foreign key: a column whose value must exist as a real row in another grid. Try to insert a membership for a team id that isn't there, and the database refuses. It's the rule that keeps the grids honest with each other — no memberships floating in space, pointing at nothing.

A foreign key is what turns a pile of separate grids into a connected web.

* * *

## When the row I point at is deleted, what happens to me?

A foreign key raises a question the moment you try to delete something.

Say you delete the *Design crew* team. What should happen to the membership rows that point at it? They can't keep pointing at a team that's gone — that's the exact thing the foreign key forbids.

So every foreign key must answer one question:

```plaintext
When the row I point at is deleted, what happens to me?
```

You answer it with `ON DELETE`. There are two answers you'll reach for.

`ON DELETE CASCADE` **— "delete me too."**

```sql
team_id uuid NOT NULL REFERENCES teams(id) ON DELETE CASCADE
```

Delete a team, and all its membership rows vanish with it. Delete a user, and all *their* membership rows vanish. No orphaned memberships pointing at a deleted team or a deleted user. Clean. The deletion *cascades* — it flows down the chain from the parent to the rows that depend on it.

`ON DELETE SET NULL` **— "don't delete me, just blank the pointer."**

Here's a different case. A team remembers who created it:

```sql
CREATE TABLE teams (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name          text NOT NULL,
  created_by_id uuid REFERENCES users(id) ON DELETE SET NULL
);
```

Now delete the user who created *Design crew*.

You do **not** want the team to vanish — other people are still in it. So `ON DELETE SET NULL` says: keep the team, just set `created_by_id` to `NULL`. The pointer goes blank; the row lives on. The team outlives its creator.

That single word — `CASCADE` versus `SET NULL` — is the entire difference between "the child dies with the parent" and "the child survives, forgetting the parent." It's worth pausing on, because getting it wrong means either orphaned rows or accidental mass-deletion.

```plaintext
parent row deleted
↓
CASCADE   → the rows pointing at it are deleted too
SET NULL  → the rows survive, their pointer set to NULL
```

(You may notice `created_by_id` has no `NOT NULL`. It can't — `SET NULL` needs to be *allowed* to write a blank there. The two rules go together.)

* * *

## Two grids, one question: JOIN

Now the payoff. Data lives in separate grids, but questions cross them.

"Which teams is Sam in?" The answer needs `team_members` (who's in what) *and* `teams` (the team's name). One grid can't answer it alone.

A `JOIN` stitches two grids together on a matching column.

```sql
SELECT teams.name, team_members.role
FROM team_members
JOIN teams ON team_members.team_id = teams.id
WHERE team_members.user_id = 'u_sam';
```

Read the `JOIN ... ON` as: "glue each `team_members` row to the `teams` row where their ids match." The `ON` is the matching rule — `team_members.team_id = teams.id`.

For each membership row, the database finds the team it points at and lays the two rows side by side, into one wider row:

```plaintext
teams.name    | team_members.role
--------------+------------------
Design crew   | member
```

Sam's membership pointed at *Design crew*; the join pulled in that team's `name`. Two grids, one combined answer.

You qualify columns with their grid — `teams.name`, `team_members.role` — because once two grids are joined, a bare `name` could be ambiguous. Say which grid you mean.

* * *

## The many-to-many, and why it needs a table in the middle

Why does `team_members` exist at all? Why not just... put the members on the team?

Because a user can be in **many** teams, and a team has **many** members. That's a *many-to-many* relationship, and neither grid can hold it alone. A `teams` row can't list an unbounded number of members in one cell. A `users` row can't list an unbounded number of teams.

So the relationship gets its own grid. Each row is one "this user is in this team" fact. That middle grid is a **join table**, and you read it both directions:

```plaintext
"Which teams is Sam in?"    → team_members rows where user_id = Sam
"Who is in Design crew?"    → team_members rows where team_id = Design crew
```

One grid, both questions. This pattern — two things that relate many-to-many, joined by a table in the middle — is everywhere once you see it. Students and classes. Orders and products. Users and teams.

* * *

## INNER JOIN vs LEFT JOIN — the difference is who gets dropped

There's a fork in `JOIN` that matters, and the names hide it. Let me show the bug first.

You want a roster: every user, and their role if they're on a team.

```sql
-- looks right...
SELECT users.name, team_members.role
FROM users
JOIN team_members ON team_members.user_id = users.id;
```

Run it and Theo is **missing**.

```plaintext
name  | role
------+--------
Mara  | owner
Sam   | member
```

Theo is in no team. A plain `JOIN` — an **inner** join — only keeps rows where *both* sides match. Theo has no `team_members` row, so he has nothing to match, so he falls out entirely. The join silently dropped him.

Sometimes that's what you want. Here it isn't — you wanted *every* user.

The fix is `LEFT JOIN`. It keeps every row from the left grid (`users`), matched or not. Where there's no match, it fills the right side with `NULL`:

```sql
SELECT users.name, team_members.role
FROM users
LEFT JOIN team_members ON team_members.user_id = users.id;
```

```plaintext
name  | role
------+--------
Mara  | owner
Sam   | member
Theo  | NULL     ← kept, with a blank role
```

Now Theo is there, his role `NULL` because he has none.

That's the whole distinction:

```plaintext
INNER JOIN  → keep only rows that matched on both sides
LEFT JOIN   → keep ALL left rows; NULL-fill the right where nothing matched
```

"Where did that row go?" in a report is, nine times out of ten, an inner join that should have been a left join.

* * *

## The other two joins: RIGHT and FULL

INNER and LEFT are the two you'll write almost every day. But there are four in total — so let me close the set, and clear up a word while I'm at it.

**"OUTER" is noise.** You'll see `LEFT OUTER JOIN`, `RIGHT OUTER JOIN`, `FULL OUTER JOIN`. The `OUTER` adds nothing — `LEFT JOIN` and `LEFT OUTER JOIN` are the exact same thing. So "right join" and "outer join" aren't two separate answers; `OUTER` is just the formal middle name of joins you already have. Four joins, one word you can ignore.

Picture two overlapping circles — `users` on the left, `team_members` on the right. The overlap is the rows that match on the `ON` condition. Each join keeps a different region:

```plaintext
INNER JOIN  → just the overlap             (only rows matched on both sides)
LEFT JOIN   → whole LEFT circle + overlap   (all users; NULL where no membership)
RIGHT JOIN  → whole RIGHT circle + overlap  (all memberships; NULL where no user)
FULL JOIN   → both circles, whole           (everything; NULL wherever either side is missing)
```

`RIGHT JOIN` is the mirror of `LEFT`: keep every row from the *right* grid, NULL-fill the left. `FULL JOIN` keeps everything from *both* — a user with no membership comes back with a NULL role, and a membership with no matching user comes back with a NULL name.

Now the honest part, and it's the lesson worth keeping. On *this* pair of grids, RIGHT and FULL are secretly the same as joins you've already seen — and the foreign key is why.

Remember: `team_members.user_id` REFERENCES `users.id`. A membership **can never** point at a user who doesn't exist — the database forbids it. So the part of the right circle sticking out past the overlap — memberships with no user — is always empty.

Watch what that does. `RIGHT JOIN` keeps all memberships:

```sql
SELECT users.name, team_members.role
FROM users
RIGHT JOIN team_members ON team_members.user_id = users.id;
```

But every membership already has a real user, so nothing gets NULL-filled — you get exactly the matched rows. **RIGHT JOIN here collapses to INNER JOIN.** Same two rows, Mara and Sam:

```plaintext
name | role
-----+--------
Sam  | member
Mara | owner
```

And `FULL JOIN` here collapses to `LEFT JOIN`. The only unmatched rows that exist are users without a membership (Theo, and a nameless account), so FULL adds nothing beyond what LEFT already kept — the same four rows, Theo and the nameless one with a NULL role.

That's the real reason I reached for LEFT and skipped RIGHT earlier. On this schema the gap only runs one way:

```plaintext
a user can have no team          → LEFT JOIN surfaces it
a membership can't have no user  → the foreign key already made that impossible
```

LEFT shows the gap that can actually happen. RIGHT would go looking for a gap the foreign key has ruled out.

RIGHT and FULL earn their keep when *both* sides can have unmatched rows — two independent lists you're reconciling, wanting to see what each has that the other is missing. With a foreign key in play, that's usually not your situation, so you'll live in INNER and LEFT.

And a practical note: almost nobody writes `RIGHT JOIN` anyway. `A RIGHT JOIN B` is just `B LEFT JOIN A` with the grids swapped, and since we read left-to-right, people flip it so the "keep them all" grid comes first. So the family quietly narrows back down to the two you started with.

(There's also `CROSS JOIN` — every left row paired with every right row, no `ON` at all. It builds combinations rather than matching rows, a different job entirely; you'll rarely reach for it.)

The one model that covers all of them:

```plaintext
a JOIN matches rows across two grids;
the word in front only decides which UNMATCHED rows survive —
left, right, both, or neither.
```

* * *

## Counting: GROUP BY

One more kind of question: not "which rows" but "how many," per something.

"How many members does each team have?"

You don't want the rows themselves — you want a *count per team*. That's `GROUP BY`. It collapses rows that share a value into one group, and lets you count (or sum, or average) each group.

```sql
SELECT team_id, COUNT(*) AS member_count
FROM team_members
GROUP BY team_id;
```

`GROUP BY team_id` gathers all the rows with the same `team_id` into one bucket. `COUNT(*)` counts the rows in each bucket. `AS member_count` just names the output column.

```plaintext
team_id       | member_count
--------------+-------------
t_design_crew | 2
```

`COUNT(*)` is the common one, but the same shape works with `SUM(...)`, `AVG(...)`, `MAX(...)`. The mental model: `GROUP BY` **turns many rows into one row per group, and the count/sum describes each group.**

* * *

## Making it fast: the index

Everything so far is about *correctness*. This one is about *speed*.

Ask "which teams is Sam in?" and, by default, the database reads **every single row** in `team_members` and checks each one's `user_id`. Three rows, fine. Three million rows, slow — every time.

An **index** fixes that. It's a lookup shortcut the database maintains on the side:

```sql
CREATE INDEX team_members_user_idx ON team_members (user_id);
```

Now "find Sam's memberships" jumps straight to his rows instead of scanning the whole grid — the same way the index at the back of a book beats flipping every page.

An index costs a little: it takes space, and it must be updated on every write. So you don't index every column — you index the ones you *look things up by*.

There's a second kind that does double duty:

```sql
CREATE UNIQUE INDEX team_members_team_user ON team_members (team_id, user_id);
```

A `UNIQUE` index is a shortcut **and a rule**: the pair `(team_id, user_id)` must be unique across the whole grid. Meaning the same user can't be in the same team twice. If two "add Sam to Design crew" requests arrive at the same instant, the database lets the first win and rejects the second — the uniqueness is enforced by the database, not by fragile "check, then insert" code that two requests can both slip through.

* * *

## All-or-nothing: the transaction

Last idea, and it's the one that separates toy SQL from real SQL.

Creating a team is really *two* writes: insert the team, then insert the membership that makes the creator its owner. Both have to happen, or the team is born broken — a team with no owner that nobody can manage.

Here's the danger, written the naive way:

```sql
INSERT INTO teams (name, created_by_id) VALUES ('Design crew', 'u_mara');
-- ... what if the process crashes RIGHT HERE? ...
INSERT INTO team_members (team_id, user_id, role) VALUES ('t_design_crew', 'u_mara', 'owner');
```

If the process dies between the two lines, the first row committed and the second never ran. Now there's a team with no members. A ghost.

A **transaction** fixes this. You wrap the statements so the database treats them as one indivisible unit — **all of them commit, or none of them do.**

```sql
BEGIN;
  INSERT INTO teams (name, created_by_id) VALUES ('Design crew', 'u_mara');
  INSERT INTO team_members (team_id, user_id, role) VALUES ('t_design_crew', 'u_mara', 'owner');
COMMIT;
```

`BEGIN` opens the transaction. `COMMIT` seals it — both rows land at the same instant. If anything between them fails, you `ROLLBACK` (or the database does it for you), and it's as if *neither* statement ever ran. The team can never exist without its owner.

```plaintext
BEGIN
↓
insert the team
insert the owner membership
↓
COMMIT  → both land together
   or
ROLLBACK → neither happened, no ghost
```

That property — several changes that must be true *together* — is what transactions are for. Any time "do X" really means "do X and Y, and half of it would be a mess," wrap them.

* * *

## What SQL is not, and when to reach for something else

Two honest notes to end on.

SQL is not a general programming language you write loops in. It's *declarative*: you describe the rows you want, and the database figures out how to get them. You don't tell it "scan this grid, check each row" — you say `WHERE email_verified = true`, and *it* decides whether to use an index or scan. That flip — describe the result, don't script the steps — is the mental adjustment that makes SQL click.

And it's not always the right tool. If your data is a bag of loosely-shaped documents with no relationships — logs, a cache, a blob of JSON you always read whole — a relational database's grids and joins are overhead you don't need. SQL earns its keep exactly when your data *has* structure and *has* relationships: users who belong to teams that own documents, where a question crosses all three and the answer must stay consistent. That's the case these grids were built for.

The whole thing in three beats:

```plaintext
A table is a grid, and SELECT ... WHERE is how you ask it questions.
A foreign key connects two grids, and ON DELETE decides what a deletion drags with it.
A transaction makes several writes land together, so your grids are never caught half-changed.
```