SQL Cheat Sheet: Quick Reference for All SQL Commands (2026)

According to the Stack Overflow Developer Survey 2025, SQL is the third most-used language overall, with 51.6% of professional developers working with it regularly — more than TypeScript, C#, or Go. It has held a top-5 position every year since the survey began in 2011. Despite the proliferation of NoSQL databases, the DB-Engines ranking shows relational databases occupying 7 of the top 10 positions as of Q1 2026, with PostgreSQL overtaking MySQL for the #2 spot behind Oracle.

The problem most developers face is not that SQL is hard — it is that the full syntax space is enormous, and most people use about 20% of it. This reference covers all of it: the everyday commands you already know, the joins that trip people up, window functions that replace dozens of self-joins, and the query patterns that survive EXPLAIN ANALYZE without regret.

Dialect notes are included where syntax differs meaningfully between PostgreSQL 16, MySQL 8.0+, and SQLite 3.35+.

SELECT — Retrieving Data

The SELECT statement is the entry point for every read query. Its full logical execution order differs from the written order — understanding this explains most WHERE vs HAVING confusion:

-- Basic structure
SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column1
HAVING aggregate_condition
ORDER BY column1 DESC
LIMIT 10 OFFSET 20;

-- Logical execution order (not written order):
-- 1. FROM / JOIN   — identify the source rows
-- 2. WHERE         — filter rows
-- 3. GROUP BY      — group remaining rows
-- 4. HAVING        — filter groups
-- 5. SELECT        — project columns and expressions
-- 6. DISTINCT      — remove duplicates
-- 7. ORDER BY      — sort
-- 8. LIMIT/OFFSET  — paginate

-- Select all (avoid in production — use explicit columns)
SELECT * FROM users;

-- Column aliases
SELECT first_name AS name, created_at AS registered FROM users;

-- Computed columns
SELECT price, quantity, price * quantity AS total FROM order_items;

-- Distinct values
SELECT DISTINCT country FROM users;

-- Conditional expressions
SELECT
  name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'F'
  END AS grade
FROM students;

Filtering with WHERE

-- Comparison operators
WHERE price > 100
WHERE status != 'inactive'   -- or <>
WHERE created_at >= '2026-01-01'

-- Multiple conditions
WHERE country = 'US' AND status = 'active'
WHERE role = 'admin' OR role = 'moderator'
WHERE NOT deleted

-- Range
WHERE price BETWEEN 10 AND 100   -- inclusive on both ends

-- Pattern matching
WHERE name LIKE 'J%'       -- starts with J (case-sensitive in MySQL, case-sensitive by default in PostgreSQL)
WHERE name ILIKE 'j%'      -- case-insensitive (PostgreSQL only)
WHERE email LIKE '%@gmail.com'

-- IN list
WHERE country IN ('US', 'CA', 'GB')
WHERE id NOT IN (SELECT user_id FROM banned_users)

-- NULL handling
WHERE email IS NULL
WHERE email IS NOT NULL

-- EXISTS (often faster than IN for large subqueries)
WHERE EXISTS (
  SELECT 1 FROM orders WHERE orders.user_id = users.id
)

JOINs — Combining Tables

JOINs are the most commonly misunderstood part of SQL. The visual below maps each JOIN type to its output, but the key insight is that all JOINs work the same way internally — they differ only in what happens to unmatched rows.

-- INNER JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (users who may or may not have orders)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Find users with NO orders (anti-join pattern)
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Multi-table join
SELECT u.name, p.name AS product, oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.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;

Aggregation: GROUP BY, HAVING, and Aggregate Functions

-- Core aggregate functions
SELECT
  COUNT(*)                AS total_rows,
  COUNT(email)            AS rows_with_email,   -- NULLs not counted
  COUNT(DISTINCT country) AS unique_countries,
  SUM(amount)             AS total_revenue,
  AVG(amount)             AS avg_order,
  MIN(created_at)         AS first_order,
  MAX(created_at)         AS last_order
FROM orders;

-- GROUP BY — one output row per group
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
ORDER BY users DESC;

-- GROUP BY multiple columns
SELECT country, status, COUNT(*) AS cnt
FROM users
GROUP BY country, status;

-- HAVING — filter groups (not individual rows)
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;   -- only high-value customers

-- HAVING with COUNT
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) >= 5;

-- WHERE + GROUP BY + HAVING together
SELECT country, COUNT(*) AS active_users
FROM users
WHERE created_at >= '2025-01-01'   -- WHERE runs first (filters rows)
GROUP BY country
HAVING COUNT(*) > 100;             -- HAVING runs after GROUP BY (filters groups)

