SQL Cheat Sheet: Every Query You Actually Need

The queries you reach for every day — and the ones you Google every time. This reference covers everything from basic SELECT to window functions and query optimization.

Quick reference

The 20 patterns that cover 90% of daily SQL work.

SELECT basics

-- All columns
SELECT * FROM users;

-- Specific columns with alias
SELECT
  id,
  first_name || ' ' || last_name AS full_name,
  email,
  created_at
FROM users;

-- Computed columns
SELECT
  price,
  quantity,
  price * quantity AS total,
  ROUND(price * 0.20, 2) AS vat
FROM order_items;

WHERE conditions

-- Comparison
SELECT * FROM users WHERE age >= 18;

-- Multiple conditions
SELECT * FROM users WHERE country = 'US' AND active = true;

-- IN list
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');

-- NOT IN
SELECT * FROM products WHERE category_id NOT IN (3, 7, 12);

-- BETWEEN (inclusive)
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- LIKE pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE name ILIKE '%shirt%';  -- case-insensitive (PostgreSQL)

-- NULL checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM orders WHERE shipped_at IS NOT NULL;

-- CASE expression
SELECT
  id,
  total,
  CASE
    WHEN total >= 1000 THEN 'platinum'
    WHEN total >= 500  THEN 'gold'
    WHEN total >= 100  THEN 'silver'
    ELSE 'bronze'
  END AS tier
FROM orders;

ORDER BY and LIMIT

-- Sort descending, then ascending
SELECT * FROM posts ORDER BY created_at DESC, title ASC;

-- NULLS LAST (PostgreSQL/SQLite)
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;

-- Pagination
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;   -- page 3, 20 per page

-- Top N per group (PostgreSQL — use window function)
SELECT * FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
  FROM products
) ranked
WHERE rn <= 3;

Aggregate functions

-- Basic aggregates
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_value,
  MIN(created_at)   AS first_order,
  MAX(created_at)   AS last_order
FROM orders;

-- GROUP BY
SELECT
  country,
  COUNT(*) AS users,
  AVG(age) AS avg_age
FROM users
GROUP BY country
ORDER BY users DESC;

-- HAVING (filter after aggregation)
SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(total) AS revenue
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5 AND SUM(total) > 500
ORDER BY revenue DESC;

JOINs

-- INNER JOIN — rows that match in both tables
SELECT o.id, u.email, o.total
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- LEFT JOIN — all orders, even if user deleted
SELECT o.id, u.email, o.total
FROM orders o
LEFT JOIN users u ON o.user_id = u.id;

-- RIGHT JOIN — all users, even without orders
SELECT u.email, COUNT(o.id) AS order_count
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id
GROUP BY u.id;

-- Multiple joins
SELECT
  o.id,
  u.email,
  p.name AS product,
  oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id;

-- Self join (hierarchy — employee and their manager)
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- CROSS JOIN (cartesian product — use carefully)
SELECT a.color, b.size
FROM colors a
CROSS JOIN sizes b;

JOIN visual guide

Table A    Table B
 1          2
 2          3
 3          4

INNER JOIN → rows 2, 3     (match in both)
LEFT JOIN  → rows 1, 2, 3  (all of A, nulls for missing B)
RIGHT JOIN → rows 2, 3, 4  (all of B, nulls for missing A)
FULL JOIN  → rows 1, 2, 3, 4 (everything)

Subqueries

-- WHERE subquery
SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE active = true
);

-- Correlated subquery (run once per outer row)
SELECT
  u.id,
  u.email,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

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

-- Scalar subquery in SELECT
SELECT
  p.name,
  p.price,
  (SELECT AVG(price) FROM products) AS avg_price,
  p.price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products p;

CTEs (Common Table Expressions)

-- Basic CTE
WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
)
SELECT user_id, COUNT(*), SUM(total)
FROM recent_orders
GROUP BY user_id;

-- Multiple CTEs
WITH
  active_users AS (
    SELECT id FROM users WHERE active = true
  ),
  their_orders AS (
    SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users)
  )
SELECT status, COUNT(*), SUM(total)
FROM their_orders
GROUP BY status;

