SQL Cheat Sheet

Quick reference for data analysts. One-liner + example for every pattern.

---

Query Structure

SELECT  column1, column2, agg_func(col3) AS alias   -- 5th
FROM    table_name                                    -- 1st
JOIN    other_table ON table.key = other_table.key   -- 2nd
WHERE   condition                                     -- 3rd
GROUP BY column1, column2                             -- 4th
HAVING  agg_condition                                 -- 6th
ORDER BY alias DESC                                   -- 7th
LIMIT   10;                                           -- 8th

---

Filtering — WHERE

Pattern	Example
Equal	WHERE status = 'completed'
Not equal	WHERE status <> 'cancelled'
Range	WHERE total BETWEEN 100 AND 500
List	WHERE status IN ('pending', 'processing')
Exclude list	WHERE status NOT IN ('cancelled', 'refunded')
Pattern	WHERE email LIKE '%@gmail.com'
NULL check	WHERE delivery_date IS NULL
Not null	WHERE delivery_date IS NOT NULL
AND/OR	WHERE total > 100 AND (status = 'completed' OR status = 'pending')

---

Aggregation

COUNT(*)                -- all rows
COUNT(column)           -- non-null values in column
COUNT(DISTINCT column)  -- unique non-null values
SUM(column)
AVG(column)
MIN(column) / MAX(column)

-- Multiple aggregations in one query
SELECT
    category,
    COUNT(*)                AS orders,
    COUNT(DISTINCT customer_id) AS unique_buyers,
    SUM(total)              AS revenue,
    AVG(total)              AS avg_order,
    MAX(total)              AS largest_order
FROM orders
WHERE status = 'completed'
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY revenue DESC;

---

JOINs

-- INNER: only matching rows in both tables
FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id

-- LEFT: all orders, customer info where available
FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id

-- Find orders with no matching customer
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL

-- Three tables
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id

---

Subqueries and CTEs

-- Subquery in WHERE
WHERE total > (SELECT AVG(total) FROM orders WHERE status = 'completed')

-- CTE (prefer over nested subqueries)
WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total) AS revenue
    FROM orders WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly;

---

CASE WHEN

-- Conditional column
SELECT order_id, total,
    CASE
        WHEN total > 300 THEN 'Large'
        WHEN total > 100 THEN 'Medium'
        ELSE 'Small'
    END AS order_size
FROM orders;

-- Conditional aggregation
SELECT
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
    ROUND(COUNT(CASE WHEN status = 'completed' THEN 1 END) * 100.0 / COUNT(*), 1) AS pct_complete
FROM orders;

---

Window Functions

-- Running total
SUM(total) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Running total per customer
SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date)

-- Rank within group
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_in_customer

-- Previous row value
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue

-- Top N per group (use ROW_NUMBER + CTE)
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY total DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

---

Date Functions

Task	MySQL	PostgreSQL
Current date	CURDATE()	CURRENT_DATE
Extract year	YEAR(order_date)	EXTRACT(YEAR FROM order_date)
Extract month	MONTH(order_date)	EXTRACT(MONTH FROM order_date)
Format as string	DATE_FORMAT(d, '%Y-%m')	TO_CHAR(d, 'YYYY-MM')
Date difference	DATEDIFF(d1, d2)	d1 - d2 (returns interval)
Add days	DATE_ADD(d, INTERVAL 7 DAY)	d + INTERVAL '7 days'
Truncate to month	DATE_FORMAT(d, '%Y-%m-01')	DATE_TRUNC('month', d)

---

NULL Handling

COALESCE(column, default_value)  -- return first non-null
NULLIF(a, b)                     -- return NULL if a = b, else return a (division safety)
IFNULL(column, default)          -- MySQL: same as COALESCE for one column

---

String Functions

LOWER(column) / UPPER(column)
TRIM(column)                     -- remove leading/trailing spaces
CONCAT(col1, ' ', col2)         -- MySQL
col1 || ' ' || col2             -- PostgreSQL/ANSI
SUBSTRING(column, 1, 3)         -- first 3 characters
REPLACE(column, 'old', 'new')
LENGTH(column)                   -- character count

---

Performance Tips

Problem	Fix
WHERE email LIKE '%gmail' on 50M rows	Leading wildcard → full table scan. Use full-text index.
SELECT * on wide table	Select only needed columns
No WHERE on large table	Always filter — add LIMIT for exploration
GROUP BY slow	Ensure GROUP BY columns are indexed
Correlated subquery in SELECT	Replace with JOIN + CTE
Check query plan	EXPLAIN SELECT ... (MySQL) or EXPLAIN ANALYZE SELECT ... (PostgreSQL)