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@example.com') | Adds a new row — list columns explicitly so it still works after the table's shape changes. |
| UPDATE users SET email = 'new@example.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.
Questions
In what order does SQL actually run?
FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. It explains why you cannot reference a SELECT alias in WHERE but can in ORDER BY, which otherwise looks arbitrary.
Why does NULL break my comparison?
Because NULL means unknown, so every comparison with it is unknown rather than true or false. Use IS NULL and IS NOT NULL, and remember that NOT IN against a set containing NULL returns nothing at all.
When do I need HAVING rather than WHERE?
Whenever the condition mentions an aggregate. WHERE filters rows before grouping, when COUNT and SUM do not yet exist; HAVING filters the groups afterwards.
Is SELECT * acceptable?
For exploring, yes. In application code it breaks quietly when a column is added or reordered, and it transfers columns nobody reads — both of which argue for naming what you want.
Why is my LEFT JOIN returning fewer rows than expected?
Almost always a condition on the right table in the WHERE clause, which filters out the unmatched rows the join deliberately kept. Move it into the ON clause.