SQL
SQL Reference SheetPostgres 15 · 13 categories
Basics8 entries
SELECT col1, col2

Choose columns to return

SELECT *

Return all columns

FROM table_name

Specify the source table

WHERE condition

Filter rows before grouping

ORDER BY col ASC|DESC

Sort the result set

LIMIT n / OFFSET n

Return at most n rows / skip first n

DISTINCT

Remove duplicate rows

col AS alias

Rename a column or expression

Filtering8 entries
AND / OR / NOT

Combine conditions

IN (val1, val2, …)

Match any value in a list

NOT IN (…)

Exclude values in a list

BETWEEN a AND b

Range check, inclusive

LIKE 'J%'

% = any chars, _ = one char

ILIKE 'j%'

Case-insensitive LIKE (Postgres)

IS NULL / IS NOT NULL

Check for missing value

EXISTS (subquery)

True if subquery returns any row

Aggregation7 entries
COUNT(*) / COUNT(col)

Count rows / non-null values

COUNT(DISTINCT col)

Count unique non-null values

SUM(col) / AVG(col)

Total / average (ignores NULLs)

MIN(col) / MAX(col)

Smallest / largest value

GROUP BY col

Collapse rows sharing a value

HAVING condition

Filter on aggregate result

FILTER (WHERE cond)

Conditional aggregate

Joins6 entries
INNER JOIN t ON a.id = b.id

Rows matching in both tables

LEFT JOIN t ON …

All left rows + matching right (NULL if none)

RIGHT JOIN t ON …

All right rows + matching left

FULL OUTER JOIN t ON …

All rows from both tables

CROSS JOIN t

Every combination — cartesian product

USING (col)

Join on same-named column

Subqueries5 entries
WHERE col IN (SELECT …)

Filter using subquery list

WHERE col = (SELECT …)

Scalar subquery — one value

WHERE EXISTS (SELECT 1 …)

True if subquery has any result

FROM (SELECT …) AS t

Inline view / derived table

Correlated subquery

References outer query column

CTEs3 entries
WITH cte AS (SELECT …)

Named temporary result set

WITH a AS (…), b AS (…)

Chain multiple CTEs

WITH RECURSIVE cte AS (…)

Self-referencing CTE for hierarchies

Window Functions10 entries
func() OVER ()

Apply across all rows

OVER (PARTITION BY col)

Reset window per group

OVER (ORDER BY col)

Ordered window — running totals

ROW_NUMBER()

Unique position per row

RANK() / DENSE_RANK()

Rank with / without gaps on ties

NTILE(n)

Divide rows into n equal buckets

LAG(col,n) / LEAD(col,n)

Value n rows before / after

FIRST_VALUE() / LAST_VALUE()

First / last in the window frame

SUM(col) OVER (…)

Running total

ROWS BETWEEN … AND …

Define window frame explicitly

String Functions10 entries
UPPER(col) / LOWER(col)

Change case

LENGTH(col)

Number of characters

TRIM(col)

Remove leading/trailing spaces

SUBSTRING(col, start, len)

Extract part of a string

LEFT(col,n) / RIGHT(col,n)

First / last n characters

CONCAT(a,b) or a || b

Join two strings

REPLACE(col, old, new)

Swap all occurrences

SPLIT_PART(col, delim, n)

Return nth part after splitting

INITCAP(col)

Capitalise first letter of each word

LPAD(col,n,fill) / RPAD(…)

Pad string to width n

Date & Time8 entries
NOW()

Current timestamp with time zone

CURRENT_DATE

Today's date (no time)

DATE_TRUNC('month', col)

Round timestamp down to period

EXTRACT(part FROM col)

Get numeric date part (year, month…)

AGE(end, start)

Interval between two dates

col + INTERVAL '3 days'

Add an interval to a date

TO_CHAR(col, 'YYYY-MM')

Format date as string

col::date

Cast timestamp to date

Type Casting5 entries
CAST(col AS integer)

Standard SQL cast

col::integer / ::text

Postgres shorthand cast

col::numeric(10,2)

Decimal with precision

col::boolean

Convert to true / false

col::date / ::timestamp

Convert to date or timestamp

Conditionals3 entries
CASE WHEN … THEN … END

Conditional expression

COALESCE(a, b, c)

Return first non-null value

NULLIF(a, b)

Returns NULL if a = b, else a

Math5 entries
+ - * / %

Basic arithmetic; % is modulo

ROUND(col, n)

Round to n decimal places

FLOOR(col) / CEIL(col)

Round down / up to integer

ABS(col)

Absolute value

POWER(base, exp) / SQRT(col)

Exponentiation / square root

Set Operations4 entries
UNION

Combine results, remove duplicates

UNION ALL

Combine results, keep duplicates (faster)

INTERSECT

Rows that appear in both queries

EXCEPT

Rows in first query but not second