Everyday SQL syntax and what they do. Download: CSV · JSON. Always test UPDATE/DELETE with a WHERE clause first.
| Command | What it does |
|---|---|
SELECT * FROM t; | Return all columns and rows |
SELECT a, b FROM t WHERE x > 5; | Filter rows with a condition |
SELECT DISTINCT a FROM t; | Unique values of a column |
ORDER BY a DESC | Sort results descending |
LIMIT 10 OFFSET 20 | Paginate results |
SELECT COUNT(*) FROM t; | Count rows |
SELECT a, COUNT(*) FROM t GROUP BY a; | Count rows per group |
GROUP BY a HAVING COUNT(*) > 1 | Filter groups (finds duplicates) |
INNER JOIN u ON t.id = u.t_id | Rows with matches in both tables |
LEFT JOIN u ON t.id = u.t_id | All left rows, matched right rows or NULL |
WHERE a IN (1,2,3) | Match any value in a list |
WHERE a BETWEEN 1 AND 10 | Inclusive range filter |
WHERE name LIKE 'A%' | Pattern match (% any chars, _ one char) |
WHERE a IS NULL | Test for NULL (never use = NULL) |
COALESCE(a, 0) | First non-NULL value |
CASE WHEN a > 0 THEN 'pos' ELSE 'neg' END | Conditional expression |
INSERT INTO t (a,b) VALUES (1,'x'); | Insert a row |
UPDATE t SET a = 1 WHERE id = 5; | Update rows (always include WHERE) |
DELETE FROM t WHERE id = 5; | Delete rows (always include WHERE) |
CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT); | Create a table |
ALTER TABLE t ADD COLUMN c TEXT; | Add a column |
DROP TABLE t; | Delete a table (destructive) |
CREATE INDEX idx_t_a ON t(a); | Speed up lookups on a column |
WITH x AS (SELECT ...) SELECT * FROM x; | Common table expression |
SELECT ... UNION SELECT ...; | Combine results, removing duplicates |
SELECT ... WHERE id IN (SELECT t_id FROM u); | Subquery filter |
ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) | Window function: number rows per group |
SUM(x), AVG(x), MIN(x), MAX(x) | Aggregate functions |
BEGIN; ... COMMIT; | Group statements into a transaction |
EXPLAIN SELECT ...; | Show the query plan |