Cheat sheetDev
SQL syntax
The SQL clauses you look up constantly — reading, filtering, aggregating, joining, and writing, with the UPDATE warning spelled out.
Last updated
Reading
| Syntax | What it does |
|---|---|
| SELECT name, email FROM users | Pulls just the columns you name — SELECT * grabs everything, which is slower and messier to read. |
| SELECT * FROM users WHERE id = 5 | Filters rows down to the ones matching a condition, before anything else happens. |
| SELECT * FROM users ORDER BY created_at DESC | Sorts the results — DESC for newest first, ASC (the default) for oldest first. |
| SELECT * FROM users LIMIT 10 | Caps how many rows come back — essential on a big table before you know what you're looking at. |
| SELECT DISTINCT country FROM users | Returns each value once — the fast way to see what values actually exist in a column. |
Filtering
| Syntax | What it does |
|---|---|
| WHERE name LIKE 'A%' | Pattern-matches text — % is a wildcard for any characters, so this means 'starts with A'. |
| WHERE status IN ('active', 'pending') | Matches any value in a list — shorter than chaining OR for each one. |
| WHERE price BETWEEN 10 AND 50 | Matches an inclusive range — both 10 and 50 count as matches. |
| WHERE deleted_at IS NULL | Finds rows with no value set — = NULL never works, this is the only way to check. |
| WHERE age > 18 AND country = 'US' | Combines conditions — AND narrows results, OR widens them; parentheses matter once you mix both. |
Aggregating
| Syntax | What it does |
|---|---|
| SELECT COUNT(*) FROM orders | Counts rows — add a WHERE first to count only the ones that match. |
| SELECT SUM(total) FROM orders | Adds up a column across every matching row — total revenue in one line. |
| SELECT AVG(total) FROM orders | The mean value across matching rows. |
| SELECT MIN(price), MAX(price) FROM products | The smallest and largest values in a column. |
| SELECT status, COUNT(*) FROM orders GROUP BY status | Buckets rows by a column and runs the aggregate per bucket, not across the whole table. |
| SELECT status, COUNT(*) FROM orders GROUP BY status HAVING COUNT(*) > 10 | Filters groups after aggregating — WHERE can't do this because it runs before grouping happens. |
Joining
| Syntax | What it does |
|---|---|
| SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id | Only rows that match in both tables — no match on either side, no row in the result. |
| SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id | Every row from the left table, matched data from the right if it exists — NULLs where it doesn't. |
| SELECT * FROM orders RIGHT JOIN users ON orders.user_id = users.id | The mirror of LEFT JOIN — every row from the right table this time. |
| SELECT * FROM a FULL JOIN b ON a.id = b.id | Everything from both tables, matched where possible — unmatched rows from either side show as NULLs. |
| ON a.id = b.id | The condition that decides which rows pair up — get this wrong and you get duplicate or missing rows. |
| SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id | Joins a table to itself — the standard way to resolve a 'manager is also an employee' relationship. |
Writing
| Syntax | What it does |
|---|---|
| INSERT INTO users (name, email) VALUES ('Ana', 'a@x.com') | Adds a new row — list columns explicitly so it still works after the table's shape changes. |
| UPDATE users SET email = 'new@x.com' WHERE id = 5 | Changes existing rows — leave off the WHERE and it rewrites every row in the table. |
| UPDATE users SET active = true | No WHERE means every single row gets this change — run the matching SELECT first to check. |
| DELETE FROM users WHERE id = 5 | Removes rows — same rule as UPDATE: no WHERE deletes the whole table. |
| CREATE TABLE users (id INT PRIMARY KEY, name TEXT) | Defines a table's columns and types — PRIMARY KEY marks the column that uniquely identifies each row. |
Worth remembering
- →WHERE runs before GROUP BY, and GROUP BY runs before HAVING — that order is why WHERE can't filter on an aggregate.
- →Run any UPDATE or DELETE as a SELECT with the same WHERE first — if that returns the wrong rows, so would the write.