SQL Cheat Sheet

1 Read rows — SELECT

-- pick columns from a table
SELECT name, price
FROM   products;

-- everything, alias, computed column
SELECT *,
       price * 1.06 AS price_incl_tax
FROM   products p;

-- unique values only
SELECT DISTINCT category FROM products;

Order of what the database actually runs: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That is why a column alias made in SELECT can't be used in WHERE.

2 Filter — WHERE

SELECT * FROM orders
WHERE  status IN ('paid','shipped')
  AND  total >= 100
  AND  created_at >= '2026-01-01'
  AND  note IS NOT NULL;

NULL is not equal to anything — even itself. Use IS NULL, never = NULL.

3 Sort & limit

SELECT name, price
FROM   products
ORDER  BY price DESC, name ASC
LIMIT  10;          -- Postgres/MySQL

SQL Server uses SELECT TOP 10 …. Oracle/standard: FETCH FIRST 10 ROWS ONLY. Paging: add OFFSET 20 to skip the first 20.

4 Aggregate — GROUP BY

SELECT   category,
         COUNT(*)      AS n,
         SUM(price)    AS revenue
FROM     products
GROUP BY category
HAVING   SUM(price) > 1000
ORDER BY revenue DESC;

WHERE vs HAVING: WHERE filters rows before grouping; HAVING filters groups after.

5 Combine tables — JOIN

SELECT o.id, c.name, o.total
FROM   orders     o
JOIN   customers  c ON c.id = o.customer_id
LEFT JOIN refunds r ON r.order_id = o.id
WHERE  o.total > 50;

Alias every table (o, c) and qualify columns. A LEFT JOIN that finds no match fills the right-side columns with NULL — handy for "customers with no orders".

6 Subqueries & CTEs

-- readable, reusable: a CTE
WITH big_spenders AS (
  SELECT customer_id, SUM(total) AS spend
  FROM   orders
  GROUP  BY customer_id
  HAVING SUM(total) > 1000
)
SELECT c.name, b.spend
FROM   big_spenders b
JOIN   customers c ON c.id = b.customer_id;

Prefer a WITH (CTE) over a nested subquery — it reads top-to-bottom and you can chain several.

7 Window functions

Aggregate without collapsing rows — running totals, rankings, row-by-row comparisons.

SELECT name, category, price,
  RANK()      OVER (PARTITION BY category
                    ORDER BY price DESC)      AS rnk,
  SUM(price)  OVER (PARTITION BY category)    AS cat_total,
  price - LAG(price) OVER (ORDER BY created_at) AS delta
FROM products;

8 Change data

INSERT INTO products (name, price)
VALUES ('Widget', 9.90);

UPDATE products
SET    price = price * 1.1
WHERE  category = 'premium';

DELETE FROM products
WHERE  discontinued = TRUE;

Always write the WHERE first. An UPDATE or DELETE with no WHERE hits every row. Run a SELECT with the same filter first to see what you'll touch.

9 Handy functions

SELECT name,
  CASE WHEN price > 100 THEN 'premium'
       WHEN price > 20  THEN 'standard'
       ELSE 'budget' END AS tier
FROM products;

10 Make it fast

• Index the columns you filter and join on. It's the single biggest win.
• Select only the columns you need — avoid SELECT * in production.
• Filter early; don't WHERE on a wrapped column (WHERE YEAR(d)=2026 can't use an index — use a range instead).
• Read the plan: EXPLAIN (add ANALYZE in Postgres) shows if an index is used.
• Prefer EXISTS over IN (subquery) on large sets.

80% of slow queries are one missing index away from fast.