SQL Cheatsheet
A quick reference for SQL — SELECT queries, filtering, joins, aggregates, subqueries, DDL, and common functions.
Sections
Basic SELECT
| Clause | Example | Description |
|---|---|---|
SELECT | SELECT name, age FROM users | Choose columns to return |
SELECT * | SELECT * FROM users | Return all columns |
DISTINCT | SELECT DISTINCT country FROM users | Deduplicate result rows |
AS | SELECT name AS full_name FROM users | Alias a column or table |
LIMIT | SELECT * FROM users LIMIT 10 | Return at most N rows |
OFFSET | SELECT * FROM users LIMIT 10 OFFSET 20 | Skip N rows (pagination) |
ORDER BY | SELECT * FROM users ORDER BY age DESC | Sort results (ASC default) |
Filtering (WHERE)
| Operator | Example |
|---|---|
= | WHERE age = 30 |
<> / != | WHERE status <> 'inactive' |
< > <= >= | WHERE age >= 18 AND age <= 65 |
BETWEEN | WHERE age BETWEEN 18 AND 65 |
IN | WHERE country IN ('US', 'UK', 'CA') |
NOT IN | WHERE role NOT IN ('admin', 'superuser') |
LIKE | WHERE name LIKE 'A%' -- starts with A |
ILIKE | WHERE name ILIKE '%smith%' -- case-insensitive (PostgreSQL) |
IS NULL | WHERE deleted_at IS NULL |
IS NOT NULL | WHERE email IS NOT NULL |
AND / OR / NOT | WHERE active = true AND (role = 'admin' OR role = 'mod') |
Joins
| Join Type | Syntax | Returns |
|---|---|---|
INNER JOIN | FROM a INNER JOIN b ON a.id = b.a_id | Rows with matching keys in both tables |
LEFT JOIN | FROM a LEFT JOIN b ON a.id = b.a_id | All rows from a; nulls for b where no match |
RIGHT JOIN | FROM a RIGHT JOIN b ON a.id = b.a_id | All rows from b; nulls for a where no match |
FULL OUTER JOIN | FROM a FULL OUTER JOIN b ON a.id = b.a_id | All rows from both, nulls where no match |
CROSS JOIN | FROM a CROSS JOIN b | Cartesian product — every a paired with every b |
SELF JOIN | FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id | Join a table with itself |
Aggregates & Grouping
| Clause / Function | Example | Description |
|---|---|---|
COUNT(*) | SELECT COUNT(*) FROM orders | Count all rows |
COUNT(col) | SELECT COUNT(email) FROM users | Count non-null values |
SUM | SELECT SUM(amount) FROM payments | Sum of values |
AVG | SELECT AVG(score) FROM results | Average of values |
MIN / MAX | SELECT MIN(price), MAX(price) FROM products | Minimum / maximum value |
GROUP BY | SELECT country, COUNT(*) FROM users GROUP BY country | Group rows and aggregate |
HAVING | GROUP BY country HAVING COUNT(*) > 100 | Filter groups (like WHERE but post-aggregate) |
Subqueries & CTEs
| Pattern | Example |
|---|---|
Subquery in WHERE | SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = true) |
Subquery in FROM | SELECT avg_order.* FROM (SELECT user_id, AVG(amount) avg FROM orders GROUP BY user_id) avg_order |
CTE (WITH) | WITH active_users AS (SELECT id FROM users WHERE active = true) SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users) |
EXISTS | SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id) |
DDL — Creating & Modifying Tables
| Statement | Example |
|---|---|
CREATE TABLE | CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMPTZ DEFAULT NOW()) |
ALTER TABLE | ALTER TABLE users ADD COLUMN phone TEXT |
ALTER COLUMN | ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(255) |
DROP TABLE | DROP TABLE IF EXISTS sessions |
CREATE INDEX | CREATE INDEX idx_users_email ON users(email) |
DROP INDEX | DROP INDEX IF EXISTS idx_users_email |
Common Data Types
| Type | Description |
|---|---|
INTEGER / INT | 4-byte integer |
BIGINT | 8-byte integer — use for IDs on large tables |
SERIAL / BIGSERIAL | Auto-incrementing integer (PostgreSQL) |
TEXT | Variable-length string (no length limit in PostgreSQL) |
VARCHAR(n) | Variable-length string with max length n |
BOOLEAN | true / false |
NUMERIC(p,s) | Exact decimal — use for money |
FLOAT / REAL | Approximate decimal — avoid for money |
DATE | Calendar date (no time) |
TIMESTAMP | Date and time (no timezone) |
TIMESTAMPTZ | Date and time with timezone (stores as UTC) |
JSON / JSONB | JSON document (JSONB is binary, indexable — prefer it) |
UUID | 128-bit universally unique identifier |