The Complete SQL Cheat Sheet 2026

A concise directory of SQL keywords and commands for schema design, querying, data manipulation, and performance optimization.

1. Basics & SELECT

SELECT: Retrieves columns and rows from a database table.

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name FROM employees;

AS (Alias): Renames a projected column or a table temporarily in the query.

SELECT first_name AS name, salary * 12 AS yearly_salary 
FROM employees;

SELECT DISTINCT: Deduplicates rows to return only unique values.

SELECT DISTINCT department FROM employees;

LIMIT / FETCH / TOP: Limits the number of rows returned by the query.

SELECT * FROM employees LIMIT 10;            -- PostgreSQL / MySQL
SELECT TOP 10 * FROM employees;              -- SQL Server
SELECT * FROM employees FETCH FIRST 10 ROWS ONLY; -- Standard SQL

2. Filtering & Sorting

WHERE: Filters rows so that only those matching specified conditions are returned.

SELECT * FROM employees WHERE salary > 60000;

AND / OR / NOT: Combines multiple conditions or negates a condition filter.

SELECT * FROM employees 
WHERE department = 'Engineering' AND salary >= 70000;

BETWEEN: Filters values within an inclusive range (lower bounds to upper bounds).

SELECT * FROM employees WHERE salary BETWEEN 70000 AND 120000;

IN: Evaluates expression equality against a designated list of values or subquery results.

SELECT * FROM employees WHERE department IN ('Engineering', 'Data');

LIKE / ILIKE: Searches for pattern wildcards (% matching zero/more chars, _ matching one char).

SELECT * FROM users WHERE email LIKE '%@gmail.com';     -- Ends with
SELECT * FROM users WHERE name ILIKE 'alice%';       -- Case-insensitive (PostgreSQL)

IS NULL / IS NOT NULL: Checks if column expressions are empty, unknown, or defined.

SELECT * FROM employees WHERE manager_id IS NULL;

ORDER BY: Sorts results in ASCending (default) or DESCending order.

SELECT name, salary FROM employees ORDER BY salary DESC;

OFFSET: Skips a specific number of rows before returning output records.

SELECT * FROM employees ORDER BY id LIMIT 20 OFFSET 40;

3. JOINs

INNER JOIN: Returns rows when there are matching values in both tables.

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

LEFT JOIN: Returns all rows from left table, matching right table records where present (NULL otherwise).

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

RIGHT JOIN: Returns all rows from right table, matching left table records where present (NULL otherwise).

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

FULL OUTER JOIN: Returns all rows when there is a match in either left or right table.

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

CROSS JOIN: Returns the Cartesian product (all combinations) of the joined tables.

SELECT e.name, p.project_name
FROM employees e
CROSS JOIN projects p;

SELF JOIN: Joins a table back to itself under distinct aliases.

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

4. Aggregation & GROUP BY

COUNT / SUM / AVG / MIN / MAX: Aggregates multiple numerical values into a single summary scalar.

SELECT 
  COUNT(*) AS total, 
  SUM(salary) AS total_payroll, 
  AVG(salary) AS avg_salary,
  MIN(salary) AS min_val,
  MAX(salary) AS max_val 
FROM employees;

GROUP BY: Collapses rows that share specified column values into aggregate groups.

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

HAVING: Filters aggregated groups after they have been processed (unlike WHERE, which filters rows before grouping).

SELECT department, AVG(salary) 
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;

ROLLUP: Generates hierarchical sub-totals along with the final grand total.

SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY ROLLUP(department, job_title);

GROUPING SETS: Defines multiple distinct grouping scopes in a single query.

SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((department), (job_title), ());

5. Subqueries & CTEs

Subquery (Scalar / IN): A query block nested inside another query.

