beginnerintermediateadvanced

PostgreSQL · SQL Fundamentals

SQL Fundamentals

The four statements, the filtering/sorting pipeline, conditional logic, and the two gotchas that bite every SQL author

RETURNINGthe only way to see a row DELETE just removed
WHERE → GROUP BY → HAVINGfixed clause order, not left-to-right English
IS NULL, never = NULLNULL = NULL is NULL, not true
AND before ORparenthesize any WHERE that mixes both

1.SELECT, INSERT, UPDATE, DELETE and RETURNING

SELECT reads rows, INSERT adds them, UPDATE changes them, DELETE removes them. RETURNING attaches to a write to hand back the affected rows in the same round trip — no second query needed to see what happened.

Key features

SELECT never modifies data — INSERT, UPDATE and DELETE all do
INSERT ... RETURNING hands back the row(s) just inserted, including generated columns
UPDATE without a WHERE clause changes every row in the table
DELETE without a WHERE clause removes every row in the table
RETURNING works on INSERT, UPDATE, DELETE — never SELECT, which has nothing extra to give

The four statements, and what RETURNING adds to each

The four statements, and what RETURNING adds to each
Statement	Effect	With RETURNING
SELECT ... FROM t	reads rows, changes nothing	not applicable — SELECT already returns rows
INSERT INTO t ... RETURNING *	adds row(s)	hands back the row(s) just inserted, generated columns included
UPDATE t SET ... RETURNING *	changes matching row(s)	hands back each row AFTER the update was applied
DELETE FROM t ... RETURNING *	removes matching row(s)	hands back each row as it existed just before deletion

A write with RETURNING answers its own question

INSERT/UPDATE/DELETE

changes rows

RETURNING *

hands back the affected rows

result set

no second SELECT needed

Command

sql

SELECT id, title FROM tasks WHERE done = false;

INSERT INTO tasks (title) VALUES ('Write docs') RETURNING id;

UPDATE tasks SET done = true WHERE id = 3 RETURNING *;

DELETE FROM tasks WHERE done = true RETURNING id;

RETURNING hands back affected rows in the same round trip — the only way to see a deleted row's data, since a later SELECT never can.

2.WHERE, ORDER BY, LIMIT/OFFSET, GROUP BY, HAVING, DISTINCT

WHERE filters rows first. GROUP BY collapses rows into groups, HAVING filters those groups. ORDER BY sorts the result, LIMIT/OFFSET slice it. DISTINCT drops duplicates; DISTINCT ON keeps one row per group, chosen by ORDER BY.

Key features

WHERE filters rows before grouping; HAVING filters groups after grouping
WHERE cannot reference an aggregate function (count, sum, ...) — HAVING can
ORDER BY sorts the final result; LIMIT/OFFSET slice the sorted result, in that order
DISTINCT drops fully-duplicate rows; DISTINCT ON (col) keeps one row per value of col
DISTINCT ON requires ORDER BY to start with the same column, or Postgres rejects the query

What each clause operates on

What each clause operates on
Clause	Operates on	Runs relative to grouping
WHERE	individual rows	before GROUP BY
GROUP BY	rows, collapsing them into groups	—
HAVING	groups	after GROUP BY
ORDER BY	the final result set	after everything else
LIMIT / OFFSET	the sorted final result	after ORDER BY

DISTINCT vs DISTINCT ON

DISTINCT vs DISTINCT ON
Form	Keeps
SELECT DISTINCT *	one copy of each fully-identical row
SELECT DISTINCT col	one row per distinct value of col — other columns collapsed away
SELECT DISTINCT ON (col) *	one whole row per value of col, chosen by ORDER BY

Clause order in one query

WHERE

filters rows

GROUP BY

collapses into groups

HAVING

filters groups

ORDER BY

sorts the result

LIMIT/OFFSET

slices the sorted result

Command

sql

SELECT customer_id, count(*) AS orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING count(*) > 2
ORDER BY orders DESC
LIMIT 5 OFFSET 0;

SELECT DISTINCT status FROM orders;

WHERE filters rows before grouping and cannot see an aggregate; HAVING filters groups after and can. Order: WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

3.CASE, COALESCE, NULLIF, CAST and type conversion

CASE branches like an if/elif chain. COALESCE returns the first non-NULL argument — a default. NULLIF returns NULL if two values are equal, else the first — turning a sentinel into a real NULL. CAST converts a value between types.

Key features

CASE evaluates each WHEN in order, top to bottom, and stops at the first match
COALESCE(a, b, c) returns the first of a, b, c that is not NULL
NULLIF(a, b) returns NULL if a = b, otherwise returns a
CAST(x AS type) and x::type are equivalent — :: is Postgres's shorthand, not standard SQL
CASE with no ELSE returns NULL when no WHEN matches, not an error

Four conditional and conversion tools

