SQL Cheat Sheet
SQL cheatsheet for quick, production-oriented reference of SQL syntax used to query and manage relational databases, including SELECT statements, joins, grouping, subqueries, set operations, common table expressions (CTEs), window functions, and transaction control.
SELECT
The SELECT statement retrieves rows from a table or query expression.
SELECT [DISTINCT] select_list
FROM table_or_query
[WHERE condition]
[GROUP BY group_list]
[HAVING group_condition]
[ORDER BY order_list]
[OFFSET n ROWS]
[FETCH FIRST n ROWS ONLY];
SELECT id, name
FROM city
WHERE population >= 1000000
ORDER BY name;
-- Result (example)
-- id | name
-- 2 | Berlin
-- 1 | Paris
SELECT DISTINCT
SELECT DISTINCT removes duplicate rows from the result set based on the selected columns.
SELECT DISTINCT column1 [, column2, ...]
FROM table_name;
SELECT DISTINCT country_id
FROM city
ORDER BY country_id;
-- Result (example)
-- country_id
-- 1
-- 2
-- 4
Aliases
Aliases rename columns or tables within a query result.
SELECT expression AS alias_name
FROM table_name AS alias_name;
SELECT co.name AS country_name, ci.name AS city_name
FROM country AS co
JOIN city AS ci
ON ci.country_id = co.id;
-- Result (example)
-- country_name | city_name
-- France | Paris
-- Germany | Berlin
WHERE
WHERE filters rows returned by SELECT, or rows affected by DML statements.
SELECT select_list
FROM table_name
WHERE condition;
SELECT name, rating
FROM city
WHERE rating > 3;
-- Result (example)
-- name | rating
-- Paris | 5
Comparison Operators
Comparison operators evaluate relationships between values.
= <> != < <= > >=
SELECT name
FROM city
WHERE name <> 'Berlin'
AND population >= 500000;
-- Result (example)
-- name
-- Paris
LIKE
LIKE matches text patterns using wildcards.
SELECT select_list
FROM table_name
WHERE text_column LIKE pattern;
SELECT name
FROM city
WHERE name LIKE 'P%' OR name LIKE '%s';
-- Result (example)
-- name
-- Paris
Wildcards
Wildcard characters define pattern matching rules for LIKE.
% -- any sequence of characters
_ -- a single character
SELECT name
FROM city
WHERE name LIKE '_ublin';
-- Result (example)
-- name
-- Dublin
-- Lublin
BETWEEN
BETWEEN filters values within an inclusive range.
SELECT select_list
FROM table_name
WHERE numeric_or_date BETWEEN low AND high;
SELECT name, population
FROM city
WHERE population BETWEEN 500000 AND 5000000
ORDER BY population DESC;
-- Result (example)
-- name | population
-- Berlin | 3460000
-- Paris | 2243000
IN
IN matches values against a list (or subquery result).
SELECT select_list
FROM table_name
WHERE expr IN (value1, value2, ...);
SELECT name
FROM city
WHERE country_id IN (1, 4, 7, 8)
ORDER BY name;
-- Result (example)
-- name
-- Paris
IS NULL
IS NULL and IS NOT NULL test for missing values.
SELECT select_list
FROM table_name
WHERE column IS NULL | column IS NOT NULL;
SELECT name
FROM city
WHERE rating IS NOT NULL;
-- Result (example)
-- name
-- Paris
-- Berlin
ORDER BY
ORDER BY sorts rows using one or more expressions.
SELECT select_list
FROM table_name
ORDER BY expr1 [ASC|DESC], expr2 [ASC|DESC];
SELECT name, rating
FROM city
ORDER BY rating DESC, name ASC;
-- Result (example)
-- name | rating
-- Paris | 5
-- Berlin| 3
LIMIT
LIMIT restricts the number of rows returned (supported by many systems).
SELECT select_list
FROM table_name
ORDER BY order_list
LIMIT n [OFFSET m];
SELECT name, population
FROM city
ORDER BY population DESC
LIMIT 2;
-- Result (example)
-- name | population
-- Berlin | 3460000
-- Paris | 2243000
OFFSET FETCH
OFFSET ... FETCH paginates results using a standard-style syntax (supported by multiple systems).
SELECT select_list
FROM table_name
ORDER BY order_list
OFFSET n ROWS
FETCH NEXT m ROWS ONLY;
SELECT name, population
FROM city
ORDER BY population DESC
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;
-- Result (example)
-- name | population
-- Berlin | 3460000
-- Paris | 2243000
TOP
TOP limits rows returned in SQL Server-style syntax.
SELECT TOP (n) select_list
FROM table_name
ORDER BY order_list;
SELECT TOP (2) name, population
FROM city
ORDER BY population DESC;
-- Result (example)
-- name | population
-- Berlin | 3460000
-- Paris | 2243000
INNER JOIN
INNER JOIN returns rows that match the join condition in both tables.
SELECT select_list
FROM table1
INNER JOIN table2
ON join_condition;
SELECT ci.name AS city, co.name AS country
FROM city AS ci
INNER JOIN country AS co
ON ci.country_id = co.id;
-- Result (example)
-- city | country
-- Paris | France
-- Berlin | Germany
LEFT JOIN
LEFT JOIN returns all rows from the left table and matching rows from the right table.
SELECT select_list
FROM table1
LEFT JOIN table2
ON join_condition;
SELECT ci.name AS city, co.name AS country
FROM city AS ci
LEFT JOIN country AS co
ON ci.country_id = co.id;
-- Result (example)
-- city | country
-- Paris | France
-- Berlin | Germany
-- Warsaw | NULL
RIGHT JOIN
RIGHT JOIN returns all rows from the right table and matching rows from the left table.
SELECT select_list
FROM table1
RIGHT JOIN table2
ON join_condition;
SELECT ci.name AS city, co.name AS country
FROM city AS ci
RIGHT JOIN country AS co
ON ci.country_id = co.id;
-- Result (example)
-- city | country
-- Paris | France
-- Berlin | Germany
-- NULL | Iceland
FULL JOIN
FULL JOIN returns all rows from both tables; non-matching sides return NULL.
SELECT select_list
FROM table1
FULL OUTER JOIN table2
ON join_condition;
SELECT ci.name AS city, co.name AS country
FROM city AS ci
FULL OUTER JOIN country AS co
ON ci.country_id = co.id;
-- Result (example)
-- city | country
-- Paris | France
-- Berlin | Germany
-- Warsaw | NULL
-- NULL | Iceland
CROSS JOIN
CROSS JOIN returns the Cartesian product of two tables.
SELECT select_list
FROM table1
CROSS JOIN table2;
SELECT ci.name AS city, co.name AS country
FROM city AS ci
CROSS JOIN country AS co;
-- Result (example)
-- city | country
-- Paris | France
-- Paris | Germany
GROUP BY
GROUP BY groups rows so aggregate functions can compute per-group values.
SELECT group_exprs, aggregate_exprs
FROM table_name
GROUP BY group_exprs;
SELECT country_id, COUNT(*) AS city_count
FROM city
GROUP BY country_id
ORDER BY country_id;
-- Result (example)
-- country_id | city_count
-- 1 | 3
HAVING
HAVING filters groups after aggregation.
SELECT group_exprs, aggregate_exprs
FROM table_name
GROUP BY group_exprs
HAVING group_condition;
SELECT country_id, AVG(rating) AS avg_rating
FROM city
GROUP BY country_id
HAVING AVG(rating) > 3.0;
-- Result (example)
-- country_id | avg_rating
-- 1 | 4.0
COUNT
COUNT returns the number of rows or non-null values.
SELECT COUNT(*) | COUNT(column) | COUNT(DISTINCT column)
FROM table_name
[WHERE condition];
SELECT COUNT(*) AS total_rows, COUNT(rating) AS rated_rows
FROM city;
-- Result (example)
-- total_rows | rated_rows
-- 8 | 6
SUM
SUM totals numeric values.
SELECT SUM(numeric_column)
FROM table_name
[WHERE condition];
SELECT country_id, SUM(population) AS total_city_pop
FROM city
GROUP BY country_id;
-- Result (example)
-- country_id | total_city_pop
-- 1 | 5000000
AVG
AVG computes the average numeric value.
SELECT AVG(numeric_column)
FROM table_name
[WHERE condition];
SELECT AVG(population) AS avg_city_pop
FROM city;
-- Result (example)
-- avg_city_pop
-- 1800000
MIN MAX
MIN and MAX return the smallest and largest values.
SELECT MIN(expr), MAX(expr)
FROM table_name
[WHERE condition];
SELECT MIN(population) AS min_pop, MAX(population) AS max_pop
FROM country;
-- Result (example)
-- min_pop | max_pop
-- 66600000 | 80700000
Subquery IN
A multi-row subquery can be used with IN to match any returned value.
SELECT select_list
FROM table_name
WHERE expr IN (SELECT expr FROM other_table WHERE condition);
SELECT name
FROM city
WHERE country_id IN (
SELECT id
FROM country
WHERE population > 20000000
);
-- Result (example)
-- name
-- Paris
-- Berlin
Subquery EXISTS
EXISTS tests whether a subquery returns at least one row.
SELECT select_list
FROM table_name
WHERE EXISTS (SELECT 1 FROM other_table WHERE condition);
SELECT name
FROM country AS c
WHERE EXISTS (
SELECT 1
FROM city AS ci
WHERE ci.country_id = c.id
);
-- Result (example)
-- name
-- France
-- Germany
Subquery ANY ALL
ANY compares a value to any value from a subquery; ALL compares to all values.
-- ANY: comparison is true if it matches at least one row returned
-- ALL: comparison is true only if it matches all rows returned
SELECT select_list
FROM table_name
WHERE expr < ANY (subquery)
OR expr > ALL (subquery);
SELECT *
FROM products
WHERE price < ANY (SELECT unit_price FROM supplier_products);
-- Result (example)
-- Rows whose price is less than at least one supplier unit_price.
UNION
UNION combines result sets and removes duplicates.
SELECT select_list FROM table1
UNION
SELECT select_list FROM table2;
SELECT first_name, last_name FROM customers
UNION
SELECT first_name, last_name FROM employees;
-- Result (example)
-- Unique names from both sets.
INTERSECT
INTERSECT returns rows appearing in both result sets.
SELECT select_list FROM table1
INTERSECT
SELECT select_list FROM table2;
SELECT first_name, last_name FROM customers
INTERSECT
SELECT first_name, last_name FROM employees;
-- Result (example)
-- Names present in both sets.
EXCEPT
EXCEPT returns rows in the first result set not present in the second.
SELECT select_list FROM table1
EXCEPT
SELECT select_list FROM table2;
SELECT first_name, last_name FROM customers
EXCEPT
SELECT first_name, last_name FROM employees;
-- Result (example)
-- Names present only in customers.
INSERT
INSERT adds new rows to a table.
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
INSERT INTO customers (first_name, last_name)
VALUES ('Mary', 'Doe');
-- Result (example)
-- INSERT 0 1
UPDATE
UPDATE modifies existing rows in a table.
UPDATE table_name
SET column1 = value1 [, column2 = value2, ...]
WHERE condition;
UPDATE employees
SET employee_name = 'John Doe', department = 'Marketing'
WHERE employee_id = 10;
-- Result (example)
-- UPDATE 1
DELETE
DELETE removes rows from a table.
DELETE FROM table_name
WHERE condition;
DELETE FROM employees
WHERE employee_name = 'John Doe';
-- Result (example)
-- DELETE 1
TRUNCATE
TRUNCATE deletes all rows from a table while keeping the table definition.
TRUNCATE TABLE table_name;
TRUNCATE TABLE customers;
-- Result (example)
-- TRUNCATE TABLE
CREATE TABLE
CREATE TABLE defines a new table and its columns.
CREATE TABLE table_name (
column1 datatype [constraints],
column2 datatype [constraints],
...
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT
);
-- Result (example)
-- CREATE TABLE
ALTER TABLE ADD COLUMN
ALTER TABLE ... ADD adds a new column to an existing table.
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE customers
ADD email VARCHAR(100);
-- Result (example)
-- ALTER TABLE
DROP TABLE
DROP TABLE removes a table definition and its data.
DROP TABLE table_name;
DROP TABLE customers;
-- Result (example)
-- DROP TABLE
CREATE INDEX
CREATE INDEX constructs an index on one or more columns (or expressions in some systems).
CREATE INDEX index_name
ON table_name (column1 [, column2, ...]);
CREATE INDEX idx_city_country_id
ON city (country_id);
-- Result (example)
-- CREATE INDEX
GRANT
GRANT gives privileges to a user or role.
GRANT privilege_list
ON object_name
TO grantee;
GRANT SELECT, INSERT
ON employees
TO "John Doe";
-- Result (example)
-- GRANT
REVOKE
REVOKE removes privileges from a user or role.
REVOKE privilege_list
ON object_name
FROM grantee;
REVOKE SELECT, INSERT
ON employees
FROM "John Doe";
-- Result (example)
-- REVOKE
BEGIN TRANSACTION
Begins an explicit transaction so multiple statements execute atomically.
BEGIN TRANSACTION;
-- statements
COMMIT;
BEGIN TRANSACTION;
INSERT INTO employees (name, age) VALUES ('Alice', 30);
UPDATE products SET price = 25.00 WHERE category = 'Electronics';
COMMIT;
-- Result (example)
-- COMMIT
SAVEPOINT
SAVEPOINT sets a named point within a transaction for partial rollback.
SAVEPOINT savepoint_name;
BEGIN TRANSACTION;
INSERT INTO employees (name, age) VALUES ('Carol', 28);
SAVEPOINT before_update;
UPDATE products SET price = 40.00 WHERE category = 'Electronics';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;
-- Result (example)
-- COMMIT
WITH CTE
A common table expression (CTE) defines a named temporary result set used by a single statement.
WITH cte_name AS (
SELECT select_list
FROM table_name
WHERE condition
)
SELECT select_list
FROM cte_name;
WITH big_cities AS (
SELECT name, population
FROM city
WHERE population >= 2000000
)
SELECT name
FROM big_cities
ORDER BY population DESC;
-- Result (example)
-- name
-- Berlin
-- Paris
WITH RECURSIVE
A recursive CTE references itself to process hierarchical or iterative data.
WITH RECURSIVE cte_name (columns...) AS (
seed_query
UNION ALL
recursive_query
)
SELECT *
FROM cte_name;
WITH RECURSIVE org_chart (employee_id, manager_id, depth) AS (
SELECT employee_id, manager_id, 0
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, oc.depth + 1
FROM employees e
JOIN org_chart oc
ON e.manager_id = oc.employee_id
)
SELECT *
FROM org_chart
ORDER BY depth, employee_id;
-- Result (example)
-- employee_id | manager_id | depth
-- 1 | NULL | 0
Window Function OVER
Window functions compute values across sets of rows related to the current row using OVER (...).
SELECT select_list,
window_function(...) OVER (PARTITION BY partition_exprs ORDER BY order_exprs) AS alias
FROM table_name;
SELECT country_id,
name,
population,
SUM(population) OVER (PARTITION BY country_id) AS country_total
FROM city
ORDER BY country_id, population DESC;
-- Result (example)
-- country_id | name | population | country_total
-- 1 | Paris | 2243000 | 5000000
ROW_NUMBER
ROW_NUMBER() numbers rows within a partition in the order specified.
SELECT select_list,
ROW_NUMBER() OVER (PARTITION BY partition_exprs ORDER BY order_exprs) AS row_num
FROM table_name;
SELECT country_id, name, population,
ROW_NUMBER() OVER (PARTITION BY country_id ORDER BY population DESC) AS rn
FROM city;
-- Result (example)
-- country_id | name | population | rn
-- 1 | Paris | 2243000 | 1
DENSE_RANK
DENSE_RANK() assigns ranks within partitions; ties receive the same rank without gaps.
SELECT select_list,
DENSE_RANK() OVER (PARTITION BY partition_exprs ORDER BY order_exprs) AS drnk
FROM table_name;
SELECT group_id, product_id, price,
DENSE_RANK() OVER (PARTITION BY group_id ORDER BY price DESC) AS price_rank
FROM products;
-- Result (example)
-- group_id | product_id | price | price_rank
-- 1 | 10 | 99.00 | 1
LAG LEAD
LAG and LEAD access values from preceding or following rows in a window.
SELECT select_list,
LAG(expr, offset, default) OVER (PARTITION BY partition_exprs ORDER BY order_exprs) AS prev_value,
LEAD(expr, offset, default) OVER (PARTITION BY partition_exprs ORDER BY order_exprs) AS next_value
FROM table_name;
SELECT day,
revenue,
LAG(revenue, 1, 0) OVER (ORDER BY day) AS prev_revenue,
revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM daily_sales
ORDER BY day;
-- Result (example)
-- day | revenue | prev_revenue | delta
-- 2025-01-01 | 100 | 0 | 100
NTILE
NTILE(n) divides a partition into n buckets and assigns each row a bucket number.
SELECT select_list,
NTILE(n) OVER (PARTITION BY partition_exprs ORDER BY order_exprs) AS bucket
FROM table_name;
SELECT customer_id, total_spend,
NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile
FROM customer_spend;
-- Result (example)
-- customer_id | total_spend | spend_quartile
-- 42 | 9999.00 | 1
MERGE
MERGE performs conditional insert/update/delete operations using a source-to-target match.
MERGE INTO target_table AS t
USING source_table AS s
ON match_condition
WHEN MATCHED THEN
UPDATE SET column = value
WHEN NOT MATCHED THEN
INSERT (columns...) VALUES (values...);
MERGE INTO inventory AS t
USING incoming_inventory AS s
ON t.sku = s.sku
WHEN MATCHED THEN
UPDATE SET qty = s.qty
WHEN NOT MATCHED THEN
INSERT (sku, qty) VALUES (s.sku, s.qty);
-- Result (example)
-- Rows updated/inserted based on match condition.
EXPLAIN
EXPLAIN displays the planned execution strategy for a statement (system support varies).
EXPLAIN statement;
EXPLAIN
SELECT ci.name, co.name
FROM city ci
JOIN country co ON ci.country_id = co.id
WHERE ci.population > 1000000;
-- Result (example)
-- Query plan output depends on the database engine.