-- Scalar Subquery
SELECT name, salary, (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

-- IN Subquery
SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NYC');

EXISTS / NOT EXISTS: Returns true if the subquery contains one or more matching rows (stops scanning early).

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

WITH (Common Table Expression - CTE): Creates a named temporary result set available inside the query's scope.

WITH high_earners AS (
  SELECT id, name, salary FROM employees WHERE salary > 90000
)
SELECT * FROM high_earners;

WITH RECURSIVE (Recursive CTE): Iterates over its own output to resolve hierarchical operations (like trees or parent-child structures).

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth;

6. Window Functions

OVER / PARTITION BY: Specifies the window bounds and row ordering context for window functions.

-- Syntax skeleton
FUNCTION() OVER (PARTITION BY col1 ORDER BY col2 ROWS/RANGE BETWEEN ...)

ROW_NUMBER / RANK / DENSE_RANK / NTILE: Assigns sequence integers, ranking positions, or percentile buckets inside a window.

SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk,
  NTILE(4)     OVER (ORDER BY salary) AS quartile
FROM employees;

LAG / LEAD: Fetches values from preceding or succeeding rows at a specified offset.

SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;

FIRST_VALUE / LAST_VALUE: Extracts evaluated values of the first or final row within a window slice.

SELECT month, revenue,
  FIRST_VALUE(revenue) OVER (ORDER BY month) AS first_month,
  LAST_VALUE(revenue) OVER (
    ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_month
FROM monthly_revenue;

Running Total / Moving Average: Performs window aggregations across a range relative to the current position.

SELECT order_date, amount,
  SUM(amount) OVER (
    ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  AVG(amount) OVER (
    ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

7. Data Types

INT / BIGINT: Integers (32-bit vs large scale 64-bit indexes/identifiers).

id INT, large_id BIGINT

DECIMAL / NUMERIC: Exact representation decimal data types for financial computations.

price DECIMAL(10, 2)

VARCHAR / CHAR / TEXT: Alphanumeric string representations (variable, static length, or unlimited limits).

username VARCHAR(50), country CHAR(2), bio TEXT

BOOLEAN: Logical true/false flag state indicator.

is_active BOOLEAN

DATE / TIMESTAMP / TIMESTAMPTZ: Temporal date calendars, timestamp instances, and timestamp instances with timezones.

start_date DATE, created_at TIMESTAMPTZ

JSON / JSONB: Semi-structured JSON strings (JSONB is binary encoded and indexable in PostgreSQL).

metadata JSONB

8. DDL — Schema Management

CREATE TABLE: Provisions a new relational table schema specifying constraints.

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  age INT CHECK (age >= 18),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

FOREIGN KEY / ON DELETE: Links table rows together while configuring delete cascade logic.

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(300) NOT NULL
);

ALTER TABLE: Alters schema column configurations or constraints dynamically.

ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users DROP COLUMN bio;
ALTER TABLE users RENAME COLUMN old_name TO new_name;

DROP TABLE: Disables and deletes the targeted database table completely.

DROP TABLE IF EXISTS users CASCADE;

CREATE / DROP SCHEMA: Instantiates or removes a named logical namespace grouping database objects.

CREATE SCHEMA analytics;
DROP SCHEMA analytics CASCADE;

9. DML — Modifying Data

INSERT INTO: Commits new row data into appropriate column structures.

INSERT INTO users (email, username) VALUES ('alice@example.com', 'alice');

INSERT ... ON CONFLICT (UPSERT): Handles key violations by updating fields rather than erroring.

INSERT INTO users (email, username)
VALUES ('alice@example.com', 'alice_new')
ON CONFLICT (email) DO UPDATE SET username = EXCLUDED.username;

UPDATE: Updates attributes of existing entries matching filters.

UPDATE employees SET salary = salary * 1.05 WHERE department = 'Engineering';

DELETE FROM: Removes entries matches a conditional criteria.

DELETE FROM users WHERE active = FALSE;

TRUNCATE: Quick wipe of all row data inside tables without firing triggers.

TRUNCATE TABLE session_logs RESTART IDENTITY CASCADE;

10. Indexes & Performance

CREATE INDEX: Creates a B-Tree index structure to query criteria columns faster.

CREATE INDEX idx_employees_dept ON employees(department);

CREATE UNIQUE INDEX: Configures a unique key constraint to prevent duplicate entries while speeding query lookups.

CREATE UNIQUE INDEX idx_users_email ON users(email);

Composite Index: Indexes multiple search fields together in a leftmost order sequence.

CREATE INDEX idx_emp_dept_sal ON employees(department, salary);

Partial Index: Builds an index coverage area restrictively over matching filter rows.

CREATE INDEX idx_active_users ON users(email) WHERE active = TRUE;

Covering Index: Appends query columns onto the leaf node indices to bypass regular table scan lookup reads.

CREATE INDEX idx_emp_cov ON employees(department) INCLUDE (name, hire_date);

EXPLAIN / EXPLAIN ANALYZE: Returns the computed executing steps parsed by the database query optimizer.

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';

11. Transactions & ACID

BEGIN / COMMIT / ROLLBACK: Executes data updates in isolated, atomic transaction units.

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT; -- Or ROLLBACK; if statements fail

SAVEPOINT: Marks checkpoint boundaries within active transaction blocks to partially revert changes.

BEGIN;
  INSERT INTO orders (id) VALUES (1);
  SAVEPOINT pt1;
  INSERT INTO order_items (order_id) VALUES (1);
  ROLLBACK TO pt1; -- Undoes only items insertion
COMMIT;

SET TRANSACTION ISOLATION LEVEL: Configures ACID isolation options for transaction runtime.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

FOR UPDATE: Row locking mechanism that locks selected records against concurrency updates.

SELECT * FROM inventory WHERE product_id = 10 FOR UPDATE;

ADVISORY LOCK: Application-defined locks monitored using internal database function nodes.

SELECT pg_advisory_lock(12345);
-- ... critical section ...
SELECT pg_advisory_unlock(12345);

12. String Functions

UPPER / LOWER: Changes case casing format of strings.

SELECT UPPER('hello'), LOWER('WORLD');

LENGTH / OCTET_LENGTH: Enumerates character counts or total storage size footprint in bytes.

SELECT LENGTH('Hello World'), OCTET_LENGTH('Hello');

TRIM / LTRIM / RTRIM: Cuts spacing characters bounding string fields.

SELECT TRIM('  hello  '), LTRIM('  hello  '), RTRIM('  hello  ');

SUBSTRING / LEFT / RIGHT: Extracts sub-fragments from target string columns.

SELECT SUBSTRING('Hello World', 7, 5), LEFT('Hello', 2), RIGHT('World', 2);

CONCAT / CONCAT_WS / ||: Concatenates string parameters together.

SELECT 'Hello' || ' ' || 'World';
SELECT CONCAT('Hello', ' ', 'World');
SELECT CONCAT_WS(', ', 'Alice', 'Bob', 'Carol');

REPLACE / POSITION: Coordinates substring replacing and substring character positions.

SELECT POSITION('World' IN 'Hello World');
SELECT REPLACE('foo bar', 'foo', 'baz');

LPAD / RPAD: Fits character width by padding either start or end areas.

SELECT LPAD('42', 5, '0'), RPAD('Hi', 5, '!');

13. Date & Time Functions

NOW / CURRENT_DATE / CURRENT_TIMESTAMP: Assesses active system clock values.

SELECT NOW(), CURRENT_DATE, CURRENT_TIMESTAMP;

EXTRACT: Isolates key date dimensions (like YEAR, MONTH, DOW) from timestamp attributes.

SELECT EXTRACT(YEAR FROM NOW()), EXTRACT(DOW FROM NOW());

DATE_TRUNC: Standardizes timezone stamp intervals downwards.

SELECT DATE_TRUNC('month', NOW());

INTERVAL: Extrapolates temporal addition margins during date math.

SELECT NOW() + INTERVAL '30 days';
SELECT NOW() - INTERVAL '1 year';

DATEDIFF / AGE: Measures duration scopes separating date instances.

SELECT AGE(NOW(), '1990-06-15'::DATE);
SELECT DATEDIFF('2026-12-31', '2026-01-01');

TO_CHAR / DATE_FORMAT: Outlines text configuration outputs representing timestamps.

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD'); -- PostgreSQL
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');  -- MySQL

CAST / ::: coerces string text representations to Date formats.

SELECT '2026-06-29'::DATE;
SELECT CAST('2026-06-29' AS DATE);

14. NULL Handling

IS NULL / IS NOT NULL: Validates whether queries are empty/missing vs defined.

SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

COALESCE: Evaluates parameters left to right, returning the first non-NULL item.

SELECT COALESCE(phone, email, 'N/A') FROM users;

NULLIF: Evaluates down to NULL if compared parameters match up (prevents division by zero errors).

SELECT total / NULLIF(quantity, 0) FROM order_lines;

NULLS FIRST / NULLS LAST: Dictates NULL positional values during ordering operations.

SELECT name FROM employees ORDER BY manager_id NULLS LAST;

15. Views

CREATE VIEW: Creates a dynamic virtual table reference for a query.

CREATE VIEW active_employees AS
SELECT id, name FROM employees WHERE active = TRUE;

CREATE OR REPLACE VIEW / DROP VIEW: Modifies active view queries or drops views entirely.

CREATE OR REPLACE VIEW active_employees AS SELECT id, name, hire_date FROM employees WHERE active = TRUE;
DROP VIEW IF EXISTS active_employees;

CREATE MATERIALIZED VIEW: Persists the view query output physically on disk.

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW: Triggers dynamic data updates for materialized view disk storage.

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Quick Reference

SQL Execution Order

JOIN Quick Reference

A INNER JOIN B      → Only matched rows
A LEFT  JOIN B      → All A rows + NULLs for unmatched B
A RIGHT JOIN B      → All B rows + NULLs for unmatched A
A FULL  JOIN B      → All rows from both, NULLs where no match
A CROSS JOIN B      → Cartesian product of both tables

Common Window Function Patterns

-- Running Total
SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)

-- Moving Average
AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- Lag/Lead
LAG(col, 1)  OVER (ORDER BY date)
LEAD(col, 1) OVER (ORDER BY date)