SQL Cheatsheet

The most common SQL syntax in one place — printable 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

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;

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)