SQL Fundamentals

What we're doing: Run all four statements against one table, using RETURNING each time to see the effect without a follow-up SELECT.

CREATE TABLE tasks (id SERIAL PRIMARY KEY, title TEXT, done BOOLEAN DEFAULT false);

INSERT INTO tasks (title) VALUES ('Ship the feature'), ('Write docs')
    RETURNING id, title;

SELECT id, title, done FROM tasks ORDER BY id;

UPDATE tasks SET done = true WHERE title = 'Ship the feature'
    RETURNING id, done;

DELETE FROM tasks WHERE done = true
    RETURNING id, title;

1

id fills itself; done defaults to false for every new row.

3–4

Two rows in one INSERT. RETURNING hands back both, in the order they were inserted.

6

A plain SELECT, just to see the table state — not required by RETURNING, done here to show it.

8–9

UPDATE changes one row; RETURNING confirms exactly what changed, without a second query.

11–12

DELETE removes the row that is now done; RETURNING hands back what was deleted, since the row no longer exists to SELECT afterward.

 id |       title       
----+-------------------
  1 | Ship the feature
  2 | Write docs
(2 rows)

 id |       title       | done 
----+-------------------+------
  1 | Ship the feature   | f
  2 | Write docs         | f
(2 rows)

 id | done 
----+------
  1 | t
(1 row)

 id |       title       
----+-------------------
  1 | Ship the feature
(1 row)

Why this works: RETURNING attaches directly to the write statement, so PostgreSQL hands back the affected rows as part of executing it — there is no window where the client has committed a write but not yet seen its result. This matters most for DELETE: once a row is gone, an ordinary SELECT can never see it again, so RETURNING is the only way to get the deleted row's data back in the same transaction. UPDATE's RETURNING reflects the row AFTER the change, which is why done reads 't', not the pre-update 'f'.

What we're doing: Show WHERE filtering rows, GROUP BY/HAVING filtering groups by an aggregate WHERE cannot see, and ORDER BY/LIMIT slicing the result.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    status TEXT,
    total NUMERIC
);

INSERT INTO orders (customer_id, status, total) VALUES
    (1, 'completed', 50), (1, 'completed', 30), (1, 'completed', 20),
    (2, 'completed', 100), (2, 'cancelled', 40),
    (3, 'completed', 10);

SELECT customer_id, count(*) AS orders, sum(total) AS total
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING count(*) > 1
ORDER BY total DESC
LIMIT 1;

8–11

Six rows: customer 1 has three completed orders, customer 2 has one completed and one cancelled, customer 3 has one completed.

15

WHERE removes the cancelled row before any grouping happens.

16

The remaining five rows collapse into three groups, one per customer_id.

17

HAVING drops customer 3's group (count 1) — count(*) is an aggregate, which WHERE could never have referenced.

18

Of the two surviving groups, sort by total descending.

19

LIMIT 1 keeps only the top group after sorting — customer 1, whose three orders sum to 100.

 customer_id | orders | total 
-------------+--------+-------
           1 |      3 |   100
(1 row)

Why this works: WHERE runs first and works row by row, so it can test status but has no concept of "how many rows share this customer_id" yet — that only exists after GROUP BY collapses rows into groups. HAVING runs after grouping, so count(*) and sum(total) are already computed values it can filter on. Customer 2 also totals 100 across all orders, but its cancelled row was removed by WHERE before grouping, leaving only one completed order (total 100) — group count 1, which HAVING excludes. Customer 1 survives with count 3 and total 100, and LIMIT 1 keeps it as the sole top row.

What we're doing: Combine all four tools in one SELECT over a small orders table, showing each one produce a real, checkable value.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    total NUMERIC,
    nickname TEXT,
    discount_code TEXT
);

INSERT INTO orders (total, nickname, discount_code) VALUES
    (150, NULL, ''),
    (25, 'Big Sale', 'NONE');

SELECT
    id,
    CASE WHEN total > 100 THEN 'large' WHEN total > 10 THEN 'medium' ELSE 'small' END AS size,
    COALESCE(nickname, 'Unnamed order') AS label,
    NULLIF(discount_code, '') AS code,
    total::text || ' USD' AS display_total
FROM orders
ORDER BY id;

8–10

Row 1 has total 150, no nickname, and an empty-string discount_code — not NULL, an actual empty string.

13–15

Three WHEN branches; the first row matches "large" (>100), the second matches "medium" (>10).

16

Row 1's NULL nickname falls through to the fallback; row 2's real nickname is used as-is.

17

NULLIF('', '') returns NULL for row 1, turning the empty-string sentinel into a real absence. Row 2's 'NONE' does not equal '', so it passes through unchanged.

18

total::text converts NUMERIC to TEXT so it can be concatenated with a literal string using ||.

 id |  size  |     label     | code | display_total 
----+--------+---------------+------+----------------
  1 | large  | Unnamed order |      | 150 USD
  2 | medium | Big Sale      | NONE | 25 USD

