SELECT * FROM users;Select all columns from a table.Queries, joins, filtering, and aggregation — the syntax that's easy to blank on when you haven't touched SQL in a few weeks.
SELECT * FROM users;Select all columns from a table.SELECT name, email FROM users WHERE active = true;Select specific columns with a filter condition.ORDER BY created_at DESCSort results, newest first.LIMIT 10 OFFSET 20Return 10 rows, skipping the first 20 (pagination).WHERE name LIKE '%smith%'Match rows where a column contains a substring.WHERE status IN ('active', 'pending')Match any value in a list.SELECT DISTINCT country FROM users;Return unique values, removing duplicates.WHERE age BETWEEN 18 AND 65Match values within an inclusive range.WHERE email IS NULLMatch rows where a column has no value.WHERE email IS NOT NULLMatch rows where a column has a value.SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)Match rows that have at least one related row in another table.UNION / UNION ALLCombine results of two queries; UNION removes duplicates, UNION ALL keeps them.INNER JOIN orders ON users.id = orders.user_idOnly rows with matches in both tables.LEFT JOIN orders ON users.id = orders.user_idAll rows from the left table, NULLs where there's no match.RIGHT JOINAll rows from the right table, NULLs where there's no match on the left.FULL OUTER JOINAll rows from both tables, matched where possible.SELF JOIN (alias the same table twice)Join a table to itself, e.g. to compare rows within one table.CROSS JOINEvery row from one table paired with every row from another (Cartesian product).COUNT(*)Count the number of rows.SUM(amount) / AVG(amount)Sum or average a numeric column.GROUP BY countryGroup rows sharing a value, usually paired with an aggregate function.HAVING COUNT(*) > 5Filter groups after aggregation (WHERE can't reference aggregates).MAX(price) / MIN(price)Highest or lowest value in a column.COUNT(DISTINCT country)Count unique values in a column.ROUND(AVG(price), 2)Round an aggregate result to a fixed number of decimal places.INSERT INTO users (name, email) VALUES ('A', 'a@x.com');Insert a new row.UPDATE users SET active = false WHERE id = 5;Update rows matching a condition. Never omit WHERE unless you mean every row.DELETE FROM users WHERE id = 5;Delete rows matching a condition.UPSERT / ON CONFLICT DO UPDATEInsert a row, or update it if a conflicting key already exists (syntax varies by database).BEGIN; ... COMMIT;Wrap statements in a transaction so they all succeed or all roll back together.ROLLBACK;Undo all changes made in the current open transaction.TRUNCATE TABLE users;Instantly remove all rows from a table (faster than DELETE, can't be filtered).CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);Create a new table with columns and types.ALTER TABLE users ADD COLUMN age INT;Add a new column to an existing table.CREATE INDEX idx_users_email ON users(email);Speed up lookups/filters/sorts on a column.DROP TABLE users;Permanently delete a table and all its data.ALTER TABLE users DROP COLUMN age;Remove a column from a table.ALTER TABLE users RENAME TO customers;Rename a table.FOREIGN KEY (user_id) REFERENCES users(id)Enforce that a column's values must exist in another table's column.INNER JOIN only returns rows that have matching values in both tables. LEFT JOIN returns all rows from the left table, with NULLs filled in for columns from the right table when there's no match.
WHERE filters rows before aggregation happens, so it can't reference aggregate functions like COUNT() or SUM(). Use HAVING instead, which filters groups after aggregation.
Add an index to columns you frequently filter, join, or sort by, especially in large tables. Avoid over-indexing, since every index speeds up reads but slows down writes and uses extra storage.