Window Functions: The Most Underused Feature in SQL

Window functions perform calculations across a set of related rows without collapsing them into one row — unlike GROUP BY. They are available in PostgreSQL 8.4+, MySQL 8.0+, and SQLite 3.25+. According to the Mode Analytics 2024 SQL Report, window functions are the single most requested SQL skill in data engineering job postings, yet only 34% of SQL users report feeling comfortable with them.

-- Syntax: function() OVER (PARTITION BY ... ORDER BY ...)

-- ROW_NUMBER: unique sequential number per partition
SELECT
  user_id,
  order_id,
  created_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_seq
FROM orders;

-- RANK vs DENSE_RANK
-- RANK: 1, 2, 2, 4 (gap after tie)
-- DENSE_RANK: 1, 2, 2, 3 (no gap)
SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

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

-- Running total per user (partition resets per customer)
SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS user_running_total
FROM orders;

-- LAG / LEAD: access previous/next row's value
SELECT
  order_date,
  amount,
  LAG(amount)  OVER (ORDER BY order_date) AS prev_day_amount,
  LEAD(amount) OVER (ORDER BY order_date) AS next_day_amount,
  amount - LAG(amount) OVER (ORDER BY order_date) AS delta
FROM daily_sales;

-- NTILE: split rows into N buckets (e.g. quartiles)
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_totals;

-- Deduplication: keep only 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
) t
WHERE rn = 1;

CTEs: Common Table Expressions with WITH

CTEs were standardized in SQL:1999 and are supported by all modern databases. They define named temporary result sets scoped to a single query. Their primary value is readability: a 5-subquery monstrosity becomes a readable sequence of named steps.

-- Basic CTE
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE status = 'active' AND created_at >= '2025-01-01'
)
SELECT au.name, COUNT(o.id) AS orders
FROM active_users au
LEFT JOIN orders o ON au.id = o.user_id
GROUP BY au.id, au.name;

-- Multiple CTEs (chain them with commas)
WITH
  revenue AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
  ),
  top_customers AS (
    SELECT user_id, total
    FROM revenue
    WHERE total > 5000
  )
SELECT u.name, tc.total
FROM top_customers tc
JOIN users u ON u.id = tc.user_id
ORDER BY tc.total DESC;

-- Recursive CTE: org chart (manager → employee hierarchy)
WITH RECURSIVE org_chart AS (
  -- Base case: CEO (no manager)
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees of each level
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT name, depth
FROM org_chart
ORDER BY depth, name;

INSERT, UPDATE, DELETE, and UPSERT

-- INSERT
INSERT INTO users (name, email, created_at)
VALUES ('Alice', '[email protected]', NOW());

-- Multi-row INSERT (single round-trip, much faster than looping)
INSERT INTO products (name, price, category)
VALUES
  ('Widget A', 9.99, 'hardware'),
  ('Widget B', 14.99, 'hardware'),
  ('Gadget X', 49.99, 'electronics');

-- INSERT from SELECT
INSERT INTO archive_orders
SELECT * FROM orders WHERE created_at < '2024-01-01';

-- UPDATE
UPDATE users
SET status = 'inactive', updated_at = NOW()
WHERE last_login < NOW() - INTERVAL '90 days';

-- UPDATE with JOIN (PostgreSQL syntax)
UPDATE orders o
SET status = 'cancelled'
FROM users u
WHERE o.user_id = u.id AND u.fraud_flag = true;

-- DELETE
DELETE FROM sessions WHERE expires_at < NOW();

-- DELETE with JOIN (PostgreSQL)
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id AND o.status = 'cancelled';

-- UPSERT: PostgreSQL (INSERT ... ON CONFLICT)
INSERT INTO user_scores (user_id, score, updated_at)
VALUES (42, 1500, NOW())
ON CONFLICT (user_id)
DO UPDATE SET score = EXCLUDED.score, updated_at = EXCLUDED.updated_at;

-- UPSERT: MySQL (INSERT ... ON DUPLICATE KEY UPDATE)
INSERT INTO user_scores (user_id, score, updated_at)
VALUES (42, 1500, NOW())
ON DUPLICATE KEY UPDATE
  score = VALUES(score),
  updated_at = VALUES(updated_at);

DDL: Tables, Indexes, and Constraints

-- CREATE TABLE
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,            -- PostgreSQL auto-increment
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  status     VARCHAR(20) DEFAULT 'active'
               CHECK (status IN ('active', 'inactive', 'banned')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- MySQL equivalent (AUTO_INCREMENT instead of SERIAL)
CREATE TABLE users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL,
  ...
);

-- Foreign key
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount > 0),
  status     VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created ON orders(created_at DESC);

