SQL Queries Cheatsheet

sql

SQL query reference — SELECT to advanced JOINs and window functions.

7 sections · 53 items

Basic SELECT

SELECT

SELECT column1, column2 FROM table_name

Retrieves specific columns from a table

sql

SELECT name, email FROM users

SELECT *

SELECT * FROM table_name

Retrieves all columns from a table

sql

SELECT * FROM products

WHERE

SELECT columns FROM table WHERE condition

Filters rows based on a specified condition

sql

SELECT * FROM users WHERE age > 18

ORDER BY

SELECT columns FROM table ORDER BY column [ASC|DESC]

Sorts the result set by specified columns

sql

SELECT * FROM products ORDER BY price DESC

LIMIT

SELECT columns FROM table LIMIT number

Restricts the number of rows returned

sql

SELECT * FROM orders LIMIT 10

OFFSET

SELECT columns FROM table LIMIT n OFFSET m

Skips a specified number of rows before returning results

sql

SELECT * FROM users LIMIT 10 OFFSET 20

DISTINCT

SELECT DISTINCT column FROM table

Returns only unique values eliminating duplicates

sql

SELECT DISTINCT country FROM customers

AS (Alias)

SELECT column AS alias_name FROM table

Renames a column or table with a temporary alias

sql

SELECT first_name AS name, COUNT(*) AS total FROM users

Filtering

AND

WHERE condition1 AND condition2

Combines conditions where all must be true

sql

SELECT * FROM users WHERE age > 18 AND status = 'active'

WHERE condition1 OR condition2

Combines conditions where at least one must be true

sql

SELECT * FROM products WHERE category = 'books' OR category = 'music'

NOT

WHERE NOT condition

Negates a condition

sql

SELECT * FROM users WHERE NOT country = 'USA'

WHERE column IN (value1, value2, ...)

Matches any value in a specified list

sql

SELECT * FROM orders WHERE status IN ('pending', 'processing')

BETWEEN

WHERE column BETWEEN value1 AND value2

Selects values within a given inclusive range

sql

SELECT * FROM products WHERE price BETWEEN 10 AND 100

WHERE column LIKE pattern

Searches for a specified pattern using wildcards % and _

sql

SELECT * FROM users WHERE email LIKE '%@gmail.com'

IS NULL

WHERE column IS NULL

Checks for NULL values in a column

sql

SELECT * FROM customers WHERE phone IS NULL

IS NOT NULL

WHERE column IS NOT NULL

Checks for non-NULL values in a column

sql

SELECT * FROM orders WHERE shipped_date IS NOT NULL

EXISTS

WHERE EXISTS (subquery)

Returns true if the subquery returns any rows

sql

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

Joins

INNER JOIN

SELECT cols FROM t1 INNER JOIN t2 ON t1.col = t2.col

Returns rows that have matching values in both tables

sql

SELECT orders.id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id

LEFT JOIN

SELECT cols FROM t1 LEFT JOIN t2 ON t1.col = t2.col

Returns all rows from left table and matched rows from right table

sql

SELECT users.name, orders.id FROM users LEFT JOIN orders ON users.id = orders.user_id

RIGHT JOIN

SELECT cols FROM t1 RIGHT JOIN t2 ON t1.col = t2.col

Returns all rows from right table and matched rows from left table

sql

SELECT orders.id, customers.name FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id

FULL OUTER JOIN

SELECT cols FROM t1 FULL OUTER JOIN t2 ON t1.col = t2.col

Returns all rows when there is a match in either table

sql

SELECT a.name, b.order_id FROM customers a FULL OUTER JOIN orders b ON a.id = b.customer_id

CROSS JOIN

SELECT cols FROM t1 CROSS JOIN t2

Returns Cartesian product of both tables

sql

SELECT colors.name, sizes.name FROM colors CROSS JOIN sizes

Self Join

SELECT cols FROM table t1 JOIN table t2 ON t1.col = t2.col

Joins a table to itself to compare rows within the same table

sql

SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id

Aggregation

GROUP BY

SELECT col, AGG(col2) FROM table GROUP BY col

Groups rows sharing a property to apply aggregate functions

sql

SELECT country, COUNT(*) FROM users GROUP BY country

HAVING

SELECT col, AGG(col2) FROM table GROUP BY col HAVING condition

Filters groups based on aggregate function results

sql

SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) > 50

COUNT

COUNT(column) or COUNT(*)

Returns the number of rows or non-NULL values

sql

SELECT COUNT(*) FROM orders WHERE status = 'completed'

SUM

SUM(column)

Returns the total sum of a numeric column

sql

SELECT SUM(amount) AS total_sales FROM orders

AVG

AVG(column)

Returns the average value of a numeric column

sql

SELECT AVG(rating) FROM reviews WHERE product_id = 1

MIN

MIN(column)

Returns the smallest value in a column

sql

SELECT MIN(price) AS cheapest FROM products

MAX

MAX(column)

