SQL cheat sheet
Standard SQL that works in PostgreSQL, MySQL, SQLite and SQL Server, with notes where they differ.
Read data
SELECT col1, col2 FROM t;- Chosen columns
SELECT * FROM t WHERE col = 'x';- Filter rows
SELECT DISTINCT col FROM t;- Unique values
SELECT * FROM t ORDER BY col DESC;- Sort descending
SELECT * FROM t LIMIT 10;- First 10 rows (SQL Server uses TOP 10)
Filters
col IN ('a', 'b')- Matches any listed value
col BETWEEN 10 AND 20- Inclusive range
col LIKE 'A%'- Starts with A
col IS NULL- Missing value (= NULL never matches)
a = 1 AND (b = 2 OR c = 3)- Combine conditions with parentheses
Aggregate
COUNT(*)- Number of rows
SUM(col), AVG(col)- Total and average
MIN(col), MAX(col)- Smallest and largest
GROUP BY col- One row per distinct value
HAVING COUNT(*) > 1- Filter groups after aggregation
Joins
FROM a INNER JOIN b ON b.a_id = a.id- Only rows that match in both
FROM a LEFT JOIN b ON b.a_id = a.id- All rows of a, NULL where b has no match
FROM a CROSS JOIN b- Every combination (rarely wanted)
Change data
INSERT INTO t (a, b) VALUES (1, 'x');- Add a row
UPDATE t SET a = 2 WHERE id = 1;- Change rows (always use WHERE)
DELETE FROM t WHERE id = 1;- Remove rows (always use WHERE)
BEGIN; ... COMMIT;- Group statements in a transaction (ROLLBACK to cancel)
Tables
CREATE TABLE t (id INT PRIMARY KEY, name TEXT NOT NULL);- Create a table
ALTER TABLE t ADD COLUMN age INT;- Add a column
CREATE INDEX idx_name ON t (name);- Speed up lookups on a column
DROP TABLE t;- Delete a table and its data
EXPLAIN SELECT ...;- Show the query plan
Learn it properly