Four conditional and conversion tools
Expression	Returns
CASE WHEN cond THEN a ELSE b END	a if cond is true, else b (or NULL with no ELSE)
COALESCE(a, b, c)	the first of a, b, c that is not NULL
NULLIF(a, b)	NULL if a equals b, otherwise a
x::integer / CAST(x AS integer)	x converted to the target type, or an error if the value cannot convert

Four tools, four different jobs

CASE

branch on any condition

COALESCE

first non-NULL value

NULLIF

turn a sentinel into NULL

CAST / ::

change the type

Command

sql

SELECT
    CASE WHEN total > 100 THEN 'large' WHEN total > 10 THEN 'medium' ELSE 'small' END AS size,
    COALESCE(nickname, first_name, 'Unknown') AS display_name,
    NULLIF(discount_code, '') AS discount_code,
    price::numeric(10, 2) AS price
FROM orders;

COALESCE returns the first non-NULL argument; NULLIF manufactures a NULL when two values are equal — combine them to treat a sentinel value as absence.

4.Aliases, expressions, scalar and aggregate functions

AS renames a column or table. A scalar function returns one value per row (upper, round); an aggregate returns one value per group (count, sum, avg) — the same row-vs-group distinction WHERE and HAVING split on.

Key features

AS renames a column or table; the AS keyword itself is optional for a column alias
A table alias lets a query refer to a long or repeated table name by a short one
A scalar function returns one value per input row: upper(), length(), round()
An aggregate returns one value per group, or per result set if there is no GROUP BY at all
A SELECT-list expression like price * quantity is computed per row, like a scalar function

Scalar vs aggregate functions

Scalar vs aggregate functions
Kind	Input → output	Examples
Scalar	one row → one value, every row	upper(text), length(text), round(numeric), now()
Aggregate	many rows → one value per group	count(*), sum(col), avg(col), min(col), max(col)

Command

sql

SELECT
    o.id AS order_id,               -- column alias, AS optional
    c.name customer_name,           -- alias without AS — same effect
    o.total * 1.1 AS total_with_tax -- an expression, aliased
FROM orders o                       -- table alias
JOIN customers c ON c.id = o.customer_id;

A scalar function returns one value per row; an aggregate returns one per group. A SELECT-list alias is not visible inside WHERE — repeat the expression.

5.NULL semantics and three-valued logic

NULL means 'unknown,' not zero or empty. Comparing anything to NULL — even NULL = NULL — produces NULL, not true or false, which is why WHERE silently drops rows a reader might expect kept.

Key features

NULL = NULL evaluates to NULL (unknown), never true
Any comparison against NULL (=, <, >, <>) evaluates to NULL
IS NULL / IS NOT NULL treat NULL specially and are the only ones returning true or false
WHERE keeps a row only when its condition is true — false and NULL are both excluded
NOT NULL (false) differs from NULL (unknown) — NOT applied to unknown is still unknown

Three-valued logic — AND/OR truth tables involving NULL

Three-valued logic — AND/OR truth tables involving NULL
a	b	a AND b	a OR b
true	NULL	NULL	true
false	NULL	false	NULL
NULL	NULL	NULL	NULL

= NULL vs IS NULL

WHERE email = NULL

Compares against an unknown value
Always evaluates to NULL (unknown)
WHERE excludes it — matches ZERO rows, always, for every table

WHERE email IS NULL

A special-cased test, not a comparison
Evaluates to true or false, never NULL
Correctly matches every row whose email really is absent

Command

sql

SELECT * FROM customers WHERE email IS NULL;
SELECT * FROM customers WHERE email IS NOT NULL;

-- always wrong — matches nothing, ever, on any table:
SELECT * FROM customers WHERE email = NULL;

NULL = NULL is NULL, not true — WHERE drops every NULL comparison since it only keeps true rows. Use IS NULL / IS NOT NULL, never = NULL.

6.Operator precedence and parentheses

AND binds tighter than OR, so a WHERE clause mixing both groups the AND conditions first by default — not necessarily the grouping intended. Parentheses always override the default and are never wrong to add.

Key features

Comparisons (=, <, >) bind tighter than NOT, which binds tighter than AND, tighter than OR
a AND b OR c means (a AND b) OR c, not a AND (b OR c)
Parentheses always override default precedence, and never change meaning added redundantly
When in doubt, add parentheses — they cost nothing and remove all ambiguity for the reader
Precedence applies inside one condition — never across separate WHERE and HAVING clauses

Command

sql

-- ambiguous to a reader, unambiguous to PostgreSQL: AND binds first
SELECT * FROM orders
WHERE status = 'completed' AND total > 100 OR status = 'refunded';

-- the same query, made explicit
SELECT * FROM orders
WHERE (status = 'completed' AND total > 100) OR status = 'refunded';

-- a DIFFERENT query, also legal, with different meaning
SELECT * FROM orders
WHERE status = 'completed' AND (total > 100 OR status = 'refunded');

AND binds tighter than OR — a AND b OR c means (a AND b) OR c. Add parentheses whenever a WHERE clause mixes both; it costs nothing and removes the ambiguity.

Spotted a mistake on this page? Tell us.

Read Sheet