SQL
Cheat Sheet

Joins, aggregations, window functions, indexing, and common query patterns. From basics to advanced SQL.

Basic Queries

SELECT & WHERE

SELECT name, age, email
FROM users
WHERE age >= 18
  AND status = 'active'
ORDER BY name ASC
LIMIT 10 OFFSET 20;

Filtering & Sorting

SELECT * FROM orders
WHERE created_at > '2024-01-01'
  AND total BETWEEN 10 AND 100
  AND status IN ('pending', 'shipped')
ORDER BY created_at DESC;

Joins

Inner & Left Join

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

-- LEFT JOIN: all from left table
SELECT u.name, COALESCE(SUM(o.total), 0)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

Self & Cross Join

-- Self join
SELECT a.name, b.name AS manager
FROM employees a
JOIN employees b ON a.manager_id = b.id;

-- Cross join (all combinations)
SELECT s.name, p.name
FROM students s
CROSS JOIN projects p;

Aggregations & Grouping

GROUP BY & HAVING

SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY avg_salary DESC;

Aggregate Functions

COUNT(*), COUNT(DISTINCT col)
SUM(col), AVG(col)
MIN(col), MAX(col)
GROUP_CONCAT(col SEPARATOR ', ')

Window Functions

Ranking & Running Totals

SELECT
    name, salary, department,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS dept_rank,
    SUM(salary) OVER (
        ORDER BY hire_date
    ) AS running_total
FROM employees;

Lag & Lead

SELECT
    month, revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev,
    revenue - LAG(revenue, 1) OVER (ORDER BY month)
        AS growth
FROM monthly_sales;

Indexing & Performance

Index Types

CREATE INDEX idx_users_email
    ON users (email);

CREATE INDEX idx_orders_user_date
    ON orders (user_id, created_at);

-- Partial index
CREATE INDEX idx_active
    ON users (email) WHERE status = 'active';

EXPLAIN & Optimization

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'a@b.com';

-- Avoid SELECT *
-- Use covering indexes
-- Filter indexed columns early

Download

Print this page or save as PDF for quick reference.

Tip: Use Ctrl+P (Cmd+P on Mac) to print or save as PDF.

SQLCheat Sheet