SQL Cheat Sheet

This cheat sheet puts the SQL you reach for most on one page: the order a query runs in, the clauses, joins, aggregates, subqueries, CTEs, set operators, window functions, the common string and date functions, and the rules for NULL. It's organized the way you actually reach for it while writing a query — not alphabetically. Most of it is standard SQL that works the same in PostgreSQL, MySQL, SQL Server and SQLite; where a database spells something differently, the table says so. Every example runs as-is against the sample tables in the Playground, which runs SQLite, so paste one in and see the result instead of taking it on faith.

The order SQL actually runs in

This is the single most useful thing to memorize, because it explains half of SQL's "weird" rules — like why most databases won't let you filter on a column alias in WHERE, and none let you use COUNT() there. You write a query top to bottom, but the database executes it in this order:

SQLite, which runs the playground, is lenient here and accepts a SELECT alias in WHERE; PostgreSQL, MySQL and SQL Server reject it, so write the expression out in full if the query has to travel.

SELECT basics

SELECT name, unit_price
FROM Products
ORDER BY unit_price DESC
LIMIT 3;

WHERE operators

JOINs

SELECT e.first_name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id;

Aggregate functions

Every aggregate collapses a set of rows into one value — used alone, that means the whole table; used with GROUP BY, one value per group. Apart from COUNT(*), which counts rows, they all skip NULLs, and over zero rows only COUNT gives 0: SUM, AVG, MIN and MAX give NULL, so wrap them in COALESCE(..., 0) when a report needs a number.

SELECT dept_id, COUNT(*) AS headcount, ROUND(AVG(salary), 0) AS avg_salary
FROM Employees
GROUP BY dept_id
HAVING COUNT(*) > 2
ORDER BY headcount DESC;

Subqueries

SELECT first_name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

CTEs: the WITH clause

WITH dept_pay AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM Employees
  GROUP BY dept_id
)
SELECT d.dept_name, ROUND(p.avg_salary) AS avg_salary
FROM dept_pay p
JOIN Departments d ON d.dept_id = p.dept_id
ORDER BY avg_salary DESC;

Set operators: UNION, INTERSECT, EXCEPT

Both queries must return the same number of columns, in compatible types, and a single ORDER BY goes at the very end.

SELECT city FROM Customers
UNION
SELECT location FROM Departments
ORDER BY city;

Window functions

Unlike GROUP BY, a window function keeps every row and adds a value to it. MySQL needs version 8.0 or later.

SELECT first_name, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM Employees
ORDER BY dept_id, rank_in_dept;

CASE — conditional values

SELECT order_id, order_total,
  CASE
    WHEN order_total >= 8000 THEN 'Large'
    WHEN order_total >= 3000 THEN 'Medium'
    ELSE 'Small'
  END AS size_band
FROM Orders;

CASE checks its WHEN branches top to bottom and stops at the first match — always include an ELSE, or unmatched rows come back NULL.

String functions

Date functions

strftime returns text, so the year comes back as '2024'; the playground also adds YEAR(), MONTH() and DAY(), which plain SQLite lacks, so YEAR(d) from the MySQL and SQL Server columns runs there too. The date functions guide runs each of these.

NULL handling

SELECT
  (SELECT COUNT(*) FROM Employees
   WHERE emp_id NOT IN (SELECT manager_id FROM Employees)) AS not_in_count,
  (SELECT COUNT(*) FROM Employees e
   WHERE NOT EXISTS (SELECT 1 FROM Employees m WHERE m.manager_id = e.emp_id)) AS not_exists_count;

Both halves ask how many employees manage nobody. NOT EXISTS finds seven; NOT IN finds none, because five employees have a NULL manager_id and NOT IN can never be sure a value is absent from a list that contains a NULL.

Changing data and tables

The WHERE clause is doing the same job here as in SELECT — it's just as easy to forget, and far more expensive to forget in an UPDATE or DELETE. It's safe to experiment with all of these in the Playground: the sample database resets to its original state on every page refresh.

Related reading

• SQL Commands: DDL, DML, DCL and TCL Explained
• SQL Aggregate Functions: COUNT, SUM, AVG, MIN and MAX
• The SQL SELECT Statement: A Beginner's Guide
• SQL JOINs Explained: INNER, LEFT, RIGHT & FULL
• GROUP BY vs HAVING in SQL: What's the Difference?
• SQL Subqueries Explained: Nested SELECT Statements
• SQL CASE WHEN Explained: Conditional Logic in Queries
• SQL CTEs Explained: How the WITH Clause Works
• UNION vs UNION ALL in SQL: Which One and Why
• SQL Window Functions: ROW_NUMBER, RANK & PARTITION BY
• SQL String Functions: UPPER, SUBSTRING, TRIM & More
• SQL Date Functions: YEAR, MONTH, DATEDIFF & Ranges
• PostgreSQL vs MySQL vs SQL Server vs SQLite: SQL Syntax Differences