2026-09-09
A dense reference for the SQL you actually write day to day - clause order, JOIN types, and the aggregate/string/date functions that come up constantly. Paste anything you write against this into the SQL Formatter to get it back readable.
SQL clauses have a fixed order you write them in, but the database evaluates them in a different order internally. Getting the written order right is what matters for not hitting a syntax error:
| Order | Clause | Purpose |
|---|---|---|
| 1 | SELECT |
Columns/expressions to return |
| 2 | FROM |
Source table(s) |
| 3 | JOIN ... ON |
Combine rows from other tables |
| 4 | WHERE |
Filter rows before grouping |
| 5 | GROUP BY |
Collapse rows into groups |
| 6 | HAVING |
Filter groups after aggregation |
| 7 | ORDER BY |
Sort the result set |
| 8 | LIMIT / OFFSET |
Cap and page the result set |
The key gotcha: WHERE runs before grouping and can't reference an aggregate like COUNT(*); use HAVING for that instead.
| JOIN | Returns |
|---|---|
INNER JOIN |
Only rows with a match in both tables |
LEFT JOIN |
All rows from the left table, matched rows from the right (NULLs where there's no match) |
RIGHT JOIN |
All rows from the right table, matched rows from the left (NULLs where there's no match) |
FULL OUTER JOIN |
All rows from both tables, NULLs on whichever side has no match |
CROSS JOIN |
Every row from the left paired with every row from the right (cartesian product, no ON) |
Self JOIN |
A table joined to itself, usually via aliases, for hierarchical or comparative data |
INNER JOIN and plain JOIN are the same thing - INNER is implicit.
| Function | Purpose |
|---|---|
COUNT(*) |
Number of rows |
COUNT(col) |
Number of non-NULL values in col |
COUNT(DISTINCT col) |
Number of distinct non-NULL values |
SUM(col) |
Total of a numeric column |
AVG(col) |
Mean of a numeric column |
MIN(col) / MAX(col) |
Smallest / largest value |
GROUP_CONCAT(col) / STRING_AGG(col, sep) |
Concatenate group values into one string (name varies by database) |
Any non-aggregated column in SELECT alongside an aggregate must appear in GROUP BY.
| Function | Purpose |
|---|---|
CONCAT(a, b) or `a |
|
LENGTH(s) / LEN(s) |
Length of a string |
UPPER(s) / LOWER(s) |
Change case |
TRIM(s) |
Strip leading/trailing whitespace |
SUBSTRING(s, start, len) |
Extract part of a string |
REPLACE(s, old, new) |
Replace all occurrences of a substring |
LIKE '%pattern%' |
Pattern match with % (any characters) and _ (one character) |
| Function | Purpose |
|---|---|
NOW() / CURRENT_TIMESTAMP |
Current date and time |
CURDATE() / CURRENT_DATE |
Current date only |
DATE_ADD(d, INTERVAL n unit) |
Add an interval to a date |
DATEDIFF(d1, d2) |
Difference between two dates |
EXTRACT(unit FROM d) |
Pull out a part of a date (year, month, day, ...) |
DATE_FORMAT(d, fmt) / TO_CHAR(d, fmt) |
Format a date as a string (name and format tokens vary by database) |
Exact function names and syntax vary between MySQL, PostgreSQL, SQL Server, and SQLite - the table above covers the common shape, but always check your specific database's docs for the precise signature.