Reference
Look it up, fast.
Deeper guides, scannable cheatsheets, and fixes for the mistakes everyone hits. All in one place.
Querying
SELECT syntax
The shape of a query and the order its clauses run in.
WHERE operators
Comparisons, AND/OR, IN, BETWEEN, and LIKE.
JOIN types
INNER, LEFT, RIGHT, FULL, and CROSS joins.
Aggregate functions
COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING.
Subqueries and EXISTS
Nested SELECTs, IN subqueries, EXISTS, and correlation.
Common table expressions
The WITH clause, chained steps, and CTE vs subquery.
Set operations
UNION ALL, UNION, INTERSECT, and EXCEPT.
Window functions
OVER, PARTITION BY, ranking, offsets, and running totals.
Tables & objects
CREATE TABLE
Declare columns, types, defaults, and constraints.
Views
Save a query under a name and reuse it like a table.
CREATE TABLE AS SELECT
Store a query result as a table you can query again.
Temporary tables
Scratch tables and views that clean up after themselves.
Schemas
Group objects into namespaces like staging and reporting.
Table constraints
NOT NULL, DEFAULT, CHECK, UNIQUE, and keys.
Sequences
Generate ID values with nextval and column defaults.
Macros and functions
Name a reusable expression or a parameterized query.
ALTER TABLE
Add, rename, retype, and drop columns on a live table.
DROP statements
Remove tables, views, schemas, and more without breakage.
Functions & expressions
Query basics
SELECT, WHERE, ORDER BY, and LIMIT at a glance.
Joins
INNER, LEFT, RIGHT, FULL, and CROSS, with what each one keeps.
Aggregates & grouping
COUNT, SUM, AVG, GROUP BY, and HAVING.
Date & string functions
The everyday date and text functions, with dialect notes.
Window functions
OVER, PARTITION BY, ranking, LAG/LEAD, and running totals.
Subqueries & CTEs
Scalar, IN, and EXISTS subqueries plus the WITH clause.
Set operations
UNION, UNION ALL, INTERSECT, and EXCEPT side by side.
CASE expressions
Searched and simple CASE, plus conditional aggregation.
NULL handling
IS NULL, COALESCE, NULLIF, and null-safe equality.
!!!!!!!!!!!!!!!!!
Why = NULL never matches
Comparisons to NULL return unknown, so the row is dropped. Use IS NULL.
COUNT or SUM is too high after a JOIN
A one-to-many join repeats rows, inflating totals. Count what you mean.
Why a JOIN without ON multiplies rows
A missing join condition pairs every row with every row. Add the key match.
Why a many-to-many JOIN inflates totals
When both sides have repeats, the join multiplies combinations. Pre-aggregate first.
Why joining on names instead of ids is risky
Names and labels can repeat or change. Join on stable ids whenever the schema gives you one.
Why my query returns no rows
Usually a filter that does not match the real data: case, ranges, or AND/OR.
Why my percentage comes out 0
Integer division truncates. Multiply by 100.0 and round.
Why a window running total jumps on ties
The default RANGE frame can group tied ORDER BY values. Use ROWS for row-by-row totals.
Why NOT IN with a NULL returns nothing
A single NULL in the list makes every NOT IN test unknown. Use NOT EXISTS.
LIMIT without ORDER BY gives unpredictable rows
With no ORDER BY, the database can return any rows in any order. Sort first.
Why a date range drops the last day
Comparing a timestamp to a bare date cuts off that day. Use a half-open range.
Why a LEFT JOIN turns into an INNER JOIN
A WHERE filter on the right table removes NULL matches. Move it into ON.
Why INNER JOIN filters belong in the right place
Relationship predicates belong in ON; row filters belong in WHERE. Mixing them hides bugs.
Why COUNT(column) skips NULL values
COUNT(*) counts rows, but COUNT(column) counts only non-NULL values.
Why averaging averages gives the wrong result
An average of group averages ignores group size. Recompute from totals.
Why OR without parentheses changes your filter
AND runs before OR. Add parentheses so the filter matches your intent.
Why UNION ALL keeps duplicate rows
UNION ALL stacks results exactly as written. Use UNION only when duplicates should collapse.