SQL cheat sheet

Skeleton

SELECT columns
FROM table
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column DESC
LIMIT 10;

Always written in this order.

It runs in a different order

FROM → WHERE → GROUP BY
→ HAVING → SELECT
→ ORDER BY → LIMIT

That's why WHERE can't use a COUNT or a SELECT alias.

Filter rows

age > 40 AND city = 'Salt Harbor'
job <> 'jeweler' OR age < 30
city IN ('Salt Harbor', 'San Ciro')
age BETWEEN 30 AND 40
name LIKE 'Ma%'
email IS NULL

Text in single quotes. % means "anything".

Summarize

COUNT(*)              -- rows
COUNT(email)          -- no NULLs
COUNT(DISTINCT city)  -- distinct
SUM(loot)  AVG(loot)
MIN(loot)  MAX(loot)

Group

SELECT city, SUM(loot)
FROM thefts
WHERE loot >= 500         -- rows
GROUP BY city
HAVING COUNT(*) >= 2;     -- groups

In SELECT, only GROUP BY columns and aggregates.

Subquery

SELECT name, total
FROM orders
WHERE total >
  (SELECT AVG(total) FROM orders);

The inner one runs first.

Dates

-- all of March, nights included
date >= '2024-03-01'
AND date < '2024-04-01'

Better than BETWEEN when the column stores the time.

Sort, no repeats

SELECT DISTINCT city FROM thefts;
ORDER BY loot DESC  -- highest first
LIMIT 3             -- goes last

Without ORDER BY, LIMIT returns arbitrary rows.