Returns the largest value in a column

sql

SELECT MAX(salary) AS highest_salary FROM employees

Subqueries

Scalar Subquery

SELECT col, (SELECT AGG(col) FROM t2) FROM t1

Returns a single value to be used in the outer query

sql

SELECT name, (SELECT AVG(salary) FROM employees) AS avg_sal FROM employees

Subquery in WHERE

SELECT cols FROM t1 WHERE col IN (SELECT col FROM t2)

Uses subquery result to filter the outer query

sql

SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1)

Correlated Subquery

SELECT cols FROM t1 WHERE col op (SELECT col FROM t2 WHERE t2.col = t1.col)

Subquery that references columns from the outer query

sql

SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id)

CTE with WITH

WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name

Creates a named temporary result set for the main query

sql

WITH top_sales AS (SELECT * FROM orders WHERE amount > 1000) SELECT customer_id, COUNT(*) FROM top_sales GROUP BY customer_id

Recursive CTE

WITH RECURSIVE cte AS (base UNION ALL recursive) SELECT * FROM cte

CTE that references itself to handle hierarchical data

sql

WITH RECURSIVE org AS (SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN org ON e.manager_id = org.id) SELECT * FROM org

Window Functions

OVER

function() OVER ([PARTITION BY col] [ORDER BY col])

Defines a window frame for the window function to operate on

sql

SELECT name, salary, SUM(salary) OVER () AS total FROM employees

PARTITION BY

function() OVER (PARTITION BY column)

Divides the result set into partitions for window calculations

sql

SELECT dept, name, AVG(salary) OVER (PARTITION BY dept) FROM employees

ROW_NUMBER

ROW_NUMBER() OVER (ORDER BY column)

Assigns a unique sequential integer to each row

sql

SELECT name, ROW_NUMBER() OVER (ORDER BY created_at) AS rn FROM users

RANK

RANK() OVER (ORDER BY column)

Assigns rank with gaps for ties

sql

SELECT name, score, RANK() OVER (ORDER BY score DESC) FROM players

DENSE_RANK

DENSE_RANK() OVER (ORDER BY column)

Assigns rank without gaps for ties

sql

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) FROM players

LAG

LAG(column, offset, default) OVER (ORDER BY column)

Accesses data from a previous row in the result set

sql

SELECT date, sales, LAG(sales, 1) OVER (ORDER BY date) AS prev_sales FROM daily_sales

LEAD

LEAD(column, offset, default) OVER (ORDER BY column)

Accesses data from a subsequent row in the result set

sql

SELECT date, price, LEAD(price, 1) OVER (ORDER BY date) AS next_price FROM stocks

FIRST_VALUE

FIRST_VALUE(column) OVER (PARTITION BY col ORDER BY col)

Returns the first value in the window frame

sql

SELECT name, FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY hire_date) AS first_hire FROM employees

LAST_VALUE

LAST_VALUE(column) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Returns the last value in the window frame

sql

SELECT name, LAST_VALUE(name) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM employees

Mutations

INSERT

INSERT INTO table (col1, col2) VALUES (val1, val2)

Adds new rows to a table

sql

INSERT INTO users (name, email) VALUES ('John', 'john@example.com')

INSERT Multiple Rows

INSERT INTO table (cols) VALUES (vals1), (vals2), (vals3)

Inserts multiple rows in a single statement

sql

INSERT INTO products (name, price) VALUES ('A', 10), ('B', 20), ('C', 30)

INSERT from SELECT

INSERT INTO table1 (cols) SELECT cols FROM table2

Inserts rows from another table or query result

sql

INSERT INTO archived_orders SELECT * FROM orders WHERE created_at < '2023-01-01'

UPDATE

UPDATE table SET col1 = val1, col2 = val2 WHERE condition

Modifies existing rows in a table

sql

UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01'

UPDATE with JOIN

UPDATE t1 SET t1.col = t2.col FROM t1 JOIN t2 ON condition

Updates rows based on values from another table

sql

UPDATE orders SET status = 'vip' FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.tier = 'gold'

DELETE

DELETE FROM table WHERE condition

Removes rows from a table based on a condition

sql

DELETE FROM sessions WHERE expires_at < NOW()

UPSERT (PostgreSQL)

INSERT INTO table (cols) VALUES (vals) ON CONFLICT (col) DO UPDATE SET col = val

Inserts a row or updates it if a conflict occurs

sql

INSERT INTO users (id, name) VALUES (1, 'John') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name

UPSERT (MySQL)

INSERT INTO table (cols) VALUES (vals) ON DUPLICATE KEY UPDATE col = val

Inserts a row or updates it if duplicate key exists

sql

INSERT INTO users (id, name) VALUES (1, 'John') ON DUPLICATE KEY UPDATE name = VALUES(name)

TRUNCATE

TRUNCATE TABLE table_name

Removes all rows from a table quickly without logging individual deletions

sql

TRUNCATE TABLE temp_data