Why this works: CASE evaluates WHEN clauses top to bottom and stops at the first true one, so 150 matches "large" before "medium" is even checked. COALESCE treats SQL NULL specifically — row 1's nickname column holds NULL, so COALESCE moves to its fallback; row 2's real value short-circuits it. NULLIF compares the discount_code against the empty string as its second argument — row 1's discount_code IS the empty string, so NULLIF manufactures a NULL, which renders as blank; row 2's "NONE" does not match "", so it is returned unchanged. The ::text cast is what makes || legal at all — PostgreSQL will not implicitly concatenate a NUMERIC with a TEXT literal.

What we're doing: Use a table alias, a column alias, an inline expression, a scalar function and an aggregate function together in one query.

CREATE TABLE orders (id SERIAL PRIMARY KEY, status TEXT, total NUMERIC);

INSERT INTO orders (status, total) VALUES
    ('completed', 50), ('completed', 150), ('cancelled', 30);

SELECT
    upper(o.status) AS status_label,
    count(*) AS order_count,
    sum(o.total) AS total_revenue,
    round(avg(o.total), 2) AS avg_order
FROM orders o
WHERE o.status = 'completed'
GROUP BY o.status;

11

o is a table alias — every later reference to orders in this query can use o instead.

6

upper() is a scalar function — applied to the single, already-grouped status value.

7–9

count, sum and avg are all aggregates — one value each, for the whole (single) group that survives WHERE.

9

round(avg(...), 2) nests a scalar function around an aggregate's result — legal, since avg() already produced one value by the time round() runs.

12

WHERE filters using the alias o, exactly as it would use the full table name orders.

 status_label | order_count | total_revenue | avg_order 
--------------+-------------+---------------+-----------
 COMPLETED    |           2 |           200 |    100.00

Why this works: The cancelled row is excluded by WHERE before grouping, leaving two completed orders (50 and 150). count(*) counts them, sum() adds their totals, and avg() computes their mean — all three are aggregates because they each collapse multiple rows into one value per group. upper() and round() are scalar: upper() runs once on the single grouped status value, and round() runs once on avg()'s already-computed result, formatting it rather than aggregating anything further.

What we're doing: Show WHERE email = NULL silently matching nothing, next to IS NULL correctly matching the rows with a missing email.

CREATE TABLE customers (id SERIAL PRIMARY KEY, email TEXT);

INSERT INTO customers (email) VALUES ('a@example.com'), (NULL), (NULL);

SELECT count(*) FROM customers WHERE email = NULL;

SELECT count(*) FROM customers WHERE email IS NULL;

SELECT count(*) FROM customers WHERE NOT (email = NULL);

3

Three rows: one real email, two rows with a genuinely missing email.

5

email = NULL evaluates to NULL for every single row, including the two that ARE actually NULL — WHERE never keeps a NULL result.

7

IS NULL is the special-cased operator — it correctly reports true for the two missing-email rows.

9

NOT (NULL) is still NULL, not true — so this also matches nothing, for the same underlying reason as line 5.

 count 
-------
     0
(1 row)

 count 
-------
     2
(1 row)

 count 
-------
     0
(1 row)

Why this works: = NULL asks PostgreSQL to compare a column against an unknown value — the honest answer is always 'unknown,' regardless of what the column actually holds, so WHERE (which only keeps true rows) keeps zero rows every time. This is not a bug or an edge case unique to this table; email = NULL matches nothing on ANY table, for ANY column. IS NULL sidesteps the comparison entirely — it directly tests 'is this value the special marker NULL,' which is a real true/false question with a real answer, which is exactly why it exists as separate syntax rather than reusing =.

What we're doing: Show that WHERE ... AND ... OR ... without parentheses groups the AND first, producing rows a reader might not expect from the intended "only completed orders, either large or refunded" logic.

CREATE TABLE orders (id SERIAL PRIMARY KEY, status TEXT, total NUMERIC);

INSERT INTO orders (status, total) VALUES
    ('completed', 150),   -- large completed order
    ('completed', 20),    -- small completed order
    ('refunded', 20),     -- small refunded order
    ('cancelled', 500);   -- large but cancelled

SELECT id, status, total FROM orders
WHERE status = 'completed' AND total > 100 OR status = 'refunded'
ORDER BY id;

SELECT id, status, total FROM orders
WHERE status = 'completed' AND (total > 100 OR status = 'refunded')
ORDER BY id;

3–7

Four orders: one large completed, one small completed, one small refunded, one large cancelled.

9

Parsed as (status = 'completed' AND total > 100) OR status = 'refunded' — AND binds first.

13

Explicit parentheses change the meaning: now it's completed AND (large OR refunded) — the small completed row is excluded here, unlike above.

 id |  status   | total 
----+-----------+-------
  1 | completed |   150
  3 | refunded  |    20
(2 rows)

 id |  status   | total 
----+-----------+-------
  1 | completed |   150
(1 row)

Why this works: The first query's default precedence groups AND before OR: (status = 'completed' AND total > 100) OR status = 'refunded'. That matches order 1 (completed and large) via the AND branch, and order 3 (refunded) via the OR branch — regardless of order 3's total, since the OR branch never even checks it. The second query's explicit parentheses require status = 'completed' AND (total > 100 OR status = 'refunded') — order 3 fails this immediately because its status is not 'completed', so only order 1 remains. Both queries are completely valid SQL; they simply express different logic, and the only way to tell which one a piece of code means is to look at (or add) the parentheses.