SQL Cheatsheet

SELECT col1, col2 FROM users WHERE active = true ORDER BY created_at DESC LIMIT 20;

SELECT DISTINCT country FROM users;       -- unique values
SELECT COUNT(*) FROM users;               -- total rows
SELECT NULL AS placeholder;               -- NULL value

WHERE age >= 18 AND country IN ('US', 'CA')
WHERE name LIKE 'Al%'                  -- starts with Al
WHERE name ILIKE 'al%'                 -- Postgres case-insensitive
WHERE email IS NULL
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'
WHERE id IN (1, 2, 3)
WHERE id = ANY('{1,2,3}'::int[])       -- Postgres array form

INNER JOIN (default)

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

Returns only rows where both tables match. Drops users with no orders.

LEFT JOIN

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

Keeps all users, even those with zero orders (COUNT returns 0).

Self-join (employees and managers)

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

SELECT
  country,
  COUNT(*) AS users,
  AVG(age) AS avg_age,
  SUM(total_spent) AS revenue
FROM users
WHERE active = true
GROUP BY country
HAVING COUNT(*) > 10
ORDER BY revenue DESC;

Functions: COUNT, SUM, AVG, MIN, MAX, STRING_AGG (postgres), GROUP_CONCAT (mysql).

Rank within groups

SELECT
  name,
  country,
  total_spent,
  RANK() OVER (PARTITION BY country ORDER BY total_spent DESC) AS country_rank
FROM users;

Running totals

SELECT
  date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY date) AS running_total
FROM daily_stats;

Previous-row comparison

SELECT
  date,
  views,
  views - LAG(views, 1) OVER (ORDER BY date) AS delta
FROM analytics;

WITH recent AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
),
high_value AS (
  SELECT user_id, SUM(total) AS spent FROM recent GROUP BY user_id HAVING SUM(total) > 500
)
SELECT u.name, hv.spent
FROM users u
JOIN high_value hv ON hv.user_id = u.id;

CTEs read top-to-bottom. Use them to break complex queries into named steps.

Recursive CTE (org hierarchy)

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, org.level + 1
  FROM employees e
  JOIN org ON e.manager_id = org.id
)
SELECT * FROM org;

INSERT INTO users (name, email) VALUES ('Alice', 'a@x.com');

-- Upsert (Postgres)
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- Upsert (MySQL)
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year';

DELETE FROM users WHERE active = false AND created_at < '2020-01-01';

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);  -- composite

-- Postgres-specific
CREATE INDEX CONCURRENTLY idx_x ON table(col);   -- no locking
CREATE INDEX idx_x ON table(col) WHERE active = true;  -- partial index
CREATE INDEX idx_lower_email ON users(LOWER(email));   -- functional index