Quick SQL reference covering SELECT queries, filtering, joins, aggregation, data manipulation, and schema operations. Useful for writing queries across MySQL, PostgreSQL, SQLite, and other databases.
| Code / Syntax | Description |
|---|---|
SELECT * FROM users; | Select all columns from a table |
SELECT name, email FROM users; | Select specific columns |
SELECT DISTINCT city FROM users; | Select unique values |
SELECT COUNT(*) FROM users; | Count total rows |
SELECT name AS full_name FROM users; | Alias a column name |
SELECT *, ROWNUM FROM users; | Select with row number |
SELECT CONCAT(first, ' ', last) AS name FROM users; | Concatenate columns |
SELECT COALESCE(nickname, name) FROM users; | First non-null value |
SELECT CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END FROM users; | Conditional column value |
| Code / Syntax | Description |
|---|---|
WHERE age > 21 | Filter with comparison operator |
WHERE name LIKE 'J%' | Pattern match (starts with J) |
WHERE age BETWEEN 18 AND 65 | Range filter (inclusive) |
WHERE city IN ('NYC', 'LA', 'SF') | Match any value in list |
WHERE email IS NOT NULL | Filter out null values |
WHERE age > 21 AND city = 'NYC' | Multiple conditions with AND |
ORDER BY created_at DESC | Sort descending |
ORDER BY last_name ASC, first_name ASC | Sort by multiple columns |
LIMIT 10 OFFSET 20 | Paginate results |
WHERE name LIKE '%son' | Pattern match (ends with son) |
| Code / Syntax | Description |
|---|---|
SELECT * FROM a INNER JOIN b ON a.id = b.a_id; | Inner join (matching rows only) |
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id; | Left join (all from left + matches) |
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id; | Right join (all from right + matches) |
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id; | Full outer join (all rows from both) |
SELECT * FROM a CROSS JOIN b; | Cross join (Cartesian product) |
SELECT * FROM a JOIN b USING (id); | Join using shared column name |
SELECT * FROM a NATURAL JOIN b; | Join on all matching column names |
SELECT e.name, m.name FROM emp e JOIN emp m ON e.mgr_id = m.id; | Self join |
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.id IS NULL; | Anti join (rows in A not in B) |
| Code / Syntax | Description |
|---|---|
SELECT city, COUNT(*) FROM users GROUP BY city; | Count per group |
SELECT dept, AVG(salary) FROM emp GROUP BY dept; | Average per group |
SELECT dept, SUM(salary) FROM emp GROUP BY dept; | Sum per group |
SELECT dept, MIN(salary), MAX(salary) FROM emp GROUP BY dept; | Min/max per group |
GROUP BY city HAVING COUNT(*) > 5 | Filter groups with HAVING |
SELECT city, COUNT(*) AS cnt FROM users GROUP BY city ORDER BY cnt DESC; | Sort groups by aggregate |
SELECT YEAR(created_at), COUNT(*) FROM orders GROUP BY YEAR(created_at); | Group by date part |
SELECT dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) | Window function ranking |
SELECT name, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales; | Running total with window function |
| Code / Syntax | Description |
|---|---|
INSERT INTO users (name, email) VALUES ('Jo', 'jo@x.com'); | Insert a single row |
INSERT INTO archive SELECT * FROM users WHERE active = 0; | Insert from a SELECT query |
UPDATE users SET email = 'new@x.com' WHERE id = 1; | Update specific rows |
DELETE FROM users WHERE id = 1; | Delete specific rows |
TRUNCATE TABLE logs; | Delete all rows (fast, no rollback) |
UPSERT / ON CONFLICT DO UPDATE | Insert or update if exists |
BEGIN; ... COMMIT; | Transaction block |
ROLLBACK; | Undo current transaction |
INSERT INTO users (name) VALUES ('A'), ('B'), ('C'); | Multi-row insert |
| Code / Syntax | Description |
|---|---|
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100)); | Create a table |
ALTER TABLE users ADD COLUMN age INT; | Add a column |
ALTER TABLE users DROP COLUMN age; | Remove a column |
ALTER TABLE users RENAME COLUMN name TO full_name; | Rename a column |
DROP TABLE users; | Delete a table |
CREATE INDEX idx_email ON users(email); | Create an index |
DROP INDEX idx_email; | Delete an index |
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id); | Add foreign key constraint |
CREATE VIEW active_users AS SELECT * FROM users WHERE active = 1; | Create a view |
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email); | Add unique constraint |
Found this cheat sheet useful? Check out our other references and tools.