-- Recursive CTE (walk a hierarchy)
WITH RECURSIVE org_chart AS (
  -- Anchor: top-level employees (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: employees whose manager is already in CTE
  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, name;

Window functions

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

-- Syntax
function_name() OVER (
  PARTITION BY col   -- optional: separate groups
  ORDER BY col       -- optional: row order within window
  ROWS/RANGE ...     -- optional: frame bounds
)

-- ROW_NUMBER — unique rank per group
SELECT
  id,
  user_id,
  created_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
FROM orders;

-- RANK and DENSE_RANK — ties handled differently
SELECT
  student,
  score,
  RANK()       OVER (ORDER BY score DESC) AS rank,          -- gaps on tie
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank     -- no gaps
FROM scores;

-- Running total
SELECT
  id,
  amount,
  SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM transactions;

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

-- LAG/LEAD — previous/next row value
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_change
FROM daily_revenue;

-- FIRST_VALUE / LAST_VALUE
SELECT
  product_id,
  sale_date,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY product_id ORDER BY sale_date
  ) AS first_sale_amount
FROM sales;

INSERT, UPDATE, DELETE

-- INSERT single row
INSERT INTO users (name, email, country) VALUES ('Ana', 'ana@example.com', 'ME');

-- INSERT multiple rows
INSERT INTO tags (name) VALUES ('javascript'), ('python'), ('go'), ('php');

-- INSERT from SELECT
INSERT INTO archive_orders (id, user_id, total, created_at)
SELECT id, user_id, total, created_at
FROM orders
WHERE created_at < NOW() - INTERVAL '1 year';

-- INSERT OR IGNORE duplicates (SQLite/MySQL)
INSERT IGNORE INTO subscribers (email) VALUES ('user@example.com');

-- UPSERT (PostgreSQL ON CONFLICT)
INSERT INTO page_views (page, date, views)
VALUES ('/home', '2024-07-13', 1)
ON CONFLICT (page, date)
DO UPDATE SET views = page_views.views + EXCLUDED.views;

-- UPDATE specific rows
UPDATE products
SET price = price * 1.10, updated_at = NOW()
WHERE category_id = 5 AND active = true;

-- UPDATE with JOIN (PostgreSQL)
UPDATE orders o
SET status = 'shipped'
FROM shipments s
WHERE o.id = s.order_id AND s.dispatched_at IS NOT NULL;

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

-- DELETE with subquery
DELETE FROM notifications
WHERE user_id IN (SELECT id FROM users WHERE active = false);

-- RETURNING (PostgreSQL — get affected rows back)
INSERT INTO posts (title, body) VALUES ('Hello', 'World')
RETURNING id, created_at;

UPDATE users SET last_login = NOW() WHERE id = 42
RETURNING id, last_login;

DDL — Tables and indexes

-- CREATE TABLE
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,              -- auto-increment (PostgreSQL)
  -- or: id INT AUTO_INCREMENT PRIMARY KEY   -- MySQL
  -- or: id INTEGER PRIMARY KEY AUTOINCREMENT -- SQLite
  email      TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  age        INT CHECK (age >= 0),
  country    CHAR(2) DEFAULT 'US',
  active     BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

-- ADD COLUMN
ALTER TABLE users ADD COLUMN phone TEXT;

-- RENAME COLUMN
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- DROP COLUMN
ALTER TABLE users DROP COLUMN phone_number;

-- CREATE INDEX — speed up WHERE and JOIN
CREATE INDEX idx_users_email ON users(email);

-- Partial index (index only active users)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Composite index (multi-column WHERE)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- UNIQUE constraint
ALTER TABLE products ADD CONSTRAINT uq_products_sku UNIQUE (sku);

-- FOREIGN KEY
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- DROP TABLE
DROP TABLE IF EXISTS temp_import;

NULL handling

-- NULL propagates through arithmetic
SELECT NULL + 5;     -- NULL
SELECT NULL = NULL;  -- NULL (not TRUE!)

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

-- COALESCE — first non-NULL value
SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name FROM users;

-- NULLIF — return NULL if two values are equal (avoid divide by zero)
SELECT total / NULLIF(quantity, 0) AS unit_price FROM order_items;

-- IFNULL / NVL (MySQL/Oracle) — two-argument version of COALESCE
SELECT IFNULL(phone, 'N/A') FROM users;    -- MySQL

String functions

-- Concatenation
SELECT first_name || ' ' || last_name AS name FROM users;   -- standard SQL
SELECT CONCAT(first_name, ' ', last_name) FROM users;       -- MySQL/PostgreSQL

-- Length
SELECT LENGTH('hello');    -- 5 (MySQL: characters, PostgreSQL: bytes for TEXT)
SELECT CHAR_LENGTH(name);  -- always character count

-- Case
SELECT UPPER(email), LOWER(name) FROM users;

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

-- Substring
SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username FROM users;

-- Replace
SELECT REPLACE(phone, '-', '') AS phone_digits FROM users;

-- Pattern matching
SELECT * FROM products WHERE name LIKE 'iPhone%';    -- starts with
SELECT * FROM posts WHERE slug LIKE '%javascript%';   -- contains

-- REGEXP (MySQL) / ~ (PostgreSQL)
SELECT * FROM users WHERE email ~ '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$';

Date and time

-- Current timestamp
SELECT NOW();              -- PostgreSQL, MySQL
SELECT CURRENT_TIMESTAMP;  -- Standard SQL
SELECT GETDATE();          -- SQL Server

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

-- Extract parts
SELECT
  EXTRACT(YEAR FROM created_at)  AS year,
  EXTRACT(MONTH FROM created_at) AS month,
  EXTRACT(DOW FROM created_at)   AS day_of_week   -- 0=Sunday
FROM orders;

-- Truncate to period
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

-- Format (PostgreSQL)
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI') FROM orders;

-- Convert timezone
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Belgrade' FROM events;

-- Age / difference
SELECT AGE(NOW(), birth_date) AS user_age FROM users;                -- PostgreSQL
SELECT DATEDIFF(NOW(), birth_date) / 365 AS age_years FROM users;   -- MySQL

Transactions

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

-- Rollback on error
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
-- If stock went negative, undo
ROLLBACK;  -- or COMMIT if all good

-- SAVEPOINT — partial rollback
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 99.99);
SAVEPOINT order_saved;
INSERT INTO order_items (order_id, product_id) VALUES (LASTVAL(), 9999); -- bad id
ROLLBACK TO SAVEPOINT order_saved;   -- undo only the items insert
-- order is still there
COMMIT;

