Client Portal Get a Quote

SQL Cheat Sheet

Query patterns you'll use every day.

These are the query patterns I reach for most, written for MySQL 8. Most of them work unchanged in PostgreSQL and SQL Server. Where they don't, it's usually the date functions.

The queries use made-up tables (sales, emp, students) so you can see the shape of each pattern. Swap in your own table and column names.

-- Filtering & sorting
SELECT * FROM t WHERE a BETWEEN 1 AND 10 AND b IN ('x','y') ORDER BY c DESC LIMIT 20;

-- Aggregation
SELECT city, COUNT(*), SUM(amount) FROM sales GROUP BY city HAVING SUM(amount) > 100000;

-- Joins
SELECT * FROM a LEFT JOIN b ON b.a_id = a.id;

-- CTE
WITH monthly AS (
  SELECT DATE_FORMAT(dt, '%Y-%m') m, SUM(amount) total FROM sales GROUP BY m
)
SELECT * FROM monthly ORDER BY m;

-- Window functions
SELECT name, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) r FROM emp;

-- CASE
SELECT name, CASE WHEN marks >= 60 THEN 'Pass' ELSE 'Fail' END AS result FROM students;

When to use which

WHERE vs HAVING. WHERE filters rows before grouping, HAVING filters groups after. "Cities with total sales over 1 lakh" needs HAVING, because the total doesn't exist until the grouping is done.

LEFT JOIN when you can't afford to lose rows. An inner JOIN drops any row that doesn't match. If you're listing all customers with their order count, a customer with zero orders vanishes with an inner join. Use LEFT JOIN and COUNT(o.id) (not COUNT(*)) so they show up with 0.

CTEs make long queries readable. The WITH … AS (…) block is just a named subquery. When a query has three levels of nesting, pulling each level into a CTE makes it far easier to check and to explain to someone else.

Window functions don't collapse rows. GROUP BY gives you one row per group. A window function like RANK() OVER (PARTITION BY dept …) keeps every row and adds a column next to it. That's what makes "top earner per department" or "running total by month" easy.

Mistakes I see often

  • WHERE col = NULL never matches anything. Use IS NULL.
  • COUNT(col) skips NULLs, COUNT(*) doesn't. They can give different numbers on the same table.
  • BETWEEN '2026-01-01' AND '2026-01-31' on a DATETIME column misses everything on the 31st after midnight. Use >= '2026-01-01' AND < '2026-02-01'.
  • DATE_FORMAT is MySQL-only. In PostgreSQL it's TO_CHAR(dt, 'YYYY-MM').
  • Running UPDATE or DELETE without a WHERE. Write the WHERE first, run it as a SELECT to see which rows match, and only then change it to UPDATE.