SQL Cheat Sheet: Queries, Joins, Aggregates & CTEs

Quick reference tables

SELECT syntax

Filtering — WHERE

Conditional expressions

Joins

Aggregations

Set operations

Data types

Constraints

DDL — schema changes

DML — data changes

Upsert (insert or update)

Indexes

Detailed sections

SELECT — expressions and computed columns

-- Arithmetic in SELECT
SELECT price, quantity, price * quantity AS total FROM order_items;

-- CASE WHEN — if/else per row
SELECT
  name,
  score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'F'
  END AS grade
FROM students;

-- COALESCE — first non-NULL wins
SELECT name, COALESCE(display_name, username, 'Anonymous') AS label FROM users;

-- NULLIF — avoid divide-by-zero
SELECT total_sales / NULLIF(num_orders, 0) AS avg_order_value FROM sales_summary;

-- String functions (standard SQL)
SELECT UPPER(name), LOWER(email), LENGTH(bio) FROM users;
SELECT TRIM('  hello  ');       -- 'hello'
SELECT SUBSTRING(name, 1, 3);  -- first 3 chars
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Date functions
SELECT CURRENT_DATE, CURRENT_TIMESTAMP;
SELECT EXTRACT(YEAR FROM created_at) AS year FROM orders;

JOIN patterns

-- INNER JOIN: only rows with a match on both sides
SELECT users.name, orders.total
FROM users
INNER JOIN orders ON orders.user_id = users.id;

-- LEFT JOIN: all users, even those with no orders
SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON orders.user_id = users.id
GROUP BY users.id, users.name;

-- Multi-table join
SELECT u.name, o.id AS order_id, p.name AS product
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

-- Self-join: employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- FULL OUTER JOIN: show unmatched rows from both sides
SELECT a.id AS a_id, b.id AS b_id
FROM table_a a
FULL OUTER JOIN table_b b ON a.key = b.key
WHERE a.id IS NULL OR b.id IS NULL;  -- show only non-matching rows

Subqueries

-- Scalar subquery: returns one value
SELECT name, salary,
  (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

-- Subquery in WHERE
SELECT name FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- IN subquery
SELECT name FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders WHERE total > 1000);

-- EXISTS — often faster than IN for large sets
SELECT name FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000
);

-- Correlated subquery: references outer query
SELECT name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- Derived table (subquery as a FROM source)
SELECT dept, avg_sal FROM (
  SELECT department AS dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
) dept_stats
WHERE avg_sal > 60000;

CTEs — Common Table Expressions

-- Basic CTE: readable named subquery
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
user_order_counts AS (
  SELECT user_id, COUNT(*) AS orders
  FROM orders
  GROUP BY user_id
)
SELECT u.name, COALESCE(o.orders, 0) AS total_orders
FROM active_users u
LEFT JOIN user_order_counts o ON o.user_id = u.id
ORDER BY total_orders DESC;

-- Recursive CTE: walk a hierarchy (org chart, category tree)
WITH RECURSIVE category_tree AS (
  -- Base: top-level categories
  SELECT id, name, parent_id, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive: children of each row
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c
  JOIN category_tree t ON c.parent_id = t.id
)
SELECT REPEAT('  ', depth) || name AS indented_name, depth
FROM category_tree
ORDER BY depth, name;

Window functions

Window functions compute values across a set of rows related to the current row — without collapsing them like GROUP BY does.

-- ROW_NUMBER: unique sequential rank per partition
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

-- RANK vs DENSE_RANK
-- RANK skips numbers after ties (1,2,2,4); DENSE_RANK does not (1,2,2,3)
SELECT name, score,
  RANK() OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

-- Running total
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Moving average (last 7 rows)
SELECT
  day,
  revenue,
  AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7
FROM daily_revenue;

-- LAG / LEAD: access previous/next row
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

-- Get the most recent order per user
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;

Transactions

-- Basic transaction
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

-- Rollback on error
BEGIN;
  DELETE FROM orders WHERE user_id = 99;
  -- something went wrong...
ROLLBACK;

-- Savepoint: partial rollback
BEGIN;
  INSERT INTO log (event) VALUES ('step 1');
  SAVEPOINT step1;

  INSERT INTO log (event) VALUES ('step 2');
  -- oops, undo only step 2
  ROLLBACK TO SAVEPOINT step1;

  INSERT INTO log (event) VALUES ('step 2 corrected');
COMMIT;

Isolation levels (from lowest to highest isolation):

-- Set isolation level (PostgreSQL / SQL Server)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
  -- ...
COMMIT;

Schema design patterns

-- One-to-many: users → orders
CREATE TABLE users (
  id      SERIAL PRIMARY KEY,
  name    VARCHAR(100) NOT NULL,
  email   VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id        SERIAL PRIMARY KEY,
  user_id   INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total     DECIMAL(10, 2) NOT NULL,
  status    VARCHAR(50) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Many-to-many: posts ↔ tags (junction table)
CREATE TABLE posts (
  id    SERIAL PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE tags (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INT REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  INT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

-- Query: all tags for a post
SELECT t.name
FROM tags t
JOIN post_tags pt ON pt.tag_id = t.id
WHERE pt.post_id = 42;

Cross-dialect notes

Key syntax differences between the major SQL databases:

Related: PostgreSQL Cheat Sheet | Debug a Slow SQL Query

Related Articles

Deepen your understanding with these curated continuations.

View All Articles

Cheatsheet 5 min read

PostgreSQL Cheat Sheet: SQL, Indexes, EXPLAIN & psql

Complete PostgreSQL reference — psql commands, CRUD queries, joins, indexes, EXPLAIN ANALYZE, CTEs, window functions, JSONB operators, and admin queries.

Vishnu Dec 28, 2025

HowTo 5 min read

How to Debug a Slow SQL Query in PostgreSQL

Step-by-step: find slow queries with pg_stat_statements, read EXPLAIN ANALYZE output, identify missing indexes, fix N+1 queries, and diagnose lock contention.

Vishnu Dec 31, 2025

Cheatsheet 5 min read

jq Cheat Sheet: Filter, Transform, and Query JSON from the Terminal

Complete jq reference — basic filters, pipes, array/object operations, conditionals, string interpolation, select, map, reduce, and real DevOps workflows.

Cobie Apr 07, 2026