Useful patterns

Deduplication — keep one row per group

-- PostgreSQL: delete duplicates, keep lowest id
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY email
);

Pivot — rows to columns

-- Aggregate with CASE
SELECT
  month,
  SUM(CASE WHEN product = 'A' THEN revenue END) AS product_a,
  SUM(CASE WHEN product = 'B' THEN revenue END) AS product_b
FROM monthly_revenue
GROUP BY month;

Consecutive date gaps

-- Find gaps in a date series using LAG
WITH ordered AS (
  SELECT date, LAG(date) OVER (ORDER BY date) AS prev_date
  FROM calendar_events
)
SELECT prev_date + 1 AS gap_start, date - 1 AS gap_end
FROM ordered
WHERE date > prev_date + 1;

Running top percentile

SELECT *
FROM (
  SELECT
    *,
    PERCENT_RANK() OVER (ORDER BY revenue DESC) AS pct_rank
  FROM customers
) r
WHERE pct_rank <= 0.10;   -- top 10%

Performance tips

-- See the query plan
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;  -- actually runs it

-- Index the columns you filter and join on
CREATE INDEX idx_orders_user ON orders(user_id);

-- Prefer EXISTS over COUNT for existence checks
-- Slow:
SELECT * FROM users WHERE (SELECT COUNT(*) FROM orders WHERE user_id = users.id) > 0;
-- Fast:
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

-- Avoid SELECT * in production — only fetch columns you need
-- Avoid functions on indexed columns in WHERE (prevents index use)
-- Slow: WHERE YEAR(created_at) = 2024
-- Fast: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- Use LIMIT when you only need a few rows
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

6 common mistakes

1. UPDATE or DELETE without WHERE Always double-check: UPDATE users SET active = false affects every row. Test with a SELECT using the same WHERE clause first.

2. Using = to compare NULL WHERE deleted_at = NULL returns 0 rows. Use WHERE deleted_at IS NULL.

3. Implicit type conversion hiding bugs WHERE id = '42abc' may coerce and return wrong results. Always match types.

4. GROUP BY without all non-aggregate columns Standard SQL requires every non-aggregate SELECT column to appear in GROUP BY. MySQL's ONLY_FULL_GROUP_BY mode enforces this — enable it.

5. N+1 queries — running a query per row Don't loop and query inside application code. Use a single JOIN or IN list instead.

6. Missing indexes on foreign keys Databases don't automatically index foreign key columns. Add them manually or every JOIN on that column does a full table scan.

6 frequently asked questions

What is the difference between WHERE and HAVING? WHERE filters rows before aggregation. HAVING filters after aggregation. Use HAVING only with GROUP BY or aggregate functions.

What is the difference between INNER JOIN and LEFT JOIN? INNER JOIN returns rows only when there is a match in both tables. LEFT JOIN returns all rows from the left table; columns from the right table are NULL when there is no match.

What is a CTE and when should I use it? A CTE (Common Table Expression) is a named temporary result set defined with WITH. Use it to make complex queries more readable, avoid repeating subqueries, or write recursive queries.

What is the difference between UNION and UNION ALL? UNION deduplicates rows (slower — has to sort and compare). UNION ALL keeps all rows including duplicates (faster — use this unless you need deduplication).

When should I use a subquery vs a JOIN? JOINs are usually faster — the query optimiser can choose the best join strategy. Correlated subqueries run once per outer row, which can be slow on large tables. Use EXISTS instead of IN with subqueries for better performance on large sets.

What is the difference between CHAR, VARCHAR, and TEXT? CHAR(n) is fixed-length and right-pads with spaces — use for codes like country codes. VARCHAR(n) is variable-length up to n characters — use when you know the max length. TEXT is unlimited length (PostgreSQL/MySQL) — use for long content. Performance difference is negligible in modern databases; choose based on semantics.