-- Composite index (column order matters — leftmost prefix rule)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index (PostgreSQL) — only index active users
CREATE INDEX idx_users_active ON users(email) WHERE status = 'active';

-- ALTER TABLE
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE(email);

-- DROP (irreversible)
DROP TABLE temp_data;
DROP TABLE IF EXISTS temp_data;   -- no error if doesn't exist
TRUNCATE TABLE sessions;          -- empty table, keep structure

-- Transactions
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK; to undo both changes

String, Date, and Numeric Functions

-- String functions
SELECT UPPER(name), LOWER(email), LENGTH(name) FROM users;
SELECT TRIM('  hello  ');                -- 'hello'
SELECT LTRIM('  hello'), RTRIM('hello ');
SELECT SUBSTRING(name, 1, 3) FROM users; -- first 3 chars
SELECT REPLACE(phone, '-', '') FROM users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- PostgreSQL string concat: first_name || ' ' || last_name
SELECT POSITION('@' IN email) FROM users;  -- character position

-- Date / time functions
SELECT NOW();                              -- current timestamp
SELECT CURRENT_DATE;                       -- date only
SELECT DATE_TRUNC('month', created_at) FROM orders;  -- PostgreSQL
SELECT DATE_FORMAT(created_at, '%Y-%m') FROM orders; -- MySQL

-- Date arithmetic
SELECT created_at + INTERVAL '7 days' FROM orders;  -- PostgreSQL
SELECT DATE_ADD(created_at, INTERVAL 7 DAY) FROM orders; -- MySQL

-- Extract parts
SELECT EXTRACT(YEAR FROM created_at)  AS yr FROM orders;
SELECT EXTRACT(MONTH FROM created_at) AS mo FROM orders;

-- Numeric functions
SELECT ROUND(3.14159, 2);     -- 3.14
SELECT CEIL(4.1);             -- 5
SELECT FLOOR(4.9);            -- 4
SELECT ABS(-42);              -- 42
SELECT MOD(17, 5);            -- 2 (remainder)

-- Null handling
SELECT COALESCE(phone, email, 'no contact') FROM users;  -- first non-null
SELECT NULLIF(status, 'unknown') FROM users;             -- NULL if equal

Query Optimization: What EXPLAIN Tells You

The PostgreSQL documentation is clear: never guess whether a query is slow — run EXPLAIN ANALYZE and read the actual execution plan. A query returning the right results in 40 seconds can often be fixed in 10 minutes once you see it performing a 5-million-row sequential scan that a single index would eliminate.

-- PostgreSQL: EXPLAIN ANALYZE (runs the query and shows real timing)
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- Output nodes to look for:
-- Seq Scan: full table scan — bad on large tables
-- Index Scan: using an index — good
-- Index Only Scan: no heap access needed — best
-- Hash Join / Merge Join / Nested Loop: join strategy
-- cost=X..Y: estimated cost (X=startup, Y=total)
-- actual time=X..Y: real time in ms (ANALYZE mode only)
-- rows=N: estimated vs actual row count

-- EXPLAIN without ANALYZE (no actual execution — safe for slow queries)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- MySQL
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 42;

-- SQLite
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42;

-- Common anti-patterns that break index usage:
-- BAD: function on indexed column
WHERE YEAR(created_at) = 2026
-- GOOD: range condition
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

-- BAD: leading wildcard
WHERE name LIKE '%smith'
-- GOOD: trailing wildcard (can use B-tree index)
WHERE name LIKE 'smith%'

-- BAD: implicit type conversion
WHERE user_id = '42'   -- id is INT, string forces cast
-- GOOD:
WHERE user_id = 42

Subqueries vs JOINs vs CTEs: When to Use Each

Modern query optimizers (PostgreSQL, MySQL 8.0+) will often produce the same execution plan for a correlated subquery and an equivalent JOIN. Still, the conventional wisdom holds for readability: prefer JOINs for simple lookups, CTEs for multi-step logic, and subqueries in HAVING or WHERE when you need a computed threshold.

-- Scalar subquery (single value)
SELECT name,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;
-- Better rewritten as JOIN for large tables:
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Subquery in WHERE
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);  -- above average price

-- Derived table (subquery in FROM)
SELECT dept, avg_salary
FROM (
  SELECT department AS dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) dept_averages
WHERE avg_salary > 80000;

-- EXISTS (efficient: stops after first match)
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.amount > 500
);

FAQ