SQL Cheat Sheet
SQL reference with SELECT, JOIN, window functions, subqueries, and schema commands. Works with MySQL, PostgreSQL, and SQLite.
Queries
| Syntax | Description | Example |
|---|---|---|
| Select specific columns | SELECT name, email FROM users | |
| Select all columns | SELECT * FROM products | |
| Select unique values only | SELECT DISTINCT country FROM customers | |
| Filter rows by condition | SELECT * FROM users WHERE age > 21 | |
| Combine conditions | WHERE age > 21 AND country = 'US' | |
| Sort results | ORDER BY created_at DESC | |
| Limit number of rows returned | SELECT * FROM logs LIMIT 100 | |
| Skip n rows (pagination) | LIMIT 10 OFFSET 20 | |
| Pattern matching with wildcards | WHERE name LIKE 'A%' | |
| Match any value in list | WHERE status IN ('active', 'pending') | |
| Range check (inclusive) | WHERE price BETWEEN 10 AND 50 | |
| Check for null values | WHERE email IS NOT NULL | |
| Rename column or table in output | SELECT name AS customer_name | |
| Conditional logic in queries | SELECT CASE WHEN age>=18 THEN 'Adult' ELSE 'Minor' END | |
| Check if subquery returns rows | WHERE EXISTS (SELECT 1 FROM orders WHERE ...) | |
| Return first non-null value | SELECT COALESCE(nickname, name) AS display |
Joins
| Syntax | Description | Example |
|---|---|---|
| Rows matching in both tables | FROM orders INNER JOIN users ON orders.user_id = users.id | |
| All left rows + matching right | FROM users LEFT JOIN orders ON users.id = orders.user_id | |
| All right rows + matching left | FROM orders RIGHT JOIN users ON ... | |
| All rows from both tables | FROM a FULL OUTER JOIN b ON a.id = b.id | |
| Cartesian product of both tables | FROM colors CROSS JOIN sizes | |
| Table joined with itself | FROM employees e1 JOIN employees e2 ON e1.mgr_id = e2.id |
Aggregation
| Syntax | Description | Example |
|---|---|---|
| Count non-null values | SELECT COUNT(*) FROM users | |
| Sum all values | SELECT SUM(amount) FROM orders | |
| Average of values | SELECT AVG(price) FROM products | |
| Minimum / maximum value | SELECT MIN(price), MAX(price) FROM products | |
| Group rows for aggregation | SELECT country, COUNT(*) FROM users GROUP BY country | |
| Filter groups (like WHERE for groups) | GROUP BY country HAVING COUNT(*) > 10 |
Modification
| Syntax | Description | Example |
|---|---|---|
| Insert a new row | INSERT INTO users (name, email) VALUES ('Jo', 'jo@x.com') | |
| Insert from query results | INSERT INTO archive SELECT * FROM logs WHERE year < 2024 | |
| Update existing rows | UPDATE users SET status = 'active' WHERE id = 1 | |
| Delete rows | DELETE FROM sessions WHERE expires < NOW() | |
| Insert or update on conflict | INSERT INTO ... ON CONFLICT (id) DO UPDATE SET ... | |
| Delete all rows (fast, no logging) | TRUNCATE TABLE temp_data |
Schema
| Syntax | Description | Example |
|---|---|---|
| Create a new table | CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT) | |
| Add column to table | ALTER TABLE users ADD COLUMN age INT | |
| Remove column from table | ALTER TABLE users DROP COLUMN temp | |
| Rename table or column | ALTER TABLE users RENAME COLUMN name TO full_name | |
| Delete a table permanently | DROP TABLE IF EXISTS temp_data | |
| Create index for faster queries | CREATE INDEX idx_email ON users(email) | |
| Unique identifier for each row | id SERIAL PRIMARY KEY | |
| Reference to another table's PK | FOREIGN KEY (user_id) REFERENCES users(id) | |
| Ensure column values are unique | email TEXT UNIQUE NOT NULL | |
| Prevent null values | name TEXT NOT NULL | |
| Set default value for column | status TEXT DEFAULT 'pending' | |
| Validate data on insert/update | CHECK (age >= 0) |
Window Functions
| Syntax | Description | Example |
|---|---|---|
| Sequential row numbering | ROW_NUMBER() OVER (ORDER BY created_at) | |
| Rank with gaps for ties | RANK() OVER (ORDER BY score DESC) | |
| Rank without gaps | DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) | |
| Access previous row's value | LAG(price, 1) OVER (ORDER BY date) | |
| Access next row's value | LEAD(price, 1) OVER (ORDER BY date) | |
| Running/partitioned sum | SUM(amount) OVER (PARTITION BY user_id ORDER BY date) | |
| Divide window into groups | OVER (PARTITION BY department) |
Subqueries
| Syntax | Description | Example |
|---|---|---|
| Subquery in WHERE clause | WHERE id IN (SELECT user_id FROM orders) | |
| Subquery as derived table | FROM (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) AS sub | |
| Common Table Expression | WITH top_users AS (SELECT ...) SELECT * FROM top_users |
String Functions
| Syntax | Description | Example |
|---|---|---|
| Concatenate strings | CONCAT(first_name, ' ', last_name) | |
| Change case | UPPER(name) | |
| Remove whitespace | TRIM(email) | |
| Extract part of string | SUBSTRING(phone, 1, 3) | |
| Number of characters | WHERE LENGTH(password) >= 8 | |
| Replace occurrences | REPLACE(url, 'http:', 'https:') |
Date Functions
| Syntax | Description | Example |
|---|---|---|
| Current date and time | WHERE created_at > NOW() - INTERVAL '7 days' | |
| Current date only | WHERE date = CURRENT_DATE | |
| Extract year/month/day/hour | EXTRACT(YEAR FROM created_at) | |
| Truncate to precision | DATE_TRUNC('month', created_at) | |
| Difference between dates | AGE(NOW(), birth_date) | |
| Time duration | NOW() - INTERVAL '30 days' |
Frequently asked questions
What's the difference between WHERE and HAVING?
WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY. Example: WHERE price > 10 filters rows, then GROUP BY category with HAVING COUNT(*) > 5 keeps only categories with more than 5 matching products.
When should I use INNER JOIN vs LEFT JOIN?
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns ALL rows from the left table plus matching rows from the right (NULL if no match). Use LEFT JOIN when you don't want to lose rows that have no match.
What is a CTE and when should I use one?
A CTE (Common Table Expression) is a named temporary result set defined with WITH ... AS. Use it to break complex queries into readable steps, avoid repeating subqueries, and create recursive queries. CTEs exist only for the duration of the query.
How do I prevent SQL injection?
Never concatenate user input into SQL strings. Use parameterized queries (prepared statements) with placeholders: WHERE id = $1 (PostgreSQL) or WHERE id = ? (MySQL). ORMs and query builders handle this automatically.
What's the difference between DELETE and TRUNCATE?
DELETE removes rows one by one, can use WHERE, is logged, and can be rolled back. TRUNCATE removes all rows instantly, can't use WHERE, is minimally logged, and resets auto-increment. Use TRUNCATE for clearing entire tables.
How do window functions differ from GROUP BY?
GROUP BY collapses rows into groups (one row per group). Window functions compute values across rows WITHOUT collapsing - every row keeps its identity. Use GROUP BY for summaries, window functions for rankings, running totals, and row comparisons.
Go from reference to real skills
Cheat sheets are great for quick lookups. Our in-depth courses take you from the fundamentals to professional-level mastery.
Browse all courses