SQL Cheatsheet

The most common SQL syntax in one place, printable and searchable for quick reference.

SELECT

SELECT col1, col2 FROM table;

Retrieve specific columns from a table.

SELECT first_name, salary FROM employees;

SELECT * FROM table;

Retrieve all columns from a table.

SELECT * FROM employees;

SELECT DISTINCT col FROM table;

Retrieve unique values from a column.

SELECT DISTINCT genre FROM books;

SELECT col AS alias FROM table;

Rename a column in the result set.

SELECT salary / 12 AS monthly_salary FROM employees;

WHERE

WHERE col = value

Filter rows by equality.

WHERE department_id = 1

WHERE col1 = value AND col2 = value

Combine multiple conditions (all must be true).

WHERE department_id = 1 AND salary > 90000

WHERE col IN (v1, v2, ...)

Match against a list of values.

WHERE genre IN ('Sci-Fi', 'Drama')

WHERE col BETWEEN low AND high

Match an inclusive range.

WHERE salary BETWEEN 70000 AND 100000

WHERE col LIKE pattern

Pattern match using % (any chars) and _ (one char).

WHERE title LIKE '%Kyoto%'

WHERE col IS NULL

Match rows where a column has no value.

WHERE manager_id IS NULL

GROUP BY

GROUP BY col

Group rows sharing the same value for aggregation.

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

GROUP BY col HAVING condition

Filter groups after aggregation (HAVING, not WHERE).

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 90000;

JOIN

a JOIN b ON a.id = b.a_id

INNER JOIN, only rows with a match in both tables.

employees JOIN departments ON employees.department_id = departments.id

a LEFT JOIN b ON a.id = b.a_id

Keep all rows from the left table, NULLs where no match.

movies LEFT JOIN ratings ON movies.id = ratings.movie_id

a FULL JOIN b ON a.id = b.a_id

Keep all rows from both tables, NULLs where no match.

authors FULL JOIN books ON authors.id = books.author_id

Subqueries & CTEs

WITH cte AS (SELECT ...) SELECT * FROM cte;

Common Table Expression (CTE) to define a temporary result set.

WITH high_sales AS (SELECT * FROM sales WHERE amount > 1000) SELECT COUNT(*) FROM high_sales;

WHERE col = (SELECT col FROM ...)

Scalar subquery returning a single value for comparison.

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

SELECT * FROM (SELECT ...) AS alias;

Subquery in the FROM clause (derived table).

SELECT AVG(avg_sal) FROM (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) AS dept_averages;

WHERE EXISTS (SELECT 1 FROM ...)

Test for the existence of rows in a subquery.

SELECT * FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id);

Data Modification (DML)

INSERT INTO table (col1, col2) VALUES (v1, v2);

Insert new rows into a table.

INSERT INTO customers (name, email) VALUES ('Alice Smith', '[email protected]');

UPDATE table SET col = val WHERE condition;

Update existing values in a table.

UPDATE employees SET salary = salary * 1.05 WHERE department_id = 3;

DELETE FROM table WHERE condition;

Delete rows from a table.

DELETE FROM sessions WHERE last_active < '2026-01-01';

Data Definition (DDL)

CREATE TABLE table (col type CONSTRAINT, ...);

Create a new table with columns and constraints.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE, created_at TIMESTAMP DEFAULT NOW());

ALTER TABLE table ADD COLUMN col type;

Add, delete, or modify columns in an existing table.

ALTER TABLE products ADD COLUMN discount_rate DECIMAL(4,2);

DROP TABLE table_name;

Delete a table and all its data permanently.

DROP TABLE temp_logs;

TRUNCATE TABLE table_name;

Remove all rows from a table quickly without dropping the table.

TRUNCATE TABLE active_sessions;

Set Operations

query1 UNION query2;

Combine results of two queries, removing duplicates.

SELECT email FROM customers UNION SELECT email FROM leads;

query1 UNION ALL query2;

Combine results of two queries, retaining duplicates.

SELECT city FROM offices UNION ALL SELECT city FROM clients;

query1 INTERSECT query2;

Return common rows present in both query results.

SELECT customer_id FROM active_subscribers INTERSECT SELECT customer_id FROM promo_users;

query1 EXCEPT query2;

Return rows from query1 that are not present in query2.

SELECT product_id FROM inventory EXCEPT SELECT product_id FROM sales;

Functions

COUNT(*)

Count rows in a group.

SELECT COUNT(*) FROM employees;

SUM(col)

Sum a numeric column.

SELECT SUM(salary) FROM employees;

AVG(col)

Average a numeric column.

SELECT AVG(score) FROM grades;

ROUND(num, n)

Round to n decimal places.

SELECT ROUND(price, 2) FROM books;

COALESCE(a, b, ...)

First non-null value.

SELECT COALESCE(manager_id, -1) FROM employees;

CONCAT(a, b, ...)

Concatenate strings.

SELECT CONCAT(first_name, ' ', last_name) FROM employees;

Operators

=, !=, <>, <, >, <=, >=

Comparison operators.

WHERE price >= 20

AND, OR, NOT

Logical operators for combining conditions.

WHERE is_active = true AND city = 'Seattle'

col * n, col / n, col + n, col - n

Arithmetic operators.

SELECT price * 1.08 AS price_with_tax FROM products;

Conditional Logic

CASE WHEN cond1 THEN v1 ELSE v2 END

Conditional logic to return different values based on conditions.

SELECT name, CASE WHEN score >= 50 THEN 'Pass' ELSE 'Fail' END AS status FROM students;

NULLIF(val1, val2)

Return NULL if the two values are equal, otherwise return val1.

SELECT price / NULLIF(units_sold, 0) FROM products;

Date & Time

NOW() / CURRENT_TIMESTAMP

Get the current date and time.

SELECT NOW() AS current_time_marker;

EXTRACT(field FROM date)

Extract parts (YEAR, MONTH, DAY, etc.) from a date.

SELECT EXTRACT(YEAR FROM hire_date) FROM employees;

DATE_ADD(date, INTERVAL n unit)

Add a time interval to a date value.

SELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders;

DATEDIFF(date1, date2)

Calculate the difference between two date values.

SELECT DATEDIFF(end_date, start_date) AS days_elapsed FROM tasks;

Window Functions

RANK() OVER (PARTITION BY col ORDER BY col2)

Rank rows within a partition.

RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

AVG(col) OVER (PARTITION BY col2)

Compute an aggregate without collapsing rows.

AVG(salary) OVER (PARTITION BY department_id)

SUM(col) OVER (PARTITION BY col2 ORDER BY col3)

Running total within a partition.

SUM(quantity) OVER (PARTITION BY customer_id ORDER BY order_date)