SQL Cheat Sheet 2026: Essential Queries Every Developer Needs

-- Exact match
SELECT * FROM users WHERE status = 'active';
 
-- Comparison operators
SELECT * FROM products WHERE price > 50.00;
SELECT * FROM orders WHERE created_at >= '2026-01-01';
 
-- Multiple conditions
SELECT * FROM users
WHERE status = 'active'
  AND created_at >= '2026-01-01';
 
-- OR conditions
SELECT * FROM products
WHERE category = 'electronics'
   OR category = 'accessories';
 
-- IN shorthand (cleaner than multiple ORs)
SELECT * FROM products
WHERE category IN ('electronics', 'accessories', 'gadgets');
 
-- Pattern matching with LIKE
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE name LIKE 'Pro%';
 
-- NULL checks (= will not work for NULL)
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;
 
-- Range with BETWEEN
SELECT * FROM orders
WHERE total BETWEEN 100 AND 500;

-- Sort ascending (default)
SELECT * FROM products ORDER BY price;
 
-- Sort descending
SELECT * FROM products ORDER BY price DESC;
 
-- Multiple sort columns
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;
 
-- Limit results
SELECT * FROM products ORDER BY price DESC LIMIT 10;
 
-- Pagination with OFFSET
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- Page 3 (20 items per page)

SELECT
  orders.id AS order_id,
  users.first_name,
  users.email,
  orders.total,
  orders.created_at
FROM orders
INNER JOIN users ON orders.user_id = users.id
WHERE orders.total > 100
ORDER BY orders.created_at DESC;

-- Find users who have never placed an order
SELECT
  users.id,
  users.email,
  orders.id AS order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;

-- RIGHT JOIN: all rows from right table, matched from left
SELECT
  departments.name AS department,
  employees.name AS employee
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.id;
 
-- FULL OUTER JOIN: all rows from both tables
SELECT
  users.email,
  subscriptions.plan
FROM users
FULL OUTER JOIN subscriptions ON users.id = subscriptions.user_id;

-- CROSS JOIN: every combination (cartesian product)
SELECT colors.name, sizes.name
FROM colors
CROSS JOIN sizes;
 
-- Self JOIN: join a table to itself
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Count orders per user
SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(total) AS total_spent,
  AVG(total) AS avg_order_value,
  MAX(total) AS largest_order
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC;
 
-- Count users by signup month
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS signups
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Users who have placed more than 5 orders
SELECT
  user_id,
  COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;
 
-- Categories with average price above 100
SELECT
  category,
  AVG(price) AS avg_price,
  COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING AVG(price) > 100
ORDER BY avg_price DESC;

-- Find users whose orders exceed the average order total
SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE total > (SELECT AVG(total) FROM orders)
);
 
-- Correlated subquery: reference outer query
SELECT
  p.name,
  p.price,
  p.category
FROM products p
WHERE p.price > (
  SELECT AVG(p2.price)
  FROM products p2
  WHERE p2.category = p.category
);
 
-- Subquery in SELECT (scalar subquery)
SELECT
  u.first_name,
  u.email,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;
 
-- EXISTS: check if related rows exist
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
  AND o.created_at >= '2026-01-01'
);

-- Basic CTE
WITH active_users AS (
  SELECT id, first_name, email
  FROM users
  WHERE status = 'active'
    AND last_login >= '2026-01-01'
)
SELECT
  au.first_name,
  au.email,
  COUNT(o.id) AS order_count
FROM active_users au
LEFT JOIN orders o ON au.id = o.user_id
GROUP BY au.id, au.first_name, au.email;
 
-- Multiple CTEs
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY DATE_TRUNC('month', created_at)
),
monthly_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND(
      (revenue - LAG(revenue) OVER (ORDER BY month))
      / LAG(revenue) OVER (ORDER BY month) * 100, 2
    ) AS growth_pct
  FROM monthly_revenue
)
SELECT * FROM monthly_growth
ORDER BY month DESC;
 
-- Recursive CTE: org chart traversal
WITH RECURSIVE org_tree 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, ot.depth + 1
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY depth, name;

-- ROW_NUMBER: assign sequential numbers
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;
 
-- ROW_NUMBER with PARTITION: rank within each group
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees;
 
-- RANK and DENSE_RANK: handle ties differently
SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM leaderboard;
-- RANK: 1, 2, 2, 4 (skips 3)
-- DENSE_RANK: 1, 2, 2, 3 (no skip)
 
-- Running totals with SUM
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;
 
-- Moving average
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_day_avg
FROM daily_revenue;
 
-- LAG and LEAD: access previous/next rows
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_stats;

-- INSERT single row
INSERT INTO users (first_name, last_name, email, status)
VALUES ('Jane', 'Doe', 'jane@example.com', 'active');
 
-- INSERT multiple rows
INSERT INTO products (name, price, category)
VALUES
  ('Keyboard', 79.99, 'electronics'),
  ('Mouse', 49.99, 'electronics'),
  ('Desk Pad', 29.99, 'accessories');
 
-- INSERT from SELECT
INSERT INTO archived_orders (id, user_id, total, created_at)
SELECT id, user_id, total, created_at
FROM orders
WHERE created_at < '2025-01-01';
 
-- UPDATE with conditions
UPDATE users
SET status = 'inactive', updated_at = NOW()
WHERE last_login < '2025-01-01'
  AND status = 'active';
 
-- UPDATE with JOIN (PostgreSQL syntax)
UPDATE orders o
SET status = 'cancelled'
FROM users u
WHERE o.user_id = u.id
  AND u.status = 'banned';
 
-- DELETE with conditions
DELETE FROM sessions
WHERE expires_at < NOW();
 
-- UPSERT (INSERT ... ON CONFLICT) - PostgreSQL
INSERT INTO user_settings (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme, language = EXCLUDED.language;

-- Single column index
CREATE INDEX idx_users_email ON users (email);
 
-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_date
ON orders (user_id, created_at DESC);
 
-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);
 
-- Partial index (PostgreSQL) - only index active users
CREATE INDEX idx_users_active
ON users (email)
WHERE status = 'active';

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

-- BAD: function on column prevents index use
SELECT * FROM users WHERE LOWER(email) = 'jane@example.com';
 
-- GOOD: use a functional index or normalize data on insert
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Slower with large datasets
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
 
-- Usually faster
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- CASE: conditional logic in queries
SELECT
  name,
  price,
  CASE
    WHEN price < 20 THEN 'Budget'
    WHEN price < 100 THEN 'Mid-range'
    ELSE 'Premium'
  END AS tier
FROM products;
 
-- COALESCE: first non-null value
SELECT
  COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM users;
 
-- DISTINCT ON (PostgreSQL): latest record per group
SELECT DISTINCT ON (user_id)
  user_id, total, created_at
FROM orders
ORDER BY user_id, created_at DESC;
 
-- String aggregation
-- PostgreSQL:
SELECT department, STRING_AGG(name, ', ') AS members
FROM employees GROUP BY department;
 
-- MySQL:
SELECT department, GROUP_CONCAT(name SEPARATOR ', ') AS members
FROM employees GROUP BY department;

-- Current timestamp
SELECT NOW();                          -- PostgreSQL, MySQL
SELECT CURRENT_TIMESTAMP;              -- Standard SQL
 
-- Extract parts
SELECT EXTRACT(YEAR FROM created_at) AS year FROM orders;
SELECT EXTRACT(MONTH FROM created_at) AS month FROM orders;
 
-- Date arithmetic
SELECT created_at + INTERVAL '30 days' FROM subscriptions;  -- PostgreSQL
SELECT DATE_ADD(created_at, INTERVAL 30 DAY) FROM subscriptions;  -- MySQL
 
-- Truncate to period
SELECT DATE_TRUNC('month', created_at) AS month FROM orders;  -- PostgreSQL