4. SQL Querying Fundamentals

The previous chapters designed tables and picked types for their columns. This chapter is about getting answers back out and changing the data: reading rows, computing expressions, combining tables, summarizing groups, nesting queries, and inserting, updating, and deleting rows safely. These statements are what applications send to PostgreSQL all day, so how well you know them decides whether your queries are correct, readable, and fast.

<a id="41-retrieving-data"></a>

4.1 Retrieving Data

This subchapter covers the SELECT statement on a single table: its clauses and the order they run in, choosing and naming columns, filtering rows, the special behavior of NULL, removing duplicates, and sorting and paginating results.

SELECT Statement Anatomy

A database exists to answer questions: "which orders shipped last week?", "how much did each country spend?". In SQL you ask by describing the result you want, not the steps to compute it. You never tell PostgreSQL to "open the file, loop over the rows, keep the ones that match". You say what rows and columns you want, and the planner decides how to get them (chapter 7). This is what declarative means, and SELECT is SQL's one statement for asking questions.

The sample schema used in this chapter. All examples in this chapter run against a small online shop. Create it in a scratch database so you can run every example yourself:

CREATE TABLE customers (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text        NOT NULL,
  email       text        UNIQUE,                          -- optional: may be NULL
  country     char(2)     NOT NULL,
  referred_by bigint      REFERENCES customers (id),       -- who brought this customer in
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE categories (
  id        int  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      text NOT NULL,
  parent_id int  REFERENCES categories (id)
);

CREATE TABLE products (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku          text          NOT NULL UNIQUE,
  name         text          NOT NULL,
  category_id  int           REFERENCES categories (id),
  price        numeric(10,2) NOT NULL CHECK (price >= 0),
  stock        int           NOT NULL DEFAULT 0,
  discontinued boolean       NOT NULL DEFAULT false
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint      NOT NULL REFERENCES customers (id),
  status      text        NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  placed_at   timestamptz NOT NULL DEFAULT now(),
  shipped_at  timestamptz
);

CREATE TABLE order_items (
  order_id   bigint        NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id bigint        NOT NULL REFERENCES products (id),
  quantity   int           NOT NULL CHECK (quantity > 0),
  unit_price numeric(10,2) NOT NULL,                      -- price at the time of the order
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE employees (
  id         int           PRIMARY KEY,
  name       text          NOT NULL,
  manager_id int           REFERENCES employees (id),
  department text          NOT NULL,
  salary     numeric(10,2) NOT NULL
);

INSERT INTO customers (name, email, country, referred_by, created_at) VALUES
  ('Ana Silva',    'ana@example.com',   'PT', NULL, '2024-01-10'),
  ('Ben Okafor',   'ben@example.com',   'NG', 1,    '2024-03-22'),
  ('Chloe Martin', NULL,                'FR', 1,    '2024-05-02'),
  ('Diego Ruiz',   'diego@example.com', 'ES', NULL, '2024-07-15'),
  ('Emma Weber',   'emma@example.com',  'DE', 4,    '2024-09-30'),
  ('Farid Haddad', 'farid@example.com', 'FR', NULL, '2024-11-18');   -- has no orders

INSERT INTO categories (name, parent_id) VALUES
  ('Electronics', NULL), ('Books', NULL), ('Laptops', 1), ('Accessories', 1);

INSERT INTO products (sku, name, category_id, price, stock, discontinued) VALUES
  ('LAP-001',  'UltraBook 14',       3,    1299.00,    5, false),
  ('LAP-002',  'WorkBook 15',        3,     999.00,    0, true),
  ('ACC-001',  'USB-C Cable',        4,      12.50,  200, false),
  ('ACC-002',  'Wireless Mouse',     4,      29.90,   50, false),
  ('BK-001',   'SQL in Practice',    2,      39.00,   30, false),
  ('BK-002',   'Postgres Internals', 2,      54.00,   12, false),
  ('MISC-001', 'Gift Card',          NULL,   25.00, 1000, false);  -- no category

INSERT INTO orders (customer_id, status, placed_at, shipped_at) VALUES
  (1, 'shipped',   '2025-01-05 10:00+00', '2025-01-06 09:00+00'),
  (1, 'shipped',   '2025-02-11 14:30+00', '2025-02-12 11:00+00'),
  (2, 'pending',   '2025-02-20 08:15+00', NULL),
  (3, 'cancelled', '2025-03-01 19:45+00', NULL),
  (4, 'paid',      '2025-03-03 12:00+00', NULL),
  (5, 'shipped',   '2025-03-15 16:20+00', '2025-03-17 10:30+00');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 1299.00), (1, 3, 2, 12.50),
  (2, 5, 1,   39.00), (2, 6, 1, 54.00),
  (3, 4, 1,   29.90),
  (4, 2, 1,  999.00),
  (5, 1, 1, 1299.00), (5, 4, 2, 29.90),
  (6, 5, 2,   39.00), (6, 3, 1, 12.50);

INSERT INTO employees (id, name, manager_id, department, salary) VALUES
  (1, 'Grace', NULL, 'Engineering', 9000),
  (2, 'Hugo',  1,    'Engineering', 7000),
  (3, 'Iris',  1,    'Engineering', 7200),
  (4, 'Jonas', NULL, 'Sales',       8000),
  (5, 'Kim',   4,    'Sales',       5000),
  (6, 'Lena',  4,    'Sales',       5200);

SET timezone = 'UTC';   -- the outputs shown in this chapter use UTC

Some examples change data. Rebuild the database from this script whenever you want to return to the starting state.

The clauses of a SELECT. A full SELECT can have many clauses, and they must be written in a fixed order:

SELECT   c.country, count(*) AS orders               -- 5. which columns to output
FROM     customers AS c                              -- 1. where the rows come from
JOIN     orders AS o ON o.customer_id = c.id         --    (joins are part of FROM)
WHERE    o.status <> 'cancelled'                     -- 2. keep only some rows
GROUP BY c.country                                   -- 3. collapse rows into groups
HAVING   count(*) >= 1                               -- 4. keep only some groups
ORDER BY orders DESC, c.country                      -- 7. sort the result
LIMIT    3;                                          -- 8. cut the result

--  country | orders
-- ---------+--------
--  PT      |      2
--  DE      |      1
--  ES      |      1

Only SELECT itself is required: SELECT 1 + 1; is a valid query with no table at all. Every other clause is optional.

Logical processing order. The order you write the clauses is not the order PostgreSQL evaluates them. Logically, a query works like an assembly line where each stage receives the output of the previous one:

  FROM / JOIN      build the input rows (all combinations of joined tables)
       |
     WHERE         discard rows that fail the condition
       |
    GROUP BY       collapse remaining rows into one row per group
       |
     HAVING        discard groups that fail the condition
       |
     SELECT        compute output expressions, assign aliases
       |           (window functions run here, chapter 5)
    DISTINCT       remove duplicate output rows
       |
    ORDER BY       sort (can use SELECT aliases)
       |
 LIMIT / OFFSET    keep only part of the sorted result

This is a logical model. The planner is free to execute the query in any physical order that gives the same result: it may filter before joining, use an index to avoid sorting, or stop early once LIMIT rows are found. But the logical order explains many rules that otherwise seem arbitrary:

Rule	Why (logical order)
WHERE cannot use a column alias defined in SELECT	WHERE runs before SELECT computes the alias
WHERE cannot contain aggregates like count(*)	Groups do not exist yet when WHERE runs. Use HAVING
ORDER BY can use a SELECT alias	ORDER BY runs after SELECT
LIMIT without ORDER BY returns arbitrary rows	Nothing sorted the rows before they were cut

SELECT name, price * 1.21 AS gross FROM products WHERE gross > 100;
-- ERROR:  column "gross" does not exist

SELECT name, price * 1.21 AS gross FROM products ORDER BY gross DESC LIMIT 2;   -- works

A query returns a table. The result of a SELECT is itself a table (a set of rows with named, typed columns). That is why you can put a query in the FROM clause of another query (4.5), and why the clauses compose so freely.

Key takeaways

• SQL is declarative: you describe the result, and the planner chooses how to compute it.
• Clauses are written in the order SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, but logically evaluated as FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.
• The logical order explains why WHERE cannot see aliases or aggregates while ORDER BY can see aliases.
• Every query produces a table, so queries can be nested inside other queries.

🧪 Practice

1. (Beginner) Create the sample schema. Write a query that returns the name and price of all products that cost more than 30, sorted from cheapest to most expensive.
2. (Intermediate) For each of these queries, predict whether it works, then run it and explain the result using the logical processing order: SELECT price AS p FROM products WHERE p > 10;, SELECT price AS p FROM products ORDER BY p;, SELECT status FROM orders WHERE count(*) > 1 GROUP BY status;.
3. (Interview) "In what order does a database evaluate the clauses of a SELECT statement, and does it actually execute them in that order?" Hint: Distinguish the logical order defined by the language from the physical plan, and give an example where the planner changes the physical order (pushing a filter below a join, or using an index to satisfy ORDER BY ... LIMIT).

Projection and Column Aliases

A table may have dozens of columns, but a particular screen or report needs only a few. Choosing which columns to return is called projection (the term comes from relational algebra, where it is written π). Projection also lets you compute new columns from existing ones: a price with tax, a full name, a flag.

Selecting columns and expressions. The select list is a comma-separated list of expressions. Each one becomes an output column:

SELECT name,                          -- a plain column
       price,
       price * 1.21 AS price_with_vat, -- a computed column with an alias
       upper(sku) "SKU Code"          -- AS is optional; double quotes allow spaces and capitals
FROM products
WHERE category_id = 3;

--      name     |  price  | price_with_vat | SKU Code
-- --------------+---------+----------------+----------
--  UltraBook 14 | 1299.00 |      1571.7900 | LAP-001
--  WorkBook 15  |  999.00 |      1208.7900 | LAP-002

Column aliases. AS alias renames an output column. Without an alias, PostgreSQL invents a name: the column name for plain columns, the function name for function calls (upper), and ?column? for anything else. Always alias computed columns: application code reads results by column name, and ?column? is useless there.

Aliases follow the usual identifier rules:

Alias	Resulting column name	Notes
AS total	total	Recommended: lowercase, no quotes
AS Total	total	Unquoted identifiers are folded to lowercase
AS "Total"	Total	Quoting preserves case, but then every reference must be quoted too
AS "order"	order	Quoting allows reserved words. Better to pick another name
total (no AS)	total	Legal, but easy to misread as a missing comma

The missing-comma trap shows why AS is worth writing:

SELECT name price FROM products;   -- one column named "price" that contains the names!

Table aliases. The same idea applies to tables: FROM products AS p lets you write p.name instead of products.name. Table aliases become essential when a query references several tables (4.3).

SELECT *. SELECT * returns all columns in table definition order. It is perfect for exploring in psql, but avoid it in application code:

Problem with SELECT *	Consequence
Fetches columns you do not need	More I/O and network traffic, especially for large text, jsonb, or bytea columns
The result shape changes when the table changes	Adding or reordering columns can break code that reads by position
Prevents index-only scans	An index that covers the needed columns cannot be used alone (6.3)
Hides intent	Readers cannot tell which columns the code actually uses

t.* selects all columns of one table in a join, for example SELECT o.*, c.name FROM orders o JOIN customers c ....

Key takeaways

• Projection chooses and computes output columns. Each select-list expression becomes one column.
• Alias every computed column with AS. Unquoted aliases are lowercased, quoted aliases keep their case.
• Write AS explicitly to avoid the missing-comma trap.
• Use SELECT * for exploration only. In code, list the columns you need.

🧪 Practice

1. (Beginner) Return each product's sku, name, and the value of its stock (price * stock) as a column named stock_value.
2. (Intermediate) Run SELECT price * 2, upper(name), name FROM products; and look at the column headers. Rewrite the query so that every column has a meaningful name, then add an alias that needs double quotes and explain what it costs you later.
3. (Interview) "Why do many teams forbid SELECT * in production code?" Hint: Think about network transfer, schema changes, index-only scans, and readability.

WHERE Filtering and Predicates

Tables hold every row ever stored. A question usually concerns a small subset: one customer's orders, products that are out of stock, orders from last month. The WHERE clause is the filter that keeps only the rows you care about. It is also where most of a query's performance is decided, because conditions in WHERE are what indexes accelerate.

How WHERE works. WHERE takes a predicate, an expression that evaluates to a boolean for each row. Rows where the predicate is true are kept. Rows where it is false or NULL are discarded (the NULL case is explained in NULL Semantics and Three-Valued Logic).

SELECT name, stock FROM products WHERE stock = 0;                 -- out of stock
SELECT id, status  FROM orders   WHERE status = 'pending';
SELECT name        FROM products WHERE NOT discontinued;          -- a boolean column is already a predicate

Common predicate forms

Predicate	Meaning	Example
a = b, a <> b, <, <=, >, >=	Comparison	price > 50
a BETWEEN x AND y	a >= x AND a <= y (both ends included)	price BETWEEN 25 AND 39
a IN (x, y, z)	Equal to any value in the list	status IN ('paid', 'shipped')
a LIKE pattern / ILIKE	Pattern match, case-sensitive / insensitive (4.2)	name ILIKE '%book%'
a IS NULL / a IS NOT NULL	Missing value test	shipped_at IS NULL
a IS DISTINCT FROM b	<> that treats NULL as a comparable value	email IS DISTINCT FROM 'x'
EXISTS (subquery)	Subquery returns at least one row (4.5)

Filtering by dates and times. Time-based filters are among the most common. Prefer half-open ranges (>= start, < end) to BETWEEN, because BETWEEN includes its upper bound:

-- All orders placed in February 2025
SELECT id, placed_at
FROM orders
WHERE placed_at >= '2025-02-01'
  AND placed_at <  '2025-03-01';    -- half-open: includes every instant of Feb 28, excludes Mar 1

-- Wrong: misses everything after midnight on Feb 28
-- WHERE placed_at BETWEEN '2025-02-01' AND '2025-02-28'

Sargable predicates. A predicate is sargable (from "Search ARGument ABLE") when an index on the column can be used to evaluate it. The rule of thumb: keep the indexed column alone on one side of the operator.

Not sargable	Sargable rewrite
WHERE date(placed_at) = '2025-02-11'	WHERE placed_at >= '2025-02-11' AND placed_at < '2025-02-12'
WHERE price * 1.21 > 100	WHERE price > 100 / 1.21
WHERE lower(email) = 'ana@example.com'	Keep it, but create an expression index on lower(email) (6.3)

On a six-row table this makes no difference. On a hundred-million-row table it is the difference between milliseconds and minutes.

Key takeaways

• WHERE keeps rows whose predicate is true. false and NULL both remove the row.
• BETWEEN includes both ends. Use half-open ranges (>= and <) for timestamps.
• Keep the filtered column alone on one side of the comparison so indexes can be used.

🧪 Practice

1. (Beginner) Find all products that are not discontinued and have fewer than 20 units in stock.
2. (Intermediate) Write a query for all orders placed in the first quarter of 2025 that have not shipped yet. Then write a second version that would fail to use an index on placed_at, and explain why.
3. (Interview) "What does sargable mean, and how would you rewrite WHERE extract(year FROM created_at) = 2024?" Hint: The function call hides the column from a plain B-tree index. Express the same condition as a range on the raw column.

Comparison and Logical Operators

Real filters combine several conditions: "paid or shipped orders, placed this year, and not from a test account". Combining conditions correctly requires knowing the operators, how they bind, and how PostgreSQL compares values of different types.

Comparison operators

Operator	Meaning	Notes
=	Equal	Not ==
<> or !=	Not equal	Identical. <> is the SQL standard spelling
<, <=, >, >=	Ordering	Work on numbers, text (by collation), dates, and more
BETWEEN x AND y	Inclusive range	NOT BETWEEN also exists
BETWEEN SYMMETRIC x AND y	Range where the bounds can be in either order	PostgreSQL extension
IS [NOT] DISTINCT FROM	Equality that treats two NULLs as equal	See NULL Semantics
IS [NOT] TRUE / FALSE / UNKNOWN	Boolean tests that never return NULL

Logical operators and precedence. AND, OR, and NOT combine predicates. They bind with different strength: NOT binds tighter than AND, and AND binds tighter than OR. Just as 1 + 2 * 3 is 7, not 9, the following query is not what it appears to be:

-- Intent: paid or shipped orders from customer 1
SELECT id, status FROM orders
WHERE status = 'paid' OR status = 'shipped' AND customer_id = 1;
-- Actually parsed as: status = 'paid' OR (status = 'shipped' AND customer_id = 1)
-- Returns order 5 (paid, customer 4) as well

-- Correct: parentheses make the intent explicit
SELECT id, status FROM orders
WHERE (status = 'paid' OR status = 'shipped') AND customer_id = 1;

-- Better: IN avoids the OR entirely
SELECT id, status FROM orders
WHERE status IN ('paid', 'shipped') AND customer_id = 1;

Add parentheses whenever a condition mixes AND and OR, even when precedence happens to give the right answer. The next reader should not have to remember the rules.

Row comparisons. PostgreSQL can compare whole tuples at once. Row comparison with < or > is lexicographic, like sorting words: compare the first elements, and only if they are equal, compare the second, and so on.

SELECT (1, 'b') = (1, 'b');      -- true
SELECT (2025, 1) < (2025, 3);    -- true: first fields equal, 1 < 3
SELECT (2024, 12) < (2025, 1);   -- true: 2024 < 2025, second field not considered

Row comparisons are the basis of keyset pagination.

Comparing different types. Comparisons need both sides to have compatible types. A quoted literal like '2025-02-01' has an "unknown" type until context gives it one, so it is converted to the column's type automatically. Two values of incompatible types are an error, not a silent conversion:

SELECT * FROM orders WHERE placed_at >= '2025-02-01';   -- the literal becomes a timestamptz
SELECT * FROM products WHERE sku = 42;
-- ERROR:  operator does not exist: text = integer

The error is a feature: other databases silently convert, which leads to surprising matches and unusable indexes. Casting is covered in Type Casting and Conversion.

Key takeaways

• <> and != are the same. = (not ==) tests equality.
• NOT binds tighter than AND, which binds tighter than OR. Always parenthesize mixed AND/OR conditions.
• IN (...) is clearer than chains of OR on the same column.
• Row comparisons like (a, b) < (x, y) are lexicographic.
• PostgreSQL does not silently compare incompatible types, so a type error usually points at a real bug.

🧪 Practice

1. (Beginner) Find products that are either in category 2 or cost less than 20, and are not discontinued. Write it once with parentheses and once without, and compare the results.
2. (Intermediate) Use a row comparison to return orders placed after ('2025-02-20 08:15+00', 3) when ordering by (placed_at, id). Explain how it differs from placed_at > '2025-02-20 08:15+00' AND id > 3.
3. (Interview) "What does WHERE a OR b AND c return?" Hint: Apply operator precedence, then think about what the author most likely meant.

NULL Semantics and Three-Valued Logic

Data is often incomplete. A customer did not give an email, an order has not shipped yet, a product has no category. SQL represents "no value here" with NULL. The difficulty is that NULL does not behave like a value: it means unknown, and almost every operation on an unknown produces another unknown. Most SQL bugs that "return no rows for no reason" come from this.

NULL is not a value. NULL is not zero, not an empty string, and not false. Consider NULL = NULL. If two customers both have an unknown email, are their emails the same? You cannot know, so the answer is unknown, which SQL also represents as NULL:

SELECT NULL = NULL   AS a,     -- NULL (unknown), not true
       NULL IS NULL  AS b,     -- true
       NULL <> 1     AS c,     -- NULL
       true OR NULL  AS d,     -- true: true no matter what the unknown is
       false AND NULL AS e,    -- false: false no matter what the unknown is
       true AND NULL AS f;     -- NULL: depends on the unknown

--  a | b | c | d | e | f
-- ---+---+---+---+---+---
--    | t |   | t | f |

(psql shows NULL as an empty cell. Run \pset null '(null)' to make it visible.)

Three-valued logic. Boolean expressions in SQL have three possible results: true, false, and unknown (NULL). The truth tables extend the usual ones:

a	b	a AND b	a OR b	NOT a
true	NULL	NULL	true	false
false	NULL	false	NULL	true
NULL	NULL	NULL	NULL	NULL

A helpful way to remember: replace NULL with "could be either". If the result is the same whichever value it is, that is the result. Otherwise the result is NULL.

Where it bites. WHERE keeps only rows whose predicate is true. An unknown result removes the row, just like false:

-- Customers whose email is not ana@example.com
SELECT name FROM customers WHERE email <> 'ana@example.com';
-- Ben Okafor, Diego Ruiz, Emma Weber, Farid Haddad
-- Chloe Martin is missing: NULL <> 'ana@example.com' is NULL, not true

-- Treat NULL as a regular comparable value
SELECT name FROM customers WHERE email IS DISTINCT FROM 'ana@example.com';
-- Ben Okafor, Chloe Martin, Diego Ruiz, Emma Weber, Farid Haddad

The worst case is NOT IN with a list (or subquery) that contains a NULL:

SELECT name FROM products WHERE category_id NOT IN (3, NULL);
-- (0 rows)
-- For category 2: 2 <> 3 AND 2 <> NULL  ->  true AND NULL  ->  NULL, so the row is dropped.
-- Every row is dropped. See 4.5 for the safe alternative, NOT EXISTS.

Testing and handling NULL

Need	Write	Not
Is it missing?	x IS NULL	x = NULL (always NULL, matches nothing)
Is it present?	x IS NOT NULL	x <> NULL
Equal, counting two NULLs as equal	x IS NOT DISTINCT FROM y	x = y
Replace NULL with a default	coalesce(x, default) (4.2)
Is a boolean definitely true?	flag IS TRUE (never returns NULL)	flag = true

How NULL behaves elsewhere

Context	Behavior
Arithmetic and most functions	NULL in, NULL out: 1 + NULL is NULL, upper(NULL) is NULL
String concatenation with ||	'a' || NULL is NULL. concat('a', NULL) is 'a'
Aggregates (sum, avg, count(col))	Ignore NULL inputs. count(*) counts rows, including those with NULL (4.4)
GROUP BY and DISTINCT	All NULLs go into one group / count as one distinct value
ORDER BY	NULL sorts as larger than every value: last in ASC, first in DESC
UNIQUE constraints	Multiple NULLs are allowed by default (NULLS NOT DISTINCT changes this, 2.2)
CHECK constraints	A CHECK that evaluates to NULL passes

SELECT count(*), count(email), count(DISTINCT country) FROM customers;
--  count | count | count
-- -------+-------+-------
--      6 |     5 |     5

Key takeaways

• NULL means unknown. Comparisons with NULL return NULL, and WHERE drops those rows.
• Use IS NULL / IS NOT NULL, never = NULL. Use IS DISTINCT FROM for NULL-aware equality.
• NOT IN with any NULL in the list returns no rows. Prefer NOT EXISTS.
• Aggregates skip NULLs, count(*) does not, and GROUP BY / DISTINCT treat all NULLs as one value.

🧪 Practice

1. (Beginner) List all orders that have not shipped yet, then all products without a category. Explain why WHERE shipped_at = NULL returns nothing.
2. (Intermediate) Predict the result of each expression, then check: NULL AND false, NULL OR false, NOT NULL, NULL IN (1, NULL), 1 IN (1, NULL), 2 NOT IN (1, NULL), NULL IS DISTINCT FROM NULL.
3. (Interview) "A query with WHERE id NOT IN (SELECT manager_id FROM employees) returns no rows even though there are clearly non-managers. Why?" Hint: Look at what manager_id contains for top-level employees, and evaluate NOT IN with three-valued logic.

DISTINCT and DISTINCT ON

A table can have many rows that, after projection, look identical: six customers but only five countries, ten order items but seven products. Sometimes you want each distinct value only once. Sometimes you want one representative row per group, such as each customer's latest order. PostgreSQL has a tool for each.

DISTINCT. SELECT DISTINCT removes duplicate rows from the result. Duplicates are judged on all selected columns together, and two NULLs count as equal here:

SELECT DISTINCT country FROM customers ORDER BY country;
-- DE, ES, FR, NG, PT        (FR appears once, although two customers live there)

SELECT DISTINCT country, referred_by FROM customers;
-- One row per distinct (country, referred_by) pair

DISTINCT is not free: PostgreSQL must sort or hash the whole result to find duplicates. If you find yourself adding DISTINCT to "fix" duplicate rows from a join, the join is usually wrong (it multiplies rows, 4.3), and DISTINCT hides the symptom while paying for it.

DISTINCT ON. A PostgreSQL extension that solves a very common problem: "for each X, give me the row with the highest/lowest/latest Y". DISTINCT ON (expressions) keeps the first row of each group of rows that are equal on the expressions. "First" is defined by ORDER BY, which must start with the same expressions:

-- Each customer's most recent order
SELECT DISTINCT ON (customer_id)
       customer_id, id AS order_id, placed_at
FROM orders
ORDER BY customer_id,          -- must lead with the DISTINCT ON expression
         placed_at DESC;       -- decides which row is "first" in each group

--  customer_id | order_id |       placed_at
-- -------------+----------+------------------------
--            1 |        2 | 2025-02-11 14:30:00+00
--            2 |        3 | 2025-02-20 08:15:00+00
--            3 |        4 | 2025-03-01 19:45:00+00
--            4 |        5 | 2025-03-03 12:00:00+00
--            5 |        6 | 2025-03-15 16:20:00+00

How it works:

 sorted by customer_id, placed_at DESC        DISTINCT ON (customer_id) keeps
 customer_id | order_id | placed_at
 ------------+----------+------------
      1      |    2     | 2025-02-11   <- first row for customer 1: kept
      1      |    1     | 2025-01-05      dropped
      2      |    3     | 2025-02-20   <- kept
      ...

If several rows tie on the ordering, the one kept is arbitrary. Add a tiebreaker (for example id DESC) to make the result deterministic.

Feature	DISTINCT	DISTINCT ON (expr)
Standard SQL	Yes	No (PostgreSQL only)
Removes	Rows that are identical in every selected column	All but the first row per value of expr
Other columns	Must all match to count as duplicates	Taken from the kept row
Needs ORDER BY	No	Effectively yes, starting with expr
Portable alternative		row_number() window function (5.2)

Key takeaways

• DISTINCT removes rows that are duplicates across all selected columns and treats NULLs as equal.
• Needing DISTINCT to fix a join usually means the join multiplies rows. Fix the join instead.
• DISTINCT ON (x) ... ORDER BY x, y returns one row per x, chosen by the order of y. It is the idiomatic PostgreSQL answer to "latest row per group".
• Add a unique tiebreaker to the ORDER BY so the result is deterministic.

🧪 Practice

1. (Beginner) List the distinct statuses that appear in orders, and the distinct (order_id) values in order_items.
2. (Intermediate) For each category, return the most expensive product (name and price) using DISTINCT ON. What happens to products with no category? What happens if two products in one category have the same top price?
3. (Interview) "How would you get the latest status update per device from a table of a billion status events?" Hint: Compare DISTINCT ON, a window function, and a LATERAL subquery with LIMIT 1 (4.3), and think about which index each one needs.

ORDER BY, LIMIT, and OFFSET

A table is a set of rows, and sets have no order. Without ORDER BY, PostgreSQL returns rows in whatever order is cheapest, which can change after an update, a VACUUM, or a different plan. Any output where order matters to a person or a program (a top-10 list, a page of results) must say so explicitly.

ORDER BY. Sorts the result by one or more expressions. Each can be ASC (the default) or DESC, and you can choose where NULLs go:

SELECT name, price FROM products
ORDER BY category_id NULLS FIRST,   -- products without a category first
         price DESC,                -- most expensive first within each category
         id;                        -- tiebreaker: makes the order fully deterministic

SELECT name, email FROM customers ORDER BY email NULLS FIRST;
-- Chloe Martin (NULL email) comes first; by default NULL sorts last in ASC order

You can sort by	Example	Notes
A column	ORDER BY price	Does not have to be in the select list
An expression	ORDER BY price * stock DESC
An output alias	ORDER BY gross	Only as a bare name, not inside an expression
An output position	ORDER BY 2	Fragile: breaks when the select list changes. Avoid in code

Text is sorted according to the column's collation (3.1), so 'a' vs 'B' may sort differently in different databases. ORDER BY name COLLATE "C" sorts by byte value.

LIMIT and OFFSET. LIMIT n returns at most n rows. OFFSET m skips the first m rows. Together they cut a "page" out of a sorted result:

-- Products ranked 3rd to 5th by price
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 2;

--         name        | price
-- --------------------+-------
--  Postgres Internals | 54.00
--  SQL in Practice    | 39.00
--  Wireless Mouse     | 29.90

The SQL-standard spelling is OFFSET 2 ROWS FETCH FIRST 3 ROWS ONLY. It also offers WITH TIES, which includes any extra rows that tie with the last one:

SELECT name, price FROM products
ORDER BY price DESC
FETCH FIRST 1 ROWS WITH TIES;    -- if two products shared the top price, both would be returned

Why OFFSET gets slow. OFFSET does not jump to a position. The database must produce and discard all skipped rows:

 OFFSET 0      LIMIT 20  -> produce 20 rows,      return 20
 OFFSET 1000   LIMIT 20  -> produce 1,020 rows,   return 20
 OFFSET 100000 LIMIT 20  -> produce 100,020 rows, return 20

Deep pages get slower in proportion to the offset. Offset pages also shift when rows are inserted or deleted between requests, so users may see a row twice or miss one. Keyset pagination solves both problems.

Key takeaways

• Without ORDER BY, row order is undefined, even if it looks stable in testing.
• Add a unique column (such as the primary key) as the last sort key to make the order deterministic.
• NULLS FIRST / NULLS LAST control where NULLs go. The default is last for ASC, first for DESC.
• LIMIT/OFFSET (or FETCH FIRST ... ROWS) paginate, but OFFSET must read and discard every skipped row.

🧪 Practice

1. (Beginner) Return the three products with the most stock, showing name and stock.
2. (Intermediate) Write a query that returns page 2 of orders (2 orders per page) sorted newest first. Then insert a new order and run it again. Explain what changed on page 2 and why.
3. (Interview) "Our admin page uses LIMIT 50 OFFSET n and page 2,000 takes seconds to load. Why, and what would you change?" Hint: Think about how many rows the database produces for that page, and what information the client could send instead of a page number.

Keyset Pagination

Infinite scroll, API cursors, and batch jobs that walk through a whole table all need to fetch "the next N rows" efficiently, no matter how deep they are. Keyset pagination (also called the seek method or cursor-based pagination) does this by remembering where the last page ended instead of how many rows came before.

The idea. With OFFSET, the client says "skip 10,000 rows". With a keyset, the client says "give me the rows after the last one I saw". If the sort key is indexed, the database jumps straight to that position in the index and reads only the next N entries. The cost is the same for page 1 and page 10,000.

An analogy: OFFSET is reading a book by counting pages from the start every time you resume. Keyset pagination is a bookmark.

The sort key must be unique. If you sort only by placed_at and two orders share a timestamp, "after the last one I saw" is ambiguous, and rows can be skipped or repeated. Add the primary key as a tiebreaker, and use a row comparison:

-- Supporting index: matches the ORDER BY exactly
CREATE INDEX orders_placed_at_id_idx ON orders (placed_at, id);

-- First page
SELECT id, customer_id, placed_at
FROM orders
ORDER BY placed_at, id
LIMIT 2;
-- returns orders 1 and 2; the client remembers the last row's key: ('2025-02-11 14:30+00', 2)

-- Next page: everything strictly after the last key seen
SELECT id, customer_id, placed_at
FROM orders
WHERE (placed_at, id) > ('2025-02-11 14:30+00', 2)   -- row comparison, can use the index
ORDER BY placed_at, id
LIMIT 2;
-- returns orders 3 and 4

The row comparison (placed_at, id) > (x, y) means placed_at > x OR (placed_at = x AND id > y). PostgreSQL can turn the row form directly into an index range scan, while the expanded OR form is often less efficient. For a descending sort, use < and ORDER BY placed_at DESC, id DESC, with all sort directions the same.

OFFSET versus keyset

Aspect	LIMIT/OFFSET	Keyset
Cost of page N	Grows with N	Constant (with a matching index)
Rows inserted or deleted between pages	Pages shift: duplicates or gaps	Stable: continues after the last row seen
Jump to an arbitrary page number	Yes	No, only next/previous
Total page count	Easy (but count(*) is itself expensive)	Not naturally
Sort key requirements	Any	Unique and indexed, combined with a tiebreaker
Client state	Page number	Last row's key values (often encoded as an opaque cursor token)

Mixed sort directions such as ORDER BY placed_at DESC, id ASC cannot be written as one row comparison. Either keep all directions the same, or expand the condition by hand.

Key takeaways

• Keyset pagination filters on the last seen sort key instead of skipping rows, so deep pages are as fast as the first one.
• The sort key must be unique. Add the primary key as a tiebreaker.
• Use a row comparison (a, b) > (x, y) with an index on (a, b) that matches the ORDER BY.
• It supports next/previous navigation, not jumping to page N.

🧪 Practice

1. (Beginner) Paginate products by id, 3 rows per page, using keyset pagination. Write the queries for the first three pages.
2. (Intermediate) Generate a 1,000,000-row table with generate_series (1.4) and compare EXPLAIN ANALYZE for page 40,000 (25 rows per page) with OFFSET and with a keyset condition.
3. (Interview) "Design the pagination for a public API that lists a user's transactions, newest first." Hint: Choose the sort key and tiebreaker, the index, what the cursor token contains, and how to handle a "previous page" request.

<a id="42-expressions-and-functions"></a>

4.2 Expressions and Functions

Any place in a query that takes a value (the select list, WHERE, ORDER BY, GROUP BY) can take an expression built from columns, literals, operators, and functions. This subchapter covers the most-used families: text and pattern matching, math, conversions between types, conditional logic, NULL handling, and regular expressions.

String Functions and Pattern Matching

Text arrives messy: names in inconsistent case, phone numbers with spaces, emails that need splitting into user and domain. Cleaning and reshaping text inside the query is often simpler and faster than fetching rows into the application and doing it there. PostgreSQL has a large library of string functions, and two operators, LIKE and ILIKE, for simple pattern matching.

Concatenation. || joins strings, but returns NULL if either side is NULL. concat() and concat_ws() (with separator) skip NULL arguments, which is usually what you want when assembling optional parts:

SELECT 'Ana' || ' ' || 'Silva';                    -- 'Ana Silva'
SELECT 'a' || NULL;                                -- NULL
SELECT concat('a', NULL, 'b');                     -- 'ab'
SELECT concat_ws(', ', 'Lisbon', NULL, 'PT');      -- 'Lisbon, PT': NULLs and their separators skipped
SELECT 5 || ' items';                              -- '5 items': non-text operands are converted

The most useful string functions

Function	Example	Result
length(s) / char_length(s)	length('Señor')	5 characters
octet_length(s)	octet_length('Señor')	6 bytes (ñ takes 2 bytes in UTF-8)
lower(s), upper(s), initcap(s)	initcap('hello wORLD')	Hello World
substring(s FROM start FOR len)	substring('UltraBook 14' FROM 1 FOR 5)	Ultra (positions start at 1)
left(s, n), right(s, n)	left('LAP-001', 3)	LAP
position(sub IN s) / strpos(s, sub)	position('-' IN 'LAP-001')	4 (0 if not found)
split_part(s, delim, n)	split_part('ana@example.com', '@', 2)	example.com
trim(s), ltrim, rtrim, btrim(s, chars)	btrim('xxhixx', 'x')	hi
lpad(s, len, fill), rpad	lpad('42', 5, '0')	00042
replace(s, from, to)	replace('a-b-c', '-', '/')	a/b/c
repeat(s, n), reverse(s)	repeat('ab', 3)	ababab
format(fmt, ...)	format('%s has %s items', 'Ana', 3)	Ana has 3 items
string_to_array(s, delim)	string_to_array('a,b,c', ',')	{a,b,c}

format() also has %I (quote as identifier) and %L (quote as literal), which matter when building dynamic SQL in functions (9.2):

SELECT format('SELECT * FROM %I WHERE x = %L', 'My Table', 'O''Reilly');
-- SELECT * FROM "My Table" WHERE x = 'O''Reilly'

LIKE and ILIKE. LIKE matches a whole string against a pattern with two wildcards: % matches any sequence of characters (including none), and _ matches exactly one character. ILIKE is the case-insensitive version (a PostgreSQL extension):

SELECT name FROM products WHERE name LIKE '%Book%';     -- UltraBook 14, WorkBook 15
SELECT name FROM products WHERE name ILIKE '%book%';    -- same rows: case ignored
SELECT sku  FROM products WHERE sku LIKE 'LAP-00_';     -- LAP-001, LAP-002
SELECT 'a_b' LIKE 'a\_b';                               -- true: backslash escapes the wildcard
SELECT '100%' LIKE '100!%' ESCAPE '!';                  -- true: custom escape character

Pattern	Matches	Does not match
'abc'	abc only (no wildcards: same as =)	abcd
'abc%'	abc, abcdef	xabc
'%abc'	abc, xyzabc	abcx
'%abc%'	anything containing abc	ab c
'a_c'	abc, a-c	ac, abbc

Operator synonyms: ~~ is LIKE, ~~* is ILIKE, and the !~~ forms negate them. You will see them in EXPLAIN output. PostgreSQL also supports the standard SIMILAR TO, a hybrid of LIKE and regular expressions. It has few advantages over real regular expressions.

Performance. A B-tree index can serve a pattern anchored at the start ('LAP%'), as long as the index uses the C collation or the text_pattern_ops operator class. A leading wildcard ('%book%') cannot use a B-tree at all. For "contains" searches on large tables, use a trigram index (pg_trgm, 13.2) or full-text search (5.3).

CREATE INDEX products_sku_pattern_idx ON products (sku text_pattern_ops);   -- serves sku LIKE 'LAP%'

Key takeaways

• || returns NULL if any operand is NULL. concat() and concat_ws() skip NULLs.
• String positions start at 1. length() counts characters, octet_length() counts bytes.
• LIKE uses % (any sequence) and _ (one character). ILIKE ignores case.
• Only start-anchored patterns can use a B-tree index (with text_pattern_ops or the C collation). Use trigram indexes for "contains" searches.

🧪 Practice

1. (Beginner) Return each customer's name, the domain of their email (the part after @), and their name in uppercase. What happens for Chloe Martin?
2. (Intermediate) Write a query that formats each product as 'LAP-001 | UltraBook 14 | 1299.00 EUR', padding the SKU to 10 characters, using format() or rpad(). Then find all products whose SKU prefix (the part before -) has exactly three letters, using LIKE.
3. (Interview) "Users complain that searching products by name with ILIKE '%term%' is slow on a 20-million-row table. What are your options?" Hint: Explain why a B-tree does not help here, and compare trigram indexes with full-text search for this use case.

Mathematical Functions

Business queries compute constantly: totals with tax, discounts, averages, percentages, rounding for display. Getting numbers right in SQL requires knowing two things beyond the functions themselves: how the type of each operand affects the result, and how rounding behaves.

Arithmetic operators

Operator	Example	Result	Note
+, -, *	2 * 3.5	7.0
/	7 / 2	3	Integer division truncates toward zero
/	7 / 2.0	3.5000000000000000	Any numeric operand gives a numeric result
% or mod()	-7 % 3	-1	Sign follows the dividend
^ or power()	2 ^ 10	1024
|/, ||/	|/ 16	4	Square root, cube root (prefer sqrt(), cbrt())
@ or abs()	abs(-5)	5

The integer division trap is common in percentage calculations:

SELECT 3 / 4 * 100;            -- 0: 3 / 4 is 0 in integer arithmetic
SELECT 3 * 100 / 4;            -- 75: reorder to multiply first
SELECT 3::numeric / 4 * 100;   -- 75.00000000000000000000: make one operand numeric
SELECT 1 / 0;                  -- ERROR:  division by zero

Rounding and related functions

Function	Example	Result	Note
round(x)	round(2.5), round(-2.5)	3, -3	numeric: half rounds away from zero
round(x) on double precision	round(2.5::float8)	2	Floats round half to even
round(x, digits)	round(3.14159, 2)	3.14	numeric only
trunc(x, digits)	trunc(3.789, 1)	3.7	Cuts off without rounding
ceil(x), floor(x)	ceil(4.1), floor(-4.1)	5, -5	Round up / down
sign(x)	sign(-3)	-1
div(a, b)	div(-7, 2)	-3	Integer quotient
gcd(a, b), lcm(a, b)	gcd(12, 18)	6

Other functions

Function	Example	Result
sqrt, cbrt, exp, ln, log, log(b, x)	log(100), log(2, 8)	2, 3.0000000000000000
power(a, b)	power(2, 0.5)	1.4142135623730950
pi()	pi()	3.141592653589793
greatest(...), least(...)	greatest(1, 5, NULL, 3)	5 (NULLs ignored)
width_bucket(x, low, high, n)	width_bucket(42, 0, 100, 4)	2: which of 4 equal buckets
random()	random()	A double precision in [0, 1)
random(min, max)	random(1, 6)	An integer die roll (PostgreSQL 17+)

Float versus numeric. double precision is binary floating point (3.1), so decimal fractions are approximated:

SELECT 0.1::float8 + 0.2::float8;               -- 0.30000000000000004
SELECT 0.1::float8 + 0.2::float8 = 0.3::float8; -- false
SELECT 0.1 + 0.2;                               -- 0.3: unadorned decimal literals are numeric

Use numeric for money and anything that must add up exactly, and round only for display, at the very end of a calculation.

Example: an order total report

SELECT order_id,
       sum(quantity * unit_price)                    AS net,
       round(sum(quantity * unit_price) * 0.21, 2)   AS vat,     -- 21% VAT, rounded to cents
       round(sum(quantity * unit_price) * 1.21, 2)   AS gross
FROM order_items
GROUP BY order_id
ORDER BY order_id;

Key takeaways

• Integer divided by integer truncates. Cast one operand to numeric or reorder the calculation.
• numeric rounds half away from zero, while double precision rounds half to even.
• Floats cannot represent most decimal fractions exactly. Use numeric for money.
• greatest() and least() ignore NULLs, and width_bucket() assigns values to histogram buckets.

🧪 Practice

1. (Beginner) For each product, show the price, the price with a 15% discount rounded to 2 decimals, and the price rounded up to the next whole number.
2. (Intermediate) Compute what percentage of all ordered units each product represents. Get it wrong first with integer division, then fix it so the percentages add up to 100 (within rounding).
3. (Interview) "Should prices be stored as float, numeric, or integer cents?" Hint: Compare exactness, performance, how rounding is handled, and what the application language does with each type.

Type Casting and Conversion

Values often arrive in the wrong type. A web form sends '42' as text, a CSV gives dates as '11/02/2025', and a report needs a number formatted as '1,234.50'. PostgreSQL is strictly typed: it will not guess that '42' is a number when comparing it with an integer column, so you must convert explicitly. The upside is that bad data fails loudly instead of being silently misinterpreted.

Cast syntax. There are three equivalent forms:

SELECT CAST('42' AS integer);       -- SQL standard
SELECT '42'::integer;               -- PostgreSQL shorthand, the most common in practice
SELECT integer '42';                -- typed literal: only for string constants, not expressions

SELECT '2025-02-11'::date + 1;      -- 2025-02-12
SELECT 5::text || ' items';         -- '5 items'
SELECT 3.7::int, (-3.5)::int;       -- 4, -4: numeric to integer rounds (half away from zero)
SELECT 'yes'::boolean, 'on'::bool;  -- true, true

Casts check the value, and an invalid one is an error for the whole statement:

SELECT 'abc'::int;          -- ERROR:  invalid input syntax for type integer: "abc"
SELECT '2025-02-30'::date;  -- ERROR:  date/time field value out of range: "2025-02-30"
SELECT 70000::smallint;     -- ERROR:  smallint out of range

To test a value before casting it (for example while cleaning imported data), PostgreSQL 16 added pg_input_is_valid():

SELECT raw, CASE WHEN pg_input_is_valid(raw, 'integer') THEN raw::int END AS parsed
FROM (VALUES ('42'), ('abc'), (' 7 ')) AS t(raw);
--  raw | parsed
-- -----+--------
--  42  |     42
--  abc |
--   7  |      7

Implicit casts. PostgreSQL converts automatically in a few safe situations: an untyped string literal takes the type the context needs (WHERE placed_at >= '2025-02-01'), and numbers widen when mixed (integer + numeric is numeric). Use pg_typeof() to see what type an expression has:

SELECT pg_typeof(1), pg_typeof(1.5), pg_typeof(1 + 1.5), pg_typeof(1::bigint + 1);
-- integer, numeric, numeric, bigint

Conversions that might lose information or change meaning, such as text to integer, are never implicit.

Formatting functions. Casting to text gives the default format. For a specific format, use to_char() and its inverse functions:

Function	Example	Result
to_char(number, fmt)	to_char(1234.5, 'FM9,999.00')	1,234.50 (FM removes padding)
to_char(timestamp, fmt)	to_char(placed_at, 'YYYY-MM-DD HH24:MI')	2025-02-11 14:30
to_number(text, fmt)	to_number('1,234.50', '9G999D99')	1234.50
to_date(text, fmt)	to_date('11/02/2025', 'DD/MM/YYYY')	2025-02-11
to_timestamp(text, fmt)	to_timestamp('2025-02-11 14:30', 'YYYY-MM-DD HH24:MI')	timestamptz
to_timestamp(epoch)	to_timestamp(1739284200)	Unix seconds to timestamptz

Casts and indexes. A cast on a column in WHERE hides it from an ordinary index, just like a function call does:

-- Not sargable: the cast is applied to every row's column value
SELECT * FROM orders WHERE placed_at::date = '2025-02-11';

-- Sargable: cast the constant, not the column
SELECT * FROM orders WHERE placed_at >= '2025-02-11' AND placed_at < '2025-02-12';

Key takeaways

• Cast with ::type (PostgreSQL) or CAST(x AS type) (standard). Both validate the value and fail on bad input.
• PostgreSQL casts implicitly only when it is safe. Most other conversions must be written explicitly.
• Use pg_input_is_valid() (16+) to test values before casting, and pg_typeof() to inspect types.
• Use to_char(), to_number(), to_date(), and to_timestamp() for custom formats.
• Cast constants, not columns, in WHERE so indexes stay usable.

🧪 Practice

1. (Beginner) Display each order's placed_at as 'DD Mon YYYY' (for example 05 Jan 2025), and each product's price as text with a ' EUR' suffix.
2. (Intermediate) Create a staging(raw_qty text) table with values '10', ' 3 ', 'abc', '', NULL, and '2.5'. Write a query that returns the integer quantity where valid and NULL otherwise, without the query failing.
3. (Interview) "Why does PostgreSQL raise operator does not exist: text = integer when MySQL would just run the query?" Hint: Discuss implicit conversion rules, the surprising matches they cause (for example 'abc' = 0 in some databases), and the effect on index usage.

CASE Expressions

Queries often need if-then-else logic: label orders by status for display, sort "urgent" rows first, put prices into tiers, or count only rows that match a condition. CASE is SQL's conditional expression. It returns a value, so it can appear anywhere an expression can.

Searched CASE. Evaluates conditions in order and returns the result of the first one that is true:

SELECT name, price,
       CASE
         WHEN price >= 500 THEN 'premium'    -- conditions are checked top to bottom
         WHEN price >= 30  THEN 'standard'   -- only reached when price < 500
         ELSE 'budget'                       -- when no condition is true
       END AS tier
FROM products
ORDER BY price DESC;

--         name        |  price  |   tier
-- --------------------+---------+----------
--  UltraBook 14       | 1299.00 | premium
--  WorkBook 15        |  999.00 | premium
--  Postgres Internals |   54.00 | standard
--  SQL in Practice    |   39.00 | standard
--  Wireless Mouse     |   29.90 | budget
--  Gift Card          |   25.00 | budget
--  USB-C Cable        |   12.50 | budget

Simple CASE. Compares one expression with a list of values. It is shorter when every branch is an equality test on the same expression:

SELECT id,
       CASE status
         WHEN 'pending' THEN 'Awaiting payment'
         WHEN 'paid'    THEN 'Preparing'
         WHEN 'shipped' THEN 'On its way'
       END AS label                          -- no ELSE: unmatched values give NULL
FROM orders;

Simple CASE uses =, so CASE x WHEN NULL THEN ... never matches. Use the searched form with WHEN x IS NULL.

Rules to remember

Rule	Consequence
Branches are tested in order, and the first true one wins	Put the most specific conditions first
A missing ELSE means ELSE NULL	Unexpected values silently become NULL
All results must have a common type	CASE WHEN ... THEN 1 ELSE 'x' END is an error
Evaluation is usually lazy	CASE WHEN n = 0 THEN 0 ELSE 100 / n END avoids division by zero (but constant subexpressions may be evaluated early during planning)

Where CASE is useful

-- Custom sort order: pending orders first, then paid, then the rest
SELECT id, status FROM orders
ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'paid' THEN 2 ELSE 3 END, id;

-- Conditional counting ("pivoting"): one row with a column per status
SELECT count(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,     -- count skips the NULLs
       count(CASE WHEN status = 'pending' THEN 1 END) AS pending
FROM orders;
-- The FILTER clause (4.4) is a cleaner way to write this in PostgreSQL

-- Conditional update: different discount per tier
UPDATE products
SET price = CASE WHEN price >= 500 THEN price * 0.90 ELSE price * 0.95 END
WHERE NOT discontinued;

Key takeaways

• Searched CASE WHEN cond THEN ... handles any conditions. Simple CASE x WHEN value THEN ... handles equality on one expression.
• The first matching branch wins, and without ELSE the result is NULL.
• All branches must return compatible types.
• CASE works in SELECT, WHERE, ORDER BY, GROUP BY, inside aggregates, and in UPDATE ... SET.

🧪 Practice

1. (Beginner) Label each product's stock as 'out of stock' (0), 'low' (1 to 20), or 'ok' (more than 20).
2. (Intermediate) In one query, return the number of products in each stock label from exercise 1 as three columns of one row. Then sort the products so out-of-stock items come first, then by name.
3. (Interview) "Can you rely on CASE to protect against division by zero?" Hint: Usually yes. Look up what the PostgreSQL documentation says about constant subexpressions and aggregates inside CASE branches, and consider nullif() as an alternative.

COALESCE and NULLIF

Two NULL-handling patterns appear constantly: "use this value, or a default if it is missing" and "treat this special value as missing". coalesce() and nullif() cover them in a single function call each, and they are shorter and clearer than the equivalent CASE.

COALESCE. Returns the first argument that is not NULL, or NULL if all of them are. Arguments after the first non-NULL one are not evaluated:

SELECT coalesce(NULL, NULL, 3, 4);                      -- 3

SELECT name, coalesce(email, 'no email') AS contact
FROM customers;
-- Chloe Martin | no email

-- Fallback chain: shipped date, otherwise placed date
SELECT id, coalesce(shipped_at, placed_at) AS last_event FROM orders;

All arguments must share a type, so the default must match the column:

SELECT coalesce(email, 0) FROM customers;
-- ERROR:  COALESCE types text and integer cannot be matched

A common use is making aggregates return 0 instead of NULL when there are no rows:

SELECT coalesce(sum(quantity), 0) AS units_sold
FROM order_items
WHERE product_id = 7;          -- the gift card was never ordered: sum() alone returns NULL

NULLIF. nullif(a, b) returns NULL if a = b, and a otherwise. It is the reverse of coalesce: it turns a specific value into NULL.

SELECT nullif(5, 5), nullif(5, 0);         -- NULL, 5

-- Avoid division by zero: dividing by NULL gives NULL instead of an error
SELECT name, stock, price / nullif(stock, 0) AS price_per_unit_in_stock
FROM products
WHERE category_id = 3;
--      name     | stock | price_per_unit_in_stock
-- --------------+-------+-------------------------
--  UltraBook 14 |     5 |    259.8000000000000000
--  WorkBook 15  |     0 |

-- Treat empty or blank strings as missing
SELECT nullif(trim(' '), '') IS NULL;      -- true

Combining them. coalesce(nullif(x, ''), 'default') reads as "if x is missing or empty, use the default".

Expression	Equivalent CASE
coalesce(a, b)	CASE WHEN a IS NOT NULL THEN a ELSE b END
nullif(a, b)	CASE WHEN a = b THEN NULL ELSE a END
coalesce(nullif(a, ''), 'n/a')	CASE WHEN a IS NULL OR a = '' THEN 'n/a' ELSE a END

Key takeaways

• coalesce(a, b, ...) returns the first non-NULL argument. All arguments must have a common type.
• Wrap aggregates with coalesce(..., 0) when "no rows" should be shown as zero.
• nullif(a, b) turns a into NULL when it equals b, and x / nullif(y, 0) avoids division-by-zero errors.
• Both are shorthand for CASE and make intent clearer.

🧪 Practice

1. (Beginner) List each order with its shipped_at, showing the text 'not shipped' when it is NULL. (Hint: you will need a cast.)
2. (Intermediate) For each product, compute the ratio of price to stock without errors. Then compute the total units sold per product, showing 0 for products that were never ordered.
3. (Interview) "What is the difference between coalesce(x, 0) in the select list and filtering with WHERE x IS NOT NULL?" Hint: One changes values, the other changes which rows are returned. Think about how each affects avg(x).

Regular Expressions in Postgres

LIKE handles simple wildcards, but many text problems need more: validate an email format, extract the number from 'Order #12345', strip every non-digit from a phone number, split text on any kind of whitespace. Regular expressions (regex) are a small language for describing text patterns, and PostgreSQL supports POSIX-style regular expressions (similar to Perl's) through operators and functions.

Matching operators

Operator	Meaning	Example	Result
~	Matches (case-sensitive)	'Postgres' ~ '^Post'	true
~*	Matches (case-insensitive)	'Ana' ~* '^ana$'	true
!~	Does not match	'abc' !~ 'x'	true
!~*	Does not match (case-insensitive)

Unlike LIKE, a regex matches anywhere in the string unless you anchor it with ^ (start) and $ (end).

Regex syntax essentials

Pattern	Matches
.	Any single character
[abc], [^abc], [a-z]	One of / none of / a range of characters
\d, \w, \s	A digit, a word character, whitespace (\D, \W, \S negate them)
*, +, ?	0 or more, 1 or more, 0 or 1 of the previous item
{n}, {n,m}	Exactly n, between n and m repetitions
^, $	Start and end of the string
(...)	A capture group
a|b	Alternation: a or b

Backslashes are literal inside standard SQL strings (standard_conforming_strings is on by default), so '\d+' reaches the regex engine as \d+.

Regex functions

-- Validate (a deliberately simple email check)
SELECT 'ana@example.com' ~ '^[^@\s]+@[^@\s]+\.[a-z]{2,}$';       -- true

-- Extract the first match (or the first capture group)
SELECT substring('Order #12345 shipped' FROM '#(\d+)');            -- '12345'

-- Extract all capture groups of the first match as an array
SELECT regexp_match('LAP-001', '^([A-Z]+)-(\d+)$');                -- {LAP,001}
SELECT (regexp_match('LAP-001', '^([A-Z]+)-(\d+)$'))[2];           -- '001'

-- Every match: regexp_matches with the 'g' flag returns one row per match
SELECT m[1] AS tag
FROM regexp_matches('#sql and #postgres are #fun', '#(\w+)', 'g') AS m;
-- sql, postgres, fun (three rows)

-- Replace: 'g' replaces all matches; \1, \2 refer to capture groups
SELECT regexp_replace('555-123-4567', '\D', '', 'g');              -- '5551234567'
SELECT regexp_replace('a  b   c', '\s+', ' ', 'g');                -- 'a b c'
SELECT regexp_replace('LAP-001', '^([A-Z]+)-0*(\d+)$', '\2-\1');   -- '1-LAP'

-- Split
SELECT regexp_split_to_array('one  two three', '\s+');             -- {one,two,three}
SELECT regexp_split_to_table('a,b;;c', '[,;]+');                   -- a, b, c (three rows)

PostgreSQL 15 added functions that mirror other databases: regexp_count('banana', 'an') is 2, regexp_like(s, pattern, 'i') is a function form of ~*, regexp_substr() returns the matched text, and regexp_instr() returns its position.

Task	Use
Does it match?	~, ~*, regexp_like()
Get the first match or group	substring(s FROM pattern), regexp_substr()
Get all groups of the first match	regexp_match()
Get every match as rows	regexp_matches(..., 'g')
Replace	regexp_replace(s, pattern, replacement, 'g')
Split	regexp_split_to_array(), regexp_split_to_table()

Using regexes in constraints. A CHECK with ~ enforces a format at write time (2.2):

ALTER TABLE products
  ADD CONSTRAINT sku_format CHECK (sku ~ '^[A-Z]{2,5}-\d{3}$');

Performance. Unanchored regex searches cannot use a B-tree index and scan every row. A pg_trgm GIN index can speed up many regex searches on large tables (13.2). Regexes are also slower than LIKE or plain functions for simple tasks, so do not use a regex where left() or split_part() will do.

Key takeaways

• ~ and ~* test for a match anywhere in the string. Anchor with ^ and $ to match the whole string.
• substring(... FROM pattern) and regexp_match() extract text, regexp_matches(..., 'g') returns every match as rows.
• regexp_replace(..., 'g') replaces all matches. Without 'g', only the first match is replaced.
• Regexes are powerful but expensive. Use them for validation and extraction, and index with pg_trgm for searches.

🧪 Practice

1. (Beginner) Find all products whose SKU ends with an even digit, and all customers whose email domain is example.com, using regex operators.
2. (Intermediate) Given 'Call +1 (555) 123-4567 or +44 20 7946 0958', write one query that returns each phone number on its own row with only digits and the leading +.
3. (Interview) "How would you validate email addresses in the database?" Hint: Discuss how strict a regex can realistically be, whether a CHECK or a domain type (3.5) is better, case-insensitive uniqueness, and why the only real validation is sending an email.

<a id="43-joining-tables"></a>

4.3 Joining Tables

A normalized schema (2.3) splits data into many tables so each fact is stored once. Joins put the facts back together at query time. This subchapter covers every join type PostgreSQL supports, what each one returns, the classic traps (lost rows, multiplied rows), and how to write multi-table queries that stay readable.

Inner Joins

An order row stores customer_id = 3, not the customer's name. To show "order 4 was placed by Chloe Martin", you must match each order with the customer row whose id equals the order's customer_id. That matching is a join, and the inner join is the most common kind: it returns only the pairs of rows that match.

How it works. Conceptually, an inner join considers every combination of a row from the left table and a row from the right table, and keeps the combinations where the ON condition is true:

 orders                       customers
 id | customer_id             id | name
 ---+------------             ---+-------------
  1 |  1   --------------->    1 | Ana Silva
  2 |  1   ------------/       2 | Ben Okafor
  3 |  2   --------------->    3 | Chloe Martin
  4 |  3   --------------->    ...
                               6 | Farid Haddad   <- no matching order: not in the result

SELECT o.id AS order_id, c.name AS customer, o.status
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id     -- JOIN means INNER JOIN
ORDER BY o.id;

--  order_id |   customer   |  status
-- ----------+--------------+-----------
--         1 | Ana Silva    | shipped
--         2 | Ana Silva    | shipped
--         3 | Ben Okafor   | pending
--         4 | Chloe Martin | cancelled
--         5 | Diego Ruiz   | paid
--         6 | Emma Weber   | shipped

Customer 1 appears twice, once per matching order. Farid has no orders, so he does not appear at all. These are the two defining properties of an inner join: a row appears once per match, and rows with no match disappear.

The database does not literally build every combination. It uses a nested loop, hash join, or merge join to find matches efficiently (7.3). The conceptual model only defines what the result is.

Joining several tables. Each additional JOIN adds another table to the rows built so far. Follow the foreign keys:

-- Shipped orders with customer and product details
SELECT o.id, c.name, p.name AS product, oi.quantity
FROM orders      AS o
JOIN customers   AS c  ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products    AS p  ON p.id = oi.product_id
WHERE o.status = 'shipped'
ORDER BY o.id, p.name;

--  id |    name    |      product       | quantity
-- ----+------------+--------------------+----------
--   1 | Ana Silva  | USB-C Cable        |        2
--   1 | Ana Silva  | UltraBook 14       |        1
--   2 | Ana Silva  | Postgres Internals |        1
--   2 | Ana Silva  | SQL in Practice    |        1
--   6 | Emma Weber | SQL in Practice    |        2
--   6 | Emma Weber | USB-C Cable        |        1

ON versus WHERE for inner joins. For an inner join, a condition gives the same result whether it is in ON or WHERE. By convention, put the conditions that relate the tables in ON and the conditions that filter rows in WHERE. For outer joins, the placement changes the result (next topic).

The old comma syntax. FROM orders o, customers c WHERE o.customer_id = c.id is an older way to write an inner join. It works, but if you forget the condition you silently get a cross join, and the join conditions are mixed with the filters. Prefer explicit JOIN ... ON.

Ambiguous columns. When two joined tables share a column name (id, name), you must qualify it with the table or alias. Qualify every column in a multi-table query, even unambiguous ones: the query then keeps working when someone adds a same-named column to another table.

Key takeaways

• An inner join returns one row per matching pair of rows. Rows without a match on either side are dropped.
• JOIN and INNER JOIN are the same. Put relationship conditions in ON and filters in WHERE.
• Follow foreign keys to join several tables, and qualify every column with a table alias.
• Prefer explicit JOIN ... ON over comma-separated tables in FROM.

🧪 Practice

1. (Beginner) List every product with the name of its category. Which product is missing from the result, and why?
2. (Intermediate) For every order line, return the order id, customer name, product name, quantity, and line total (quantity * unit_price), sorted by order id. How many rows do you expect before running it?
3. (Interview) "Explain what an inner join returns, without using a Venn diagram." Hint: Describe it as a filtered set of combinations of rows, and explain why a row can appear several times or not at all.

Left, Right, and Full Outer Joins

Inner joins drop rows without a match, which is often wrong. "List all customers and their orders" must include customers who have not ordered yet. "Products with their category" should not lose the gift card just because it has no category. Outer joins keep unmatched rows and fill the missing side with NULLs.

LEFT JOIN. Returns every row of the left table. Where the right table has matches, they are joined as with an inner join. Where there is no match, the left row appears once with NULL in every right-side column:

SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id     -- LEFT JOIN = LEFT OUTER JOIN
ORDER BY c.id, o.id;

--      name     | order_id
-- --------------+----------
--  Ana Silva    |        1
--  Ana Silva    |        2
--  Ben Okafor   |        3
--  Chloe Martin |        4
--  Diego Ruiz   |        5
--  Emma Weber   |        6
--  Farid Haddad |              <- kept, with NULL for the order columns

The anti-join pattern. Combining a LEFT JOIN with IS NULL on a right-side column that cannot otherwise be NULL finds rows that have no match:

-- Customers who never ordered
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;           -- test the primary key (or another NOT NULL column) of the right table
-- Farid Haddad

NOT EXISTS expresses the same thing more directly (4.5), and PostgreSQL plans both as an anti-join.

The ON versus WHERE trap. With an outer join, a condition on the right table means different things in ON and in WHERE:

-- Condition in WHERE: applied AFTER the join, removes the NULL-extended rows
SELECT c.name, o.id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';
-- Ana Silva 1, Ana Silva 2, Emma Weber 6: customers without shipped orders vanished,
-- so the LEFT JOIN silently became an inner join

-- Condition in ON: part of the matching, every customer is kept
SELECT c.name, o.id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'
ORDER BY c.id, o.id;
-- Ana Silva 1, Ana Silva 2, Ben Okafor NULL, Chloe Martin NULL,
-- Diego Ruiz NULL, Emma Weber 6, Farid Haddad NULL

Rule: conditions on the optional (NULL-extended) side of an outer join belong in ON, unless you deliberately want to remove the unmatched rows.

RIGHT JOIN. The mirror image: keeps every row of the right table. A RIGHT JOIN B is the same as B LEFT JOIN A. Most teams write only LEFT JOIN, ordering the tables so the "keep everything" table comes first, because a mix of left and right joins is hard to read.

FULL JOIN. Keeps unmatched rows from both sides:

SELECT p.name AS product, cat.name AS category
FROM products p
FULL JOIN categories cat ON cat.id = p.category_id
ORDER BY cat.id NULLS LAST, p.id;

--       product       |  category
-- --------------------+-------------
--                     | Electronics    <- category with no products directly in it
--  SQL in Practice    | Books
--  ...
--  Gift Card          |                <- product with no category

FULL JOIN is typical for reconciliation: comparing two lists (yesterday's and today's inventory, ledger versus bank statement) to find rows missing on either side.

Summary of join types

Join	Matched pairs	Unmatched left rows	Unmatched right rows
[INNER] JOIN	Kept	Dropped	Dropped
LEFT [OUTER] JOIN	Kept	Kept, right side NULL	Dropped
RIGHT [OUTER] JOIN	Kept	Dropped	Kept, left side NULL
FULL [OUTER] JOIN	Kept	Kept	Kept

Chaining outer joins. Once a table is outer-joined, every later join to it should usually also be an outer join. An inner join to the optional side removes the rows the outer join kept:

-- Farid disappears: the inner join to order_items requires a non-NULL o.id
SELECT c.name, o.id, oi.product_id
FROM customers c
LEFT JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi      ON oi.order_id = o.id;      -- should be LEFT JOIN

Key takeaways

• LEFT JOIN keeps every left row and fills missing right-side columns with NULL. FULL JOIN keeps unmatched rows from both sides.
• LEFT JOIN ... WHERE right.pk IS NULL finds rows with no match (an anti-join).
• Conditions on the optional side go in ON. Putting them in WHERE turns the outer join into an inner join.
• Prefer LEFT JOIN over RIGHT JOIN, and keep joins to an outer-joined table outer as well.

🧪 Practice

1. (Beginner) List all products with their category name, including products without a category, showing 'Uncategorized' for them.
2. (Intermediate) List every customer with the number of orders they placed in 2025 that were not cancelled, including customers with zero. Check that Chloe Martin and Farid Haddad both appear with 0.
3. (Interview) "A report uses LEFT JOIN but some rows from the left table are still missing. What are the likely causes?" Hint: Look for conditions on the right table in WHERE, and for inner joins chained after the outer join.

Cross Joins and Cartesian Products

Sometimes you really do want every combination of two sets: every size with every color to generate product variants, every store with every day to build a calendar for a report, every user with every feature flag. A cross join produces this full Cartesian product. It is also what you get by accident when a join condition is missing.

How it works. A cross join has no condition. Each row of the left table is paired with each row of the right table, so the result has rows(A) × rows(B) rows:

SELECT s.size, c.color
FROM (VALUES ('S'), ('M')) AS s(size)                 -- VALUES builds an inline table
CROSS JOIN (VALUES ('red'), ('blue')) AS c(color);

--  size | color
-- ------+-------
--  S    | red
--  S    | blue
--  M    | red
--  M    | blue

  {S, M}  x  {red, blue}  =  {(S,red), (S,blue), (M,red), (M,blue)}      2 x 2 = 4 rows

These three forms are equivalent:

SELECT * FROM a CROSS JOIN b;
SELECT * FROM a, b;                  -- comma in FROM, no condition
SELECT * FROM a JOIN b ON true;      -- a join whose condition is always true

Useful cross joins: filling in gaps. Reports often need a row for every combination, even ones with no data. Cross join the dimensions, then outer join the facts:

-- Orders per customer per month for Q1 2025, including months with zero orders
SELECT c.name, m.month::date, count(o.id) AS orders
FROM customers AS c
CROSS JOIN generate_series('2025-01-01'::timestamptz,
                           '2025-03-01'::timestamptz,
                           interval '1 month') AS m(month)          -- one row per month
LEFT JOIN orders AS o
       ON o.customer_id = c.id
      AND o.placed_at >= m.month
      AND o.placed_at <  m.month + interval '1 month'
GROUP BY c.name, m.month
ORDER BY c.name, m.month;
-- 6 customers x 3 months = 18 rows, most of them with 0 orders

Accidental cross joins. The danger is size. Two 10,000-row tables produce 100,000,000 rows. A missing condition in the comma syntax or in a multi-join query is the usual cause:

-- Forgot to relate orders to customers: 6 x 6 = 36 rows, every order "belongs" to every customer
SELECT c.name, o.id FROM customers c, orders o;

Warning signs: a query that should be fast runs for minutes, results contain obviously wrong pairings, or totals are multiplied by a suspiciously round factor. In a plan, look for a Nested Loop with no Join Filter and an enormous row estimate.

Key takeaways

• A cross join pairs every left row with every right row: rows(A) × rows(B) result rows.
• Use it deliberately with small sets: variant generation, and calendars or grids that you then outer join facts onto.
• Accidental cross joins come from missing join conditions, most often in comma-style FROM lists.

🧪 Practice

1. (Beginner) Generate all combinations of the sizes S, M, L and the colors red, green, blue as SKU strings like 'TSHIRT-M-red'.
2. (Intermediate) Build a report of units sold per product per month from January to March 2025 that includes a row with 0 for every product-month without sales.
3. (Interview) "A dashboard query's totals are exactly 4 times too high. How do you debug it?" Hint: Count the rows before aggregating, check each join condition, and look for a join that multiplies rows (a missing condition or a one-to-many join).

Self Joins

Some relationships point from a table back to itself: an employee's manager is another employee, a customer was referred by another customer, a category's parent is another category. To show "Hugo reports to Grace", you need two rows of the same table side by side. A self join does this by referencing the table twice under different aliases.

How it works. There is no special syntax. You join the table to itself, and the aliases make the two roles clear:

SELECT e.name AS employee, m.name AS manager
FROM employees AS e                               -- role 1: the employee
LEFT JOIN employees AS m ON m.id = e.manager_id   -- role 2: the manager
ORDER BY e.id;

--  employee | manager
-- ----------+---------
--  Grace    |
--  Hugo     | Grace
--  Iris     | Grace
--  Jonas    |
--  Kim      | Jonas
--  Lena     | Jonas

LEFT JOIN keeps Grace and Jonas, who have no manager. Think of e and m as two separate copies of the table:

  employees AS e                        employees AS m
  id | name  | manager_id               id | name
  ---+-------+-----------               ---+-------
   2 | Hugo  |     1  ---------------->  1 | Grace
   5 | Kim   |     4  ---------------->  4 | Jonas

Comparing rows within a table. Self joins also compare rows with other rows in the same table:

-- Who referred whom
SELECT c.name AS customer, r.name AS referred_by
FROM customers c
JOIN customers r ON r.id = c.referred_by;
-- Ben Okafor <- Ana Silva, Chloe Martin <- Ana Silva, Emma Weber <- Diego Ruiz

-- Pairs of colleagues in the same department, each pair listed once
SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.department = b.department
                AND a.id < b.id;        -- < (not <>) avoids pairing someone with themselves
                                        -- and listing both (A,B) and (B,A)
-- Grace-Hugo, Grace-Iris, Hugo-Iris, Jonas-Kim, Jonas-Lena, Kim-Lena

-- Employees who earn at least 80% of their manager's salary
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary >= m.salary * 0.8;
-- Iris (7200, manager Grace 9000)

Limits. One self join goes one level up the hierarchy. Two self joins reach the grandparent, and so on. For hierarchies of unknown depth (a whole org chart, a category tree), use a recursive CTE (5.1).

Key takeaways

• A self join references the same table twice under different aliases, one per role.
• Use LEFT JOIN when the reference is optional (the top of a hierarchy has no parent).
• Use a.id < b.id to list each unordered pair of rows exactly once.
• Each self join climbs one level. Use recursive CTEs for arbitrary depth.

🧪 Practice

1. (Beginner) List each category with the name of its parent category, including top-level categories.
2. (Intermediate) Find all pairs of products in the same category whose prices differ by less than 20, listing each pair once.
3. (Interview) "How would you find the skip-level manager (the manager's manager) of every employee, and what would you do if the hierarchy were arbitrarily deep?" Hint: Two self joins solve the fixed-depth case. Look at WITH RECURSIVE for the general case.

Natural Joins and USING

Many joins compare columns that have the same name in both tables: order_items.order_id = shipments.order_id. SQL has two shorthands for this: USING, which names the shared columns, and NATURAL JOIN, which finds them automatically. One is a convenience, the other is a trap.

USING. JOIN t USING (col) is equivalent to ON a.col = t.col, with one difference: the result has a single merged col column instead of two copies. The examples below use a small extra table:

CREATE TEMP TABLE shipments (order_id bigint, carrier text, tracking text);
INSERT INTO shipments VALUES (1, 'DHL', 'D1'), (2, 'UPS', 'U2'), (6, 'DHL', 'D6');

SELECT *
FROM order_items
JOIN shipments USING (order_id)        -- same as ON order_items.order_id = shipments.order_id
ORDER BY order_id, product_id;

--  order_id | product_id | quantity | unit_price | carrier | tracking
-- ----------+------------+----------+------------+---------+----------
--         1 |          1 |        1 |    1299.00 | DHL     | D1
--  ...                                                 (order_id appears only once)

The merged column can be referenced without a table prefix. With a FULL JOIN, it is the value from whichever side is not NULL (coalesce(a.col, b.col)), which is convenient for reconciliation queries. USING works with several columns too: USING (tenant_id, order_id).

USING requires the columns to have the same name in both tables. It does not help with orders.customer_id = customers.id, which is why some teams name primary keys customer_id instead of id. Both conventions are common (2.4).

NATURAL JOIN. Joins on all columns that share a name, without listing them. It looks convenient and is almost always a mistake:

SELECT * FROM products NATURAL JOIN categories;
-- (0 rows)
-- Both tables have "id" and "name", so the condition is
-- products.id = categories.id AND products.name = categories.name

The real problem is that the join condition is decided by the schema at execution time. Adding an innocent column like created_at or updated_at to both tables silently changes the meaning of every NATURAL JOIN between them. And if the tables share no column, NATURAL JOIN becomes a cross join.

Form	Join columns	Duplicate column in output	Robust to schema changes
JOIN ... ON a.x = b.y	Explicit, any names	Both copies	Yes
JOIN ... USING (x)	Explicit, same name	Merged into one	Yes
NATURAL JOIN	Every same-named column	Merged	No: new columns change the join

Key takeaways

• USING (col) joins on same-named columns and returns a single merged column. It is a safe shorthand.
• In a FULL JOIN ... USING, the merged column takes the non-NULL value from either side.
• NATURAL JOIN joins on every shared column name, including ones added later. Avoid it in real code.

🧪 Practice

1. (Beginner) Rewrite SELECT * FROM order_items oi JOIN shipments s ON s.order_id = oi.order_id with USING, and compare the columns of both results.
2. (Intermediate) Using FULL JOIN ... USING (order_id) between orders (renaming id to order_id in a subquery) and shipments, find orders without shipments and shipments without orders.
3. (Interview) "Why do many style guides ban NATURAL JOIN?" Hint: Think about what happens to existing queries when a migration adds a column called name or updated_at to two tables.

LATERAL Joins

Some questions need a subquery that runs for each row of another table: "each customer's most recent order", "the top 2 products per category", "for every product, call a function that expands its tags". A normal subquery in FROM cannot see the other tables in the same FROM clause. LATERAL removes that restriction: the subquery may reference columns of the tables to its left, and is evaluated once per left row.

The idea. Think of LATERAL as a for each loop in the FROM clause:

for each row c in customers:
    run the subquery using c.id     -> 0, 1, or many rows
    join c with each row it returned

Top-N per group. This is the classic use. DISTINCT ON (4.1) gives one row per group. LATERAL with LIMIT gives any number:

-- The two most expensive products in each category
SELECT cat.name AS category, top.name AS product, top.price
FROM categories AS cat
LEFT JOIN LATERAL (
    SELECT p.name, p.price
    FROM products AS p
    WHERE p.category_id = cat.id      -- refers to the outer row: only legal because of LATERAL
    ORDER BY p.price DESC
    LIMIT 2
) AS top ON true                      -- LEFT JOIN ... ON true keeps categories with no rows
ORDER BY cat.id, top.price DESC;

--   category   |      product       |  price
-- -------------+--------------------+---------
--  Electronics |                    |
--  Books       | Postgres Internals |   54.00
--  Books       | SQL in Practice    |   39.00
--  Laptops     | UltraBook 14       | 1299.00
--  Laptops     | WorkBook 15        |  999.00
--  Accessories | Wireless Mouse     |   29.90
--  Accessories | USB-C Cable        |   12.50

CROSS JOIN LATERAL versus LEFT JOIN LATERAL

Form	When the subquery returns no rows
CROSS JOIN LATERAL (...) AS x (or , LATERAL (...) AS x)	The outer row is dropped, like an inner join
LEFT JOIN LATERAL (...) AS x ON true	The outer row is kept with NULLs

-- Each customer's latest order; Farid (no orders) is kept because of LEFT JOIN
SELECT c.name, recent.id AS order_id, recent.placed_at
FROM customers AS c
LEFT JOIN LATERAL (
    SELECT o.id, o.placed_at
    FROM orders AS o
    WHERE o.customer_id = c.id
    ORDER BY o.placed_at DESC
    LIMIT 1
) AS recent ON true;

With an index on orders (customer_id, placed_at DESC), each lateral subquery is a short index scan that stops after one row. On large tables this is often much faster than computing a window function over all orders and filtering afterwards.

Other uses

-- Compute an expression once and reuse it by name (instead of repeating it)
SELECT p.name, calc.gross, calc.gross - p.price AS vat
FROM products p
CROSS JOIN LATERAL (SELECT round(p.price * 1.21, 2) AS gross) AS calc
WHERE calc.gross > 100;

-- Set-returning functions are implicitly lateral: unnest() can use p's columns directly
SELECT p.name, tag
FROM products p, unnest(string_to_array(p.sku, '-')) AS tag;

Key takeaways

• A LATERAL subquery can reference columns of the tables before it in FROM, and runs once per outer row.
• It solves top-N-per-group with ORDER BY ... LIMIT n inside the subquery.
• Use LEFT JOIN LATERAL (...) ON true to keep outer rows for which the subquery returns nothing.
• With an index matching the inner WHERE and ORDER BY, lateral top-N queries scale very well.

🧪 Practice

1. (Beginner) For each customer, return their two most recent orders (id and date) with CROSS JOIN LATERAL. Which customers are missing, and how would you include them?
2. (Intermediate) For each product, return its most recent sale (order id and date) and the total quantity ever sold, using one or two lateral subqueries. Products never sold must appear with NULLs.
3. (Interview) "How would you fetch the 3 latest comments for each of 50 posts on a page?" Hint: Compare LATERAL with LIMIT 3, a row_number() window function, and 50 separate queries, and think about the index each approach needs.

Join Order and Readability

A query that joins five or six tables can be correct and still be impossible to review. Two separate concerns hide behind "join order": the order in which you write the joins, which affects readability and, for outer joins, the result; and the order in which PostgreSQL executes them, which affects speed and is mostly decided by the planner.

Execution order is the planner's job. For inner joins, the result does not depend on order: A JOIN B JOIN C returns the same rows as C JOIN A JOIN B. The planner estimates the cost of different orders and join methods and picks the cheapest (7.2). It searches exhaustively up to a limit:

Setting	Default	Meaning
join_collapse_limit	8	Up to this many explicit JOIN items are reordered freely. Beyond it, the written order is partly kept
from_collapse_limit	8	The same for subqueries pulled up into the outer FROM list
geqo_threshold	12	From this many tables, a genetic (heuristic) search replaces exhaustive search

So for typical queries, rewriting the join order "for performance" does nothing. Write them for readability.

Outer joins are not freely reorderable. A LEFT JOIN B is not the same as B LEFT JOIN A, and an outer join followed by an inner join behaves differently from the reverse. The written order and nesting are part of the meaning:

-- Inner join to order_items after the LEFT JOIN removes customers without orders (Farid)
SELECT c.name, o.id, oi.product_id
FROM customers c
LEFT JOIN orders o  ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id;

-- Fix 1: keep the chain outer
SELECT c.name, o.id, oi.product_id
FROM customers c
LEFT JOIN orders o       ON o.customer_id = c.id
LEFT JOIN order_items oi ON oi.order_id = o.id;

-- Fix 2: nest an inner join inside the outer one with parentheses
-- ("customers, plus their orders-with-items if any")
SELECT c.name, o.id, oi.product_id
FROM customers c
LEFT JOIN (orders o JOIN order_items oi ON oi.order_id = o.id)
       ON o.customer_id = c.id;

Row multiplication (fan-out). Joining along a one-to-many relationship repeats the "one" side once per "many" row. Aggregates over the repeated side then count it several times:

-- Ana has 2 orders with 4 items in total
SELECT c.name, count(o.id) AS orders, sum(oi.quantity) AS units
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE c.id = 1
GROUP BY c.name;
-- Ana Silva | 4 | 5      <- "4 orders" is wrong: each order was repeated once per item

-- count(DISTINCT o.id) fixes this count, but not sums over the repeated side.
-- The robust fix is to aggregate each child table before joining (4.4, 4.5).

Joining a parent to two independent child tables (for example orders and support tickets of a customer) multiplies them with each other: 3 orders × 4 tickets = 12 rows per customer. Aggregate each child separately, then join the results.

Readability conventions

• Start FROM with the table the query is "about" (the grain of the result), then join outward along foreign keys.
• One join per line, with the ON condition on the same or the next line.
• Write the ON condition with the newly joined table's column first, or consistently the other way round. Pick one style.
• Use short, meaningful aliases (o, oi, c), not a, b, c in alphabetical order.
• Qualify every column. Use LEFT JOIN instead of RIGHT JOIN.
• Keep relationship conditions in ON and filters in WHERE (except filters on the optional side of an outer join).

SELECT c.country,
       p.name                       AS product,
       sum(oi.quantity)             AS units
FROM order_items AS oi                                 -- grain: one row per order line
JOIN orders      AS o   ON o.id = oi.order_id
JOIN customers   AS c   ON c.id = o.customer_id
JOIN products    AS p   ON p.id = oi.product_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY c.country, p.name
ORDER BY c.country, units DESC;

Key takeaways

• Inner join order does not change results, and the planner reorders up to join_collapse_limit (8) items, so write joins for readability.
• Outer join order and nesting change results. Keep chains outer or nest inner joins inside parentheses.
• Joining along one-to-many relationships multiplies rows. Aggregate child tables before joining when you sum or count them.
• Start from the table that defines the result's grain, join outward, and alias and qualify everything.

🧪 Practice

1. (Beginner) Reformat this query according to the conventions above: select * from products p, order_items x, orders y where x.product_id=p.id and y.id=x.order_id and y.status='paid'.
2. (Intermediate) Write a query that returns, per customer, the number of orders and the total number of units bought. Get it wrong first with a straightforward three-table join, then fix it by aggregating order_items per order before joining.
3. (Interview) "Does the order of tables in a JOIN affect performance in PostgreSQL?" Hint: Distinguish inner and outer joins, mention join_collapse_limit and GEQO, and explain when the written order is respected.

<a id="44-aggregation-and-grouping"></a>

4.4 Aggregation and Grouping

Most business questions are about summaries, not individual rows: revenue per month, orders per status, the median order value. This subchapter covers aggregate functions, grouping rows with GROUP BY, filtering groups with HAVING and aggregates with FILTER, producing subtotals with GROUPING SETS, statistical aggregates such as the median, and collecting values into strings and arrays.

Aggregate Functions

An aggregate function takes many input rows and returns one value: the count of rows, the sum of a column, its average. Doing this in the database rather than in the application means only the result travels over the network, not millions of rows, and the planner can use parallel workers and indexes to compute it.

The core aggregates

Function	Returns	NULL inputs	On zero rows
count(*)	Number of rows	Counted	0
count(expr)	Number of non-NULL values	Ignored	0
count(DISTINCT expr)	Number of distinct non-NULL values	Ignored	0
sum(expr)	Total	Ignored	NULL
avg(expr)	Arithmetic mean	Ignored	NULL
min(expr), max(expr)	Smallest / largest (numbers, text, dates, ...)	Ignored	NULL
bool_and(expr) / every(expr), bool_or(expr)	All true? / any true?	Ignored	NULL

Without GROUP BY, an aggregate query collapses the whole table (after WHERE) into a single row:

SELECT count(*)                    AS products,
       count(category_id)          AS with_category,    -- the gift card's NULL is not counted
       count(DISTINCT category_id) AS categories,
       sum(stock)                  AS units,
       round(avg(price), 2)        AS avg_price,
       min(price), max(price)
FROM products;

--  products | with_category | categories | units | avg_price |  min  |   max
-- ----------+---------------+------------+-------+-----------+-------+---------
--         7 |             6 |          3 |  1297 |    351.20 | 12.50 | 1299.00

Edge cases that matter

-- Zero input rows still produce one output row: count is 0, the others are NULL
SELECT count(*), sum(stock), max(price) FROM products WHERE false;
--  count | sum | max
-- -------+-----+-----
--      0 |     |

-- avg ignores NULLs: (10 + 20) / 2, not (10 + 20 + 0) / 3
SELECT avg(x), avg(coalesce(x, 0)) FROM (VALUES (10), (20), (NULL)) AS t(x);
--  15.0000000000000000 | 10.0000000000000000

Decide which one your question means. "Average rating of rated products" ignores unrated ones, while "average revenue per customer" usually needs customers with no revenue counted as 0.

Result types. sum() of integer returns bigint, and sum() of bigint returns numeric, so integer sums do not overflow. avg() of integers returns numeric. count() always returns bigint.

Aggregates and the select list. In a query with aggregates and no GROUP BY, every select-list item must be an aggregate (or a constant). SELECT name, max(price) FROM products is an error, because there is no single name that belongs to "the maximum". To get the row with the maximum price, use ORDER BY price DESC LIMIT 1 or DISTINCT ON.

Key takeaways

• count(*) counts rows. count(expr) counts non-NULL values. All other aggregates ignore NULL inputs.
• On zero rows, count returns 0 and the others return NULL. Wrap them with coalesce when you need 0.
• avg divides by the number of non-NULL values. Decide whether missing values should count as zero.
• An aggregate returns a value, not a row. To find the row holding the maximum, sort and limit instead.

🧪 Practice

1. (Beginner) Return the number of orders, the number of distinct customers who ordered, and the date of the first and last order.
2. (Intermediate) Compute the average line value (quantity * unit_price) over all order items, and the average order total (the average of per-order sums). Explain why the two numbers differ.
3. (Interview) "What is the difference between count(*), count(1), and count(column)?" Hint: Two of them are identical in result and performance in PostgreSQL. The third depends on NULLs.

GROUP BY Semantics

One total for the whole table is rarely enough. You want totals per something: per status, per country, per month. GROUP BY splits the rows into groups that share the same values in the grouping expressions, and computes the aggregates separately for each group. The result has one row per group.

How it works.

 orders (after WHERE)          GROUP BY status              output
 id | status                   ┌ shipped: 1, 2, 6  ┐        status    | count
 ---+-----------               │ pending: 3        │  --->  shipped   |   3
  1 | shipped                  │ cancelled: 4      │        cancelled |   1
  2 | shipped                  └ paid: 5           ┘        paid      |   1
  3 | pending                                               pending   |   1
  ...

SELECT status, count(*)
FROM orders
GROUP BY status
ORDER BY count(*) DESC, status;

--   status   | count
-- -----------+-------
--  shipped   |     3
--  cancelled |     1
--  paid      |     1
--  pending   |     1

The single-value rule. After grouping, each output row represents a whole group. So every expression in the select list must have one value per group. It must be either:

1. A grouping expression (it is the same for all rows in the group by definition), or
2. An aggregate (it combines all rows of the group into one value), or
3. A column that is functionally dependent on a grouped primary key.

SELECT name, count(*) FROM products GROUP BY category_id;
-- ERROR:  column "products.name" must appear in the GROUP BY clause
--         or be used in an aggregate function
-- Category 2 has two products: which name should the row show?

-- Rule 3: grouping by the primary key p.id makes every other column of p single-valued
SELECT p.id, p.name, sum(oi.quantity) AS units
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id;                      -- p.name is allowed without being listed

MySQL historically allowed non-grouped columns and returned an arbitrary value. PostgreSQL refuses, which prevents a whole class of silently wrong reports.

What you can group by

Grouping by	Example	Notes
Columns	GROUP BY country	Several columns form combined groups: GROUP BY country, status
Expressions	GROUP BY date_trunc('month', placed_at)	Very common for time buckets
Output column alias	GROUP BY month	PostgreSQL extension. An input column with the same name takes priority
Position	GROUP BY 1	Convenient in ad hoc queries, fragile in code

NULL values form a single group:

SELECT category_id, count(*), sum(stock)
FROM products
GROUP BY category_id
ORDER BY category_id NULLS LAST;
--  category_id | count | sum
-- -------------+-------+------
--            2 |     2 |   42
--            3 |     2 |    5
--            4 |     2 |  250
--              |     1 | 1000      <- all NULL category_ids grouped together

Time buckets

SELECT date_trunc('month', placed_at) AS month, count(*) AS orders
FROM orders
GROUP BY month                       -- the alias, resolved to the expression
ORDER BY month;
--          month          | orders
-- ------------------------+--------
--  2025-01-01 00:00:00+00 |      1
--  2025-02-01 00:00:00+00 |      2
--  2025-03-01 00:00:00+00 |      3

Months with no orders are absent: grouping only creates groups for values that exist. To show empty buckets, cross or outer join a generate_series() calendar (4.3).

Groups are unordered. GROUP BY does not sort the output. Older PostgreSQL versions often returned groups in sorted order as a side effect of a sort-based plan, but a hash aggregate returns them in any order. Always add ORDER BY.

Key takeaways

• GROUP BY produces one output row per distinct combination of grouping values, and aggregates are computed per group.
• Every select-list item must be a grouping expression, an aggregate, or depend on a grouped primary key.
• NULLs form one group. Values that do not occur produce no group.
• Grouping does not imply ordering. Add ORDER BY.

🧪 Practice

1. (Beginner) Count customers per country, and products per category, sorted by count descending.
2. (Intermediate) Return revenue (sum(quantity * unit_price)) per customer name and month, excluding cancelled orders. Why can you not group by c.name alone safely, and what should you group by instead?
3. (Interview) "Why does PostgreSQL reject SELECT department, name, max(salary) FROM employees GROUP BY department, and how would you return the highest-paid employee per department?" Hint: Explain which name the row would have to show, then use DISTINCT ON or a window function.

HAVING Clause

After grouping, you often want only some groups: customers with more than one order, products that sold fewer than 10 units, countries whose revenue exceeds a threshold. WHERE cannot do this, because it runs before groups exist (logical processing order). HAVING is the filter that runs after grouping, on the groups themselves.

WHERE versus HAVING

 rows --WHERE (filters rows)--> GROUP BY --HAVING (filters groups)--> SELECT

	WHERE	HAVING
Filters	Individual rows	Whole groups
Runs	Before grouping	After grouping
Can use aggregates	No	Yes
Can use non-grouped columns	Yes	Only inside aggregates

-- Customers with more than one order
SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
HAVING count(*) > 1;
--  customer_id | orders
-- -------------+--------
--            1 |      2

-- Both filters together: WHERE removes cancelled orders first, HAVING keeps big spenders
SELECT o.customer_id, sum(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'                         -- row filter
GROUP BY o.customer_id
HAVING sum(oi.quantity * oi.unit_price) > 1000        -- group filter
ORDER BY revenue DESC;
--  customer_id | revenue
-- -------------+---------
--            1 | 1417.00
--            4 | 1358.80

Aliases are not visible in HAVING. In PostgreSQL, HAVING cannot reference a select-list alias, because the select list is evaluated afterwards. Repeat the expression (it is computed only once anyway):

SELECT customer_id, count(*) AS n FROM orders GROUP BY customer_id HAVING n > 1;
-- ERROR:  column "n" does not exist

Put row conditions in WHERE. A condition on a grouping column works in either clause, but WHERE removes rows before the expensive grouping step, and can use indexes:

-- Works, but groups every country before throwing most of them away
SELECT country, count(*) FROM customers GROUP BY country HAVING country = 'FR';

-- Better: filter first
SELECT country, count(*) FROM customers WHERE country = 'FR' GROUP BY country;

PostgreSQL often moves such conditions down by itself, but writing them in WHERE states the intent and does not rely on the optimizer.

HAVING without GROUP BY. The whole input is one group, so the query returns either one row or none: SELECT count(*) FROM orders HAVING count(*) > 100;.

Key takeaways

• WHERE filters rows before grouping. HAVING filters groups after aggregation.
• Use aggregates in HAVING. Conditions on plain columns belong in WHERE.
• PostgreSQL does not allow select-list aliases in HAVING. Repeat the expression.

🧪 Practice

1. (Beginner) List the products (by id) that appear in more than one order.
2. (Intermediate) List the countries whose customers placed at least one non-cancelled order with a total above 100, together with the number of such orders. Decide carefully which conditions go in WHERE, which in HAVING, and whether you need to aggregate in two steps.
3. (Interview) "Can you use HAVING without GROUP BY? Can you use WHERE with an aggregate?" Hint: Think about what the groups are in each case and when each clause is evaluated.

FILTER Clause on Aggregates

Dashboards often need several counts side by side: total orders, shipped orders, and pending orders in one row. Running a separate query for each is wasteful. The FILTER clause lets each aggregate in the same query consider only the rows that match its own condition, in a single pass over the data.

Syntax. aggregate(...) FILTER (WHERE condition). Rows for which the condition is not true are skipped by that aggregate only:

SELECT count(*)                                       AS total,
       count(*) FILTER (WHERE status = 'shipped')     AS shipped,
       count(*) FILTER (WHERE status = 'pending')     AS pending,
       count(*) FILTER (WHERE status = 'cancelled')   AS cancelled
FROM orders;

--  total | shipped | pending | cancelled
-- -------+---------+---------+-----------
--      6 |       3 |       1 |         1

Pivoting rows into columns. Combined with GROUP BY, FILTER turns values of one column into separate output columns, sometimes called a pivot or crosstab:

-- Revenue per country, split by order status
SELECT c.country,
       sum(oi.quantity * oi.unit_price) FILTER (WHERE o.status = 'shipped') AS shipped,
       sum(oi.quantity * oi.unit_price) FILTER (WHERE o.status = 'paid')    AS paid,
       sum(oi.quantity * oi.unit_price)                                     AS all_statuses
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.country
ORDER BY c.country;

FILTER versus CASE. Before FILTER (PostgreSQL 9.4, SQL:2003 standard), the same was written with CASE inside the aggregate. Both work, but FILTER is clearer and avoids the subtle mistakes:

Goal	With FILTER	With CASE
Conditional count	count(*) FILTER (WHERE s = 'paid')	count(CASE WHEN s = 'paid' THEN 1 END) (not ELSE 0, which counts every row)
Conditional sum	sum(x) FILTER (WHERE s = 'paid')	sum(CASE WHEN s = 'paid' THEN x END)
Conditional array	array_agg(x) FILTER (WHERE x IS NOT NULL)	Not directly possible: CASE would add NULL elements

When no row passes the filter, count returns 0 and other aggregates return NULL, as with an empty input.

FILTER versus WHERE. WHERE removes rows for all aggregates and can use indexes. FILTER keeps the rows but tells one aggregate to ignore them. If every aggregate in the query has the same filter, move it to WHERE.

FILTER works with any aggregate, including string_agg, array_agg, and ordered-set aggregates, and also with aggregate window functions (5.2).

Key takeaways

• agg(...) FILTER (WHERE cond) makes one aggregate consider only matching rows, so several conditional aggregates take a single scan.
• It is the cleanest way to pivot status-like values into columns.
• It is clearer than CASE inside aggregates and avoids the ELSE 0 counting bug.
• Use WHERE when the condition applies to every aggregate in the query.

🧪 Practice

1. (Beginner) In one row, return the number of products, the number that are discontinued, and the number with stock below 20.
2. (Intermediate) For each month of 2025, return the number of orders and the number of distinct customers, split into two pairs of columns: shipped and not shipped.
3. (Interview) "How would you build a report with one column per order status, and what happens when a new status is added?" Hint: FILTER needs the statuses written into the query. Discuss the crosstab() function from tablefunc and pivoting in the application as alternatives.

GROUPING SETS, ROLLUP, and CUBE

Reports usually need subtotals as well as details: revenue per country and month, plus a subtotal per country, plus a grand total. With plain GROUP BY, that is three queries glued together with UNION ALL, each scanning the data again. Grouping sets compute several groupings in one query and one pass.

The examples use this helper view of non-cancelled sales:

CREATE TEMP VIEW sales AS
SELECT c.country,
       date_trunc('month', o.placed_at)::date AS month,
       cat.name                               AS category,
       oi.quantity * oi.unit_price            AS amount
FROM order_items oi
JOIN orders o          ON o.id = oi.order_id
JOIN customers c       ON c.id = o.customer_id
JOIN products p        ON p.id = oi.product_id
LEFT JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled';

GROUPING SETS. Lists exactly the groupings you want. () is the empty grouping, the grand total. Columns not part of a row's grouping set are NULL in that row:

SELECT country, category, sum(amount)
FROM sales
GROUP BY GROUPING SETS ((country), (category), ());
--  country |  category   |   sum
-- ---------+-------------+---------
--  DE      |             |   90.50     <- grouping (country)
--  ES      |             | 1358.80
--  NG      |             |   29.90
--  PT      |             | 1417.00
--          | Accessories |  127.20     <- grouping (category)
--          | Books       |  171.00
--          | Laptops     | 2598.00
--          |             | 2896.20     <- grouping (): grand total

ROLLUP. For hierarchies (year > month > day, country > city). ROLLUP (a, b) is shorthand for GROUPING SETS ((a, b), (a), ()), meaning details, subtotals for each level, and a grand total:

SELECT country, month, sum(amount)
FROM sales
GROUP BY ROLLUP (country, month)
ORDER BY country, month;
--  country |   month    |   sum
-- ---------+------------+---------
--  DE      | 2025-03-01 |   90.50
--  DE      |            |   90.50     <- subtotal for DE
--  ...
--  PT      | 2025-01-01 | 1324.00
--  PT      | 2025-02-01 |   93.00
--  PT      |            | 1417.00     <- subtotal for PT
--          |            | 2896.20     <- grand total

CUBE. For independent dimensions. CUBE (a, b) produces every combination: (a, b), (a), (b), (). With n columns it produces 2^n groupings, so keep n small.

Clause	Groupings produced for (a, b)	Use for
GROUPING SETS ((a), (b))	Exactly the listed ones	Custom combinations
ROLLUP (a, b)	(a, b), (a), ()	Hierarchical subtotals
CUBE (a, b)	(a, b), (a), (b), ()	All cross-tab subtotals

Telling subtotal NULLs from data NULLs. A NULL in a subtotal row means "all values". But the data itself can contain NULLs (the gift card has no category). The GROUPING() function tells them apart: it returns 1 for a column that is aggregated away in that row, and 0 for one that is part of the grouping. With several arguments, it returns a bitmask:

SELECT CASE WHEN GROUPING(country) = 1 THEN 'All countries' ELSE country END AS country,
       sum(amount)
FROM sales
GROUP BY ROLLUP (country)
ORDER BY GROUPING(country), country;          -- detail rows first, total last
--     country    |   sum
-- ---------------+---------
--  DE            |   90.50
--  ES            | 1358.80
--  NG            |   29.90
--  PT            | 1417.00
--  All countries | 2896.20

Grouping sets can be combined with regular grouping columns: GROUP BY year, ROLLUP (country, city) keeps the year in every grouping.

Key takeaways

• GROUPING SETS computes several GROUP BYs in one query. Columns not in a row's grouping set are NULL.
• ROLLUP (a, b) gives details, per-level subtotals, and a grand total for hierarchies.
• CUBE gives every combination of the dimensions (2^n groupings).
• Use GROUPING(col) to label subtotal rows and distinguish them from real NULL values.

🧪 Practice

1. (Beginner) Using the sales view, return revenue per category plus a grand total row labeled 'Total'.
2. (Intermediate) Use CUBE (country, category) and GROUPING(country, category) to label each row as 'detail', 'country subtotal', 'category subtotal', or 'grand total'. Why is the bitmask for a category subtotal 2 rather than 1?
3. (Interview) "How would you produce a sales report with subtotals by region and country and a grand total in a single query?" Hint: Pick between ROLLUP and CUBE, and explain how the application can recognize which rows are subtotals.

Ordered-Set and Hypothetical-Set Aggregates

Averages are easily distorted. If nine orders are worth 50 and one is worth 10,000, the average of 1,045 describes none of them. The median (the middle value) and other percentiles are more robust, and computing them requires looking at the values in sorted order. PostgreSQL provides these as ordered-set aggregates, which take an ordering in a WITHIN GROUP (ORDER BY ...) clause.

Percentiles and mode

Function	Returns
percentile_cont(f) WITHIN GROUP (ORDER BY x)	The continuous percentile: interpolates between the two nearest values
percentile_disc(f) WITHIN GROUP (ORDER BY x)	The discrete percentile: the first actual value whose position is at or after fraction f
mode() WITHIN GROUP (ORDER BY x)	The most frequent value (an arbitrary one among ties)

f is a fraction between 0 and 1: 0.5 is the median, 0.95 is the 95th percentile. An array of fractions returns an array of percentiles.

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median_cont,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY price) AS median_disc,
       round(avg(price), 2)                               AS mean
FROM products;
--  median_cont | median_disc |  mean
-- -------------+-------------+--------
--           39 |       39.00 | 351.20      <- two expensive laptops pull the mean up

-- Quartiles in one call
SELECT percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY price) FROM products;
-- {27.45,39,526.5}

-- Median salary per department: works with GROUP BY like any aggregate
SELECT department, percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department;
--  Engineering | 7200
--  Sales       | 5200

SELECT mode() WITHIN GROUP (ORDER BY status) FROM orders;    -- 'shipped'

	percentile_cont	percentile_disc
Input types	Numeric and interval types	Any sortable type (also text, timestamps)
Result	May be a value that does not exist in the data	Always an actual input value
Median of 1, 2, 3, 4	2.5	2

Percentiles are a standard way to describe latency ("p95 response time is 120 ms") and skewed distributions like order values or salaries. They require sorting all values of each group, so on very large groups they are more expensive than avg.

Hypothetical-set aggregates. These answer "where would a value rank if it were inserted into this group?", without inserting anything. Each takes the hypothetical value as its argument and the ordering in WITHIN GROUP:

Function	Answers
rank(v) WITHIN GROUP (ORDER BY x)	Rank of v, with gaps after ties
dense_rank(v) WITHIN GROUP (...)	Rank of v, without gaps
percent_rank(v) WITHIN GROUP (...)	Fraction of rows that rank strictly before v: (rank - 1) / count
cume_dist(v) WITHIN GROUP (...)	Fraction of rows (including v) at or before v

-- How would a product priced at 100 rank by price, most expensive first?
SELECT rank(100) WITHIN GROUP (ORDER BY price DESC) FROM products;   -- 3

-- Where would a 7,100 salary rank in each department?
SELECT department, rank(7100) WITHIN GROUP (ORDER BY salary DESC)
FROM employees
GROUP BY department;
--  Engineering | 3      (behind 9000 and 7200)
--  Sales       | 2      (behind 8000)

The functions with the same names without WITHIN GROUP are window functions that rank existing rows (5.2).

Key takeaways

• Ordered-set aggregates take their ordering in WITHIN GROUP (ORDER BY ...).
• percentile_cont(0.5) is the interpolated median, percentile_disc(0.5) returns an actual value. Pass an array for several percentiles at once.
• mode() returns the most frequent value.
• Hypothetical-set aggregates (rank(v), percent_rank(v), and others) tell where a value would fall in a group without inserting it.

🧪 Practice

1. (Beginner) Compute the median and the 90th percentile of order line values (quantity * unit_price).
2. (Intermediate) Compare avg() and percentile_cont(0.5) of order totals (per-order sums), and explain which better represents a "typical" order in this dataset.
3. (Interview) "Why do monitoring systems report p50, p95, and p99 latencies instead of the average?" Hint: Think about skewed distributions and what an outlier does to a mean. Then consider how expensive exact percentiles are over billions of rows and what approximate alternatives exist.

String and Array Aggregation

Some outputs need a group's values collected, not summarized: the list of products in an order as one comma-separated string, all tags of a post as an array, an order with its items as a JSON document for an API. Aggregates such as string_agg and array_agg combine a group's values into a single string, array, or JSON value, and an ORDER BY inside the call controls their order.

string_agg and array_agg

SELECT o.id,
       string_agg(p.name, ', ' ORDER BY p.name)   AS products,   -- ORDER BY inside the call
       array_agg(oi.quantity ORDER BY p.name)     AS quantities  -- same order: arrays line up
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
GROUP BY o.id
ORDER BY o.id;

--  id |              products               | quantities
-- ----+-------------------------------------+------------
--   1 | USB-C Cable, UltraBook 14           | {2,1}
--   2 | Postgres Internals, SQL in Practice | {1,1}
--   3 | Wireless Mouse                      | {1}
--   ...

Without ORDER BY inside the aggregate, the order of elements is unspecified. The ORDER BY goes inside the parentheses, after the last argument and without a comma.

Behavior with NULL and DISTINCT

Aggregate	NULL inputs	DISTINCT
string_agg(x, sep)	Skipped	string_agg(DISTINCT x, sep) (when ordered, it must order by x itself)
array_agg(x)	Kept as NULL elements	array_agg(DISTINCT x ORDER BY x)
json_agg(x) / jsonb_agg(x)	Kept as JSON null	Works if x's type has equality. json values do not, so build jsonb values instead

SELECT array_agg(email),
       array_agg(email) FILTER (WHERE email IS NOT NULL),
       string_agg(email, ',')
FROM customers WHERE country = 'FR';
--         array_agg         |      array_agg      |    string_agg
-- --------------------------+---------------------+-------------------
--  {NULL,farid@example.com} | {farid@example.com} | farid@example.com

SELECT array_agg(DISTINCT country ORDER BY country) FROM customers;   -- {DE,ES,FR,NG,PT}

JSON aggregation. Building nested JSON in the database avoids the "N+1 queries" problem, where the application runs one query per parent row to fetch its children (3.4):

SELECT c.name,
       json_agg(json_build_object('id', o.id, 'status', o.status) ORDER BY o.id) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 1
GROUP BY c.name;
-- Ana Silva | [{"id" : 1, "status" : "shipped"}, {"id" : 2, "status" : "shipped"}]

-- Key-value pairs into one object
SELECT jsonb_object_agg(status, n)
FROM (SELECT status, count(*) AS n FROM orders GROUP BY status) AS s;
-- {"paid": 1, "pending": 1, "shipped": 3, "cancelled": 1}

The reverse direction. unnest(array) and string_to_table(text, sep) (PostgreSQL 14+) turn arrays and delimited strings back into rows (3.3).

When not to aggregate into strings. A comma-separated list is for display. If the application will parse it back apart, return an array or JSON instead, which keep element boundaries and types intact even when values contain commas.

Key takeaways

• string_agg(x, sep ORDER BY ...) concatenates values. array_agg collects them into an array. Specify the order inside the call.
• string_agg skips NULLs, while array_agg keeps them. Use FILTER (WHERE x IS NOT NULL) to drop them.
• json_agg, jsonb_agg, and jsonb_object_agg build nested JSON in one query instead of N+1 queries.
• Use strings for display, and arrays or JSON for data that a program will process.

🧪 Practice

1. (Beginner) For each category, return a comma-separated, alphabetically ordered list of its product names.
2. (Intermediate) Return one JSON document per customer containing their name and an array of orders, each with its id, status, and an array of {product, quantity} items. Customers without orders should have an empty array, not null.
3. (Interview) "An API endpoint loads 100 customers, then runs one query per customer to load their orders. How would you fix it on the SQL side?" Hint: Compare a join with application-side grouping, json_agg with nested subqueries, and a second query using WHERE customer_id = ANY($1).

<a id="45-set-operations-and-subqueries"></a>

4.5 Set Operations and Subqueries

Queries can be combined in two ways: stacked vertically with set operations (UNION, INTERSECT, EXCEPT), or nested inside one another as subqueries. This subchapter covers both, including the semantics of IN, ANY, ALL, and EXISTS, correlated subqueries, derived tables, and when to rewrite a subquery as a join.

UNION, INTERSECT, and EXCEPT

Sometimes the rows you need come from two different queries: customers and employees in one contact list, products that are in stock and were ordered last month, customers who registered but never ordered. Set operations combine the results of two queries row by row, following the set operations from mathematics.

The three operations

     A = {1, 2, 3}         B = {2, 3, 4}

  A UNION B      = {1, 2, 3, 4}     rows in A or B
  A INTERSECT B  = {2, 3}           rows in both A and B
  A EXCEPT B     = {1}              rows in A that are not in B

-- Customers who ever placed a shipped order AND were not referred by anyone
SELECT customer_id FROM orders WHERE status = 'shipped'
INTERSECT
SELECT id FROM customers WHERE referred_by IS NULL;
-- 1

-- Customers who never ordered
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;
-- 6

Duplicates: the ALL variants. By default, set operations remove duplicate rows from the result, like DISTINCT. Adding ALL keeps them:

Operation	Without ALL (distinct)	With ALL (keeps counts)
UNION	Each distinct row once	All rows from both sides (simply appended)
INTERSECT	Each row present in both, once	A row appearing m and n times appears min(m, n) times
EXCEPT	Rows of A not in B, once	A row appearing m times in A and n times in B appears max(m − n, 0) times

SELECT country FROM customers WHERE referred_by IS NOT NULL      -- NG, FR, DE
UNION
SELECT country FROM customers WHERE created_at >= '2024-09-01'   -- DE, FR
ORDER BY 1;
-- DE, FR, NG          (duplicates removed)

-- Same with UNION ALL: NG, FR, DE, DE, FR (5 rows)

UNION must sort or hash the combined result to find duplicates. When the inputs cannot overlap or duplicates do not matter, UNION ALL is cheaper and should be your default.

Rules for combining queries

Rule	Detail
Same number of columns	Both queries must return the same number of columns
Compatible types, by position	Columns are matched by position, not by name. SELECT 1, 'a' UNION SELECT 'x', 2 fails
Column names come from the first query	Alias the first query's columns
ORDER BY / LIMIT apply to the whole result	Put them at the end. To sort or limit one branch, wrap it in parentheses
INTERSECT binds tighter than UNION and EXCEPT	A UNION B INTERSECT C is A UNION (B INTERSECT C). Use parentheses
NULLs count as equal	Unlike =, two NULLs are duplicates for set operations

-- A combined contact list: label each branch so the rows stay distinguishable
SELECT 'customer' AS kind, name FROM customers WHERE country = 'FR'
UNION ALL
SELECT 'employee', name FROM employees WHERE department = 'Sales'
ORDER BY kind, name;

-- Limiting each branch requires parentheses
(SELECT name, price FROM products ORDER BY price DESC LIMIT 1)   -- most expensive
UNION ALL
(SELECT name, price FROM products ORDER BY price ASC  LIMIT 1);  -- cheapest

Comparing tables. EXCEPT in both directions is a quick way to check whether two tables (or a table and a backup) hold the same rows: (A EXCEPT B) UNION ALL (B EXCEPT A) returns every difference.

Key takeaways

• UNION, INTERSECT, and EXCEPT combine query results row by row. Columns are matched by position and must have compatible types.
• Without ALL, duplicates are removed (with NULLs treated as equal). UNION ALL is cheaper and is the right default when duplicates are impossible or wanted.
• ORDER BY and LIMIT apply to the whole combined result unless a branch is parenthesized.
• INTERSECT has higher precedence than UNION and EXCEPT.

🧪 Practice

1. (Beginner) Return a single list of all names in the database (customers and employees) with a column saying where each one comes from, sorted by name.
2. (Intermediate) Find the products that were ordered in both January and March 2025 with INTERSECT, and the products ordered in February but not in March with EXCEPT. Then write the second query with NOT EXISTS.
3. (Interview) "What is the difference between UNION and UNION ALL, and which one would you use to merge daily partitions of a log table?" Hint: Think about the cost of duplicate elimination and whether duplicates are possible or meaningful.

Scalar and Row Subqueries

Some filters depend on a value that must itself be computed: "products more expensive than the average", "orders placed on the same day as the most recent one". A subquery is a complete SELECT in parentheses, used inside another statement. When it returns exactly one value, it can be used anywhere a single value can.

Scalar subqueries. Return one row with one column. The value can be compared, computed with, or selected:

-- Products priced above the average price
SELECT name, price
FROM products
WHERE price > (SELECT avg(price) FROM products);       -- computed once: 351.20
-- UltraBook 14 1299.00, WorkBook 15 999.00

-- In the select list: each product's distance from the average
SELECT name, price,
       round(price - (SELECT avg(price) FROM products), 2) AS diff_from_avg
FROM products
ORDER BY price DESC
LIMIT 3;
--         name        |  price  | diff_from_avg
-- --------------------+---------+---------------
--  UltraBook 14       | 1299.00 |        947.80
--  WorkBook 15        |  999.00 |        647.80
--  Postgres Internals |   54.00 |       -297.20

The one-row contract. A scalar subquery must return at most one row:

Subquery returns	Result
Exactly one row	Its value
Zero rows	NULL (not an error)
More than one row	ERROR: more than one row returned by a subquery used as an expression

SELECT name FROM products WHERE price = (SELECT price FROM products WHERE category_id = 2);
-- ERROR:  more than one row returned by a subquery used as an expression

The zero-row case is dangerous because it fails silently: price = NULL matches nothing. Make subqueries return one row by construction (an aggregate, a primary key lookup, or LIMIT 1 with an ORDER BY), rather than relying on the current data.

Correlated scalar subqueries in the select list. A subquery can reference columns of the outer row, so it is computed per row. This is a readable way to attach a few per-row figures:

SELECT c.name,
       (SELECT count(*)       FROM orders o WHERE o.customer_id = c.id) AS orders,
       (SELECT max(placed_at) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;
-- Farid Haddad | 0 | NULL     <- count over zero rows is 0, max is NULL

Each subquery runs once per outer row. With an index on orders (customer_id), that is fine for a page of results. For whole-table reports, a join to a pre-aggregated derived table (Subqueries in FROM Clauses) is usually faster. More on correlation in EXISTS and Correlated Subqueries.

Row subqueries. Return one row with several columns, compared with a row constructor:

-- Customer 1's most recent order, matching on two columns at once
SELECT id, placed_at
FROM orders
WHERE (customer_id, placed_at) = (SELECT customer_id, max(placed_at)
                                  FROM orders
                                  WHERE customer_id = 1
                                  GROUP BY customer_id);
-- 2 | 2025-02-11 14:30:00+00

Key takeaways

• A scalar subquery returns one value and can be used wherever an expression can.
• More than one row is an error. Zero rows silently give NULL.
• Uncorrelated subqueries are computed once. Correlated ones are evaluated per outer row.
• Row subqueries compare several columns at once with (a, b) = (SELECT x, y ...).

🧪 Practice

1. (Beginner) List all orders placed after the first order of customer 4 (Diego Ruiz), using a scalar subquery.
2. (Intermediate) For each product, show its price and the percentage of the most expensive product's price it represents, rounded to one decimal.
3. (Interview) "A query that has worked for months suddenly fails with 'more than one row returned by a subquery used as an expression'. What happened, and how do you make it robust?" Hint: The data changed, not the query. Think about uniqueness constraints, aggregates, or LIMIT 1 with a deterministic ORDER BY, and decide which reflects the real business rule.

IN, ANY, and ALL

Often a filter needs a list of values that comes from data: "customers who have shipped orders", "products cheaper than every book". A subquery that returns one column and many rows can be used with IN, ANY, or ALL, which compare a value with every row of the list.

IN. x IN (subquery) is true if x equals at least one value returned by the subquery:

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'shipped');
-- Ana Silva, Emma Weber

IN handles duplicates in the subquery naturally: Ana has two shipped orders but appears once, because each customer row is tested once. A join would return her twice (Rewriting Subqueries as Joins).

ANY and ALL. Generalize IN to any comparison operator:

Expression	True when	Equivalent
x = ANY (subquery)	x equals at least one value	x IN (subquery)
x > ANY (subquery)	x is greater than at least one value	x > min(...)
x > ALL (subquery)	x is greater than every value	x > max(...)
x <> ALL (subquery)	x differs from every value	x NOT IN (subquery)

SOME is a synonym for ANY.

-- More expensive than every book (books cost 39 and 54)
SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE category_id = 2);
-- UltraBook 14 1299.00, WorkBook 15 999.00

-- More expensive than at least one book
SELECT name, price FROM products
WHERE price > ANY (SELECT price FROM products WHERE category_id = 2);
-- Postgres Internals 54.00, WorkBook 15 999.00, UltraBook 14 1299.00

Edge cases. With an empty subquery, ALL is true and ANY is false. So price > ALL (SELECT price ... WHERE category_id = 99) returns every product, because "greater than all of nothing" is vacuously true. The max() rewrite behaves differently (price > NULL is NULL), so the two are not exactly equivalent.

NOT IN and NULL. This is the most common subquery bug. x NOT IN (list) means x <> v1 AND x <> v2 AND .... If any v is NULL, that term is NULL, and the whole condition can never be true:

-- Employees who manage nobody. manager_id is NULL for Grace and Jonas...
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);
-- (0 rows)  <- wrong

-- Safe: NOT EXISTS ignores NULLs correctly
SELECT name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.id);
-- Hugo, Iris, Kim, Lena

Adding WHERE manager_id IS NOT NULL inside the subquery also fixes it, but NOT EXISTS is safe by construction and is planned as an efficient anti-join. NOT IN (subquery) usually cannot be, because of the NULL semantics it must preserve.

Arrays instead of subqueries. ANY and ALL also accept an array. This is the standard way to pass a list of values from an application as one parameter, instead of building IN ($1, $2, $3, ...) strings:

SELECT name FROM products WHERE id = ANY (ARRAY[1, 3, 5]);
-- From application code: WHERE id = ANY ($1), binding $1 as an array

Multi-column IN. A row constructor tests several columns at once:

-- The most expensive product in each category
SELECT name FROM products
WHERE (category_id, price) IN (SELECT category_id, max(price) FROM products GROUP BY category_id);
-- UltraBook 14, Wireless Mouse, Postgres Internals   (Gift Card: NULL category never matches)

Key takeaways

• x IN (subquery) tests membership and returns each outer row at most once.
• op ANY is true if the comparison holds for some row, op ALL if it holds for every row. On an empty set, ANY is false and ALL is true.
• Never use NOT IN with a subquery that can return NULL. Use NOT EXISTS.
• Use = ANY ($1) with an array parameter to pass lists from applications.

🧪 Practice

1. (Beginner) List the names of products that appear in at least one order, using IN. Then list products cheaper than every laptop, using ALL.
2. (Intermediate) Find customers who never referred anyone with WHERE id NOT IN (SELECT referred_by FROM customers). Explain why it returns no rows, then write two correct versions: one that fixes the subquery and one with NOT EXISTS.
3. (Interview) "Why can NOT IN return an empty result when you know matching rows exist?" Hint: Expand NOT IN into a chain of <> and AND, and evaluate it when one list element is NULL.

EXISTS and Correlated Subqueries

Many questions are existence checks: "customers who have at least one shipped order", "products that were never ordered". You do not need the matching rows, only whether one exists. EXISTS asks exactly that question, and because it typically refers to the outer row, it is the standard example of a correlated subquery.

Correlated subqueries. A subquery is correlated when it references a column of the outer query. Logically, it is re-evaluated for every outer row with that row's values plugged in:

for each c in customers:
    does any row exist in orders where o.customer_id = c.id and o.status = 'shipped'?
    if yes: output c

SELECT c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1                                  -- the select list is ignored; 1 is a convention
    FROM orders AS o
    WHERE o.customer_id = c.id                -- the correlation: refers to the outer row
      AND o.status = 'shipped'
);
-- Ana Silva, Emma Weber

How EXISTS evaluates. EXISTS (subquery) is true if the subquery returns at least one row, and false otherwise. It is never NULL. The values in the subquery's select list do not matter, so SELECT 1, SELECT *, or even an empty SELECT (allowed in PostgreSQL) are the same. Evaluation can stop at the first matching row.

NOT EXISTS: the anti-join. "Rows with no match" is where EXISTS shines, because it is immune to the NULL problem of NOT IN:

-- Products that were never ordered
SELECT p.name
FROM products AS p
WHERE NOT EXISTS (SELECT FROM order_items oi WHERE oi.product_id = p.id);
-- Gift Card

Semi-joins and anti-joins. PostgreSQL does not literally run the subquery once per row. It recognizes EXISTS / IN as a semi-join ("return the outer row once if any match exists") and NOT EXISTS as an anti-join ("return the outer row if no match exists"), and can execute them as hash or merge joins:

EXPLAIN (COSTS OFF)
SELECT p.name FROM products p
WHERE NOT EXISTS (SELECT FROM order_items oi WHERE oi.product_id = p.id);
--  Hash Right Anti Join
--    Hash Cond: (oi.product_id = p.id)
--    ->  Seq Scan on order_items oi
--    ->  Hash
--          ->  Seq Scan on products p

Correlation in other places. Any subquery can be correlated: in WHERE with comparison operators, in the select list, or in LATERAL (4.3):

-- Products priced above the average of their own category
SELECT p.name, p.price, p.category_id
FROM products AS p
WHERE p.price > (SELECT avg(p2.price)
                 FROM products AS p2
                 WHERE p2.category_id = p.category_id);   -- different average for each row's category
-- UltraBook 14 (cat 3), Wireless Mouse (cat 4), Postgres Internals (cat 2)

Correlated subqueries that cannot be turned into joins (like this scalar comparison) do run once per outer row. They need an index on the correlated column (products (category_id) here) to stay fast on large tables, or a rewrite with a derived table or window function.

Pattern	Best written as	Planned as
Has at least one match	EXISTS or IN	Semi-join
Has no match	NOT EXISTS	Anti-join
Compare with a per-row aggregate	Correlated scalar subquery, derived table, or window function	Subplan per row, or join

Key takeaways

• A correlated subquery references the outer query and is logically evaluated once per outer row.
• EXISTS is true if the subquery returns any row. It is never NULL, and its select list is irrelevant.
• NOT EXISTS is the safe, efficient way to find rows without a match. PostgreSQL plans it as an anti-join.
• Index the columns a correlated subquery filters on.

🧪 Practice

1. (Beginner) Find customers who referred at least one other customer, using EXISTS.
2. (Intermediate) Find categories in which every product has stock above 10 (categories with no products should not count). Express "every" as "no product exists that violates it".
3. (Interview) "EXISTS, IN, or a JOIN: which is fastest for checking whether related rows exist?" Hint: In PostgreSQL, EXISTS and IN usually produce the same semi-join plan. Think about which form avoids duplicates, which one handles NULL safely in the negated form, and how to check with EXPLAIN.

Subqueries in FROM Clauses

Some questions take two steps: first compute per-order totals, then average them; first count orders per customer, then join the counts to customers. A subquery in the FROM clause, called a derived table, computes an intermediate result that the outer query treats as an ordinary table.

Syntax. Put a parenthesized SELECT in FROM and give it an alias. Its output columns become the derived table's columns:

-- Average order value: aggregate items per order first, then average the totals
SELECT round(avg(order_total), 2) AS avg_order_value
FROM (
    SELECT order_id, sum(quantity * unit_price) AS order_total
    FROM order_items
    GROUP BY order_id
) AS per_order;                    -- the alias (required before PostgreSQL 16)
-- 649.20

Nested aggregation like avg(sum(...)) is not allowed in one SELECT. The derived table is how you aggregate twice.

Pre-aggregating to avoid fan-out. The most important use of derived tables is the fix for row multiplication (Join Order and Readability): aggregate each child table to one row per parent before joining:

SELECT c.name, o.orders, i.units
FROM customers c
LEFT JOIN (SELECT customer_id, count(*) AS orders          -- one row per customer
           FROM orders
           GROUP BY customer_id) AS o
       ON o.customer_id = c.id
LEFT JOIN (SELECT o.customer_id, sum(oi.quantity) AS units -- one row per customer
           FROM orders o
           JOIN order_items oi ON oi.order_id = o.id
           GROUP BY o.customer_id) AS i
       ON i.customer_id = c.id
ORDER BY c.id;
--      name     | orders | units
-- --------------+--------+-------
--  Ana Silva    |      2 |     5        <- correct: 2 orders, not 4
--  ...
--  Farid Haddad |        |

Filtering on computed columns. WHERE cannot reference a select-list alias, but an outer query can filter on a derived table's columns. This makes derived tables a way to name an expression once and use it in several places:

SELECT *
FROM (
    SELECT name, price, round(price * 1.21, 2) AS gross
    FROM products
) AS t
WHERE gross BETWEEN 30 AND 100;

VALUES as a derived table. A VALUES list is an inline table, useful for small lookup lists without creating a table:

SELECT o.id, s.label
FROM orders o
JOIN (VALUES ('pending', 'Awaiting payment'),
             ('paid',    'Preparing'),
             ('shipped', 'On its way')) AS s(status, label)    -- column names in the alias
  ON s.status = o.status;

Derived tables and the planner. A derived table is not necessarily computed separately. Simple ones are pulled up (merged into the outer query) so the planner can optimize the whole thing, for example by pushing outer filters into the subquery. Subqueries with aggregates, DISTINCT, LIMIT, or window functions cannot be fully merged, but outer conditions on their grouping columns can still be pushed down.

Form	Scope	Typical use
Derived table (FROM (SELECT ...) AS t)	One place in one query	Two-step logic, pre-aggregation
CTE (WITH t AS (...))	Named, reusable within one query	Same, but more readable for several steps (5.1)
View (CREATE VIEW)	Stored in the schema	Logic shared by many queries (9.4)
LATERAL subquery	Can reference preceding tables	Per-row computations, top-N per group (4.3)

Key takeaways

• A derived table is a subquery in FROM whose result is used like a table. Before PostgreSQL 16, it must have an alias.
• Use it to aggregate in two steps, and to pre-aggregate child tables before joining so rows are not multiplied.
• An outer query can filter on columns computed in a derived table, which WHERE cannot do with aliases.
• Simple derived tables are merged into the outer query by the planner, so they rarely cost extra.

🧪 Practice

1. (Beginner) Using a derived table, return the orders whose total is above the average order total.
2. (Intermediate) For each country, return the number of customers and the total revenue of non-cancelled orders, including countries without revenue. Use two pre-aggregated derived tables and verify the numbers by hand.
3. (Interview) "What is the difference between a derived table, a CTE, and a view?" Hint: Compare scope, reusability, and how PostgreSQL optimizes each (CTE inlining since PostgreSQL 12, and MATERIALIZED).

Rewriting Subqueries as Joins

The same question can often be written either with a subquery or with a join. They are not always equivalent: they can differ in how many times a row appears and how NULLs are handled. Knowing how to translate between them, and when the results differ, lets you choose the clearest form and recognize what the planner does on its own.

Common translations

Subquery form	Join form	Caveat
WHERE x IN (SELECT y FROM t)	JOIN t ON t.y = x	The join returns duplicates when t has several matches. Add DISTINCT or keep the subquery
WHERE EXISTS (SELECT 1 FROM t WHERE t.y = x)	Same as above	Same duplicates caveat
WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.y = x)	LEFT JOIN t ON t.y = x WHERE t.pk IS NULL	Test a NOT NULL column of t
WHERE x NOT IN (SELECT y FROM t)	No exact join form	Differs from both forms above whenever y contains NULL
SELECT (SELECT agg FROM t WHERE t.y = x)	LEFT JOIN (SELECT y, agg FROM t GROUP BY y) ON ...	Join form computes all groups at once

The duplicates difference. A semi-join (IN / EXISTS) returns each outer row once. An inner join returns it once per match:

-- Subquery: each customer once
SELECT c.name FROM customers c
WHERE c.id IN (SELECT o.customer_id FROM orders o WHERE o.status = 'shipped');
-- Ana Silva, Emma Weber

-- Join: Ana appears twice (two shipped orders)
SELECT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';
-- Ana Silva, Ana Silva, Emma Weber

-- "Fixing" it with DISTINCT works, but adds a sort or hash over the whole result.
-- When you only need to know whether a match exists, EXISTS states that directly.

Choosing a form

You need	Prefer
Columns from the other table	Join
Only to know whether a related row exists	EXISTS / IN
Rows without a related row	NOT EXISTS
One aggregate per outer row, for a few rows	Correlated scalar subquery or LATERAL
Aggregates per row for a whole table	Join to a pre-aggregated derived table

What the planner does for you. PostgreSQL already transforms many subqueries:

• IN and EXISTS subqueries in WHERE become semi-joins, and NOT EXISTS becomes an anti-join, so the join algorithm and order are chosen freely.
• Simple derived tables are pulled up into the outer query.
• NOT IN (subquery) is not turned into an anti-join, because of its NULL semantics. It runs as a hashed subplan (or, if the subquery result is too large to fit in work_mem, a subplan executed once per row), which can be very slow on large tables.
• Correlated scalar subqueries in the select list or WHERE are not decorrelated. They run once per outer row.

So you rarely need to rewrite EXISTS as a join for speed. The rewrites that pay off are NOT IN to NOT EXISTS, and per-row scalar subqueries over large tables to a join with a pre-aggregated derived table. Confirm with EXPLAIN ANALYZE (7.3) rather than assuming.

Example: rewriting a per-row subquery for a full report

-- Before: one subquery execution per customer
SELECT c.name,
       (SELECT sum(oi.quantity * oi.unit_price)
        FROM orders o JOIN order_items oi ON oi.order_id = o.id
        WHERE o.customer_id = c.id) AS revenue
FROM customers c;

-- After: aggregate once, join once
SELECT c.name, r.revenue
FROM customers c
LEFT JOIN (SELECT o.customer_id, sum(oi.quantity * oi.unit_price) AS revenue
           FROM orders o
           JOIN order_items oi ON oi.order_id = o.id
           GROUP BY o.customer_id) AS r
       ON r.customer_id = c.id;

Key takeaways

• IN/EXISTS return each outer row once. A join returns it once per match, so translations must handle duplicates.
• NOT EXISTS and LEFT JOIN ... IS NULL are equivalent anti-joins. NOT IN is not, whenever NULLs are possible.
• PostgreSQL already turns IN/EXISTS into semi-joins, so choose those forms for clarity rather than speed.
• Worthwhile rewrites: NOT IN to NOT EXISTS, and per-row aggregate subqueries over many rows to a pre-aggregated join.

🧪 Practice

1. (Beginner) Rewrite "products that appear in at least one order" in three ways: IN, EXISTS, and a join. Make all three return the same rows.
2. (Intermediate) Generate 100,000 customers and 1,000,000 orders. Compare EXPLAIN ANALYZE of "customers without orders" written with NOT IN, NOT EXISTS, and LEFT JOIN ... IS NULL.
3. (Interview) "A colleague says subqueries are always slower than joins and should be rewritten. Do you agree?" Hint: Explain which subquery forms PostgreSQL turns into joins, which it cannot, and why correctness (duplicates, NULLs) matters more than the syntax.

<a id="46-modifying-data"></a>

4.6 Modifying Data

Reading data is only half of SQL. This subchapter covers the statements that change it: inserting rows one at a time or in bulk, updating and deleting rows based on other tables, returning the changed rows in the same statement, upserting with ON CONFLICT and MERGE, emptying tables with TRUNCATE, and loading files with COPY. All examples assume the sample schema. Rebuild it whenever you want to start over.

INSERT and Multi-Row Inserts

Every row in a database arrived through an INSERT (or COPY). Knowing its forms lets you rely on defaults instead of repeating them in application code, insert many rows in one round trip, and copy data between tables without moving it through the application.

Basic form. Name the target columns, then supply values in the same order. Columns you leave out get their DEFAULT, or NULL if they have none:

INSERT INTO customers (name, email, country)
VALUES ('Gina Rossi', 'gina@example.com', 'IT');
-- id comes from the identity sequence, created_at from DEFAULT now(), referred_by is NULL

Always list the columns. INSERT INTO customers VALUES (...) depends on the table's column order and breaks when a column is added.

Defaults and identity columns

You want	Write
The column default for one column	Omit the column, or write DEFAULT as its value
All defaults	INSERT INTO t DEFAULT VALUES
An explicit value in a GENERATED ALWAYS identity column	OVERRIDING SYSTEM VALUE (for data migrations only)

INSERT INTO products (sku, name, price, stock)
VALUES ('BK-003', 'Indexing Deep Dive', 45, DEFAULT);     -- stock gets its default, 0

INSERT INTO customers (id, name, country) VALUES (100, 'Test', 'US');
-- ERROR:  cannot insert a non-DEFAULT value into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

Multi-row inserts. One statement can insert many rows. Each statement costs a network round trip, parsing, and (outside an explicit transaction) a commit, so batching rows is much faster than inserting them one by one:

INSERT INTO customers (name, country) VALUES
  ('Hana Sato',   'JP'),
  ('Ivan Petrov', 'BG');

Method	Relative speed for many rows	Notes
One INSERT per row, each autocommitted	Slowest	Every row waits for a WAL flush
One INSERT per row inside one transaction	Better	One commit for all rows
Multi-row VALUES (hundreds to a few thousand rows per statement)	Much better	Fewer round trips and statements
COPY	Fastest	Streams rows. See COPY for Bulk Loading

INSERT ... SELECT. Instead of VALUES, any query can provide the rows. The data never leaves the server:

-- Add a subcategory under Electronics, looking up the parent's id
INSERT INTO categories (name, parent_id)
SELECT 'Tablets', id FROM categories WHERE name = 'Electronics';

-- Archive old shipped orders into a table with the same structure
-- Without INCLUDING IDENTITY, id is a plain bigint, so the archive can keep the original ids
CREATE TABLE orders_archive (LIKE orders INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
INSERT INTO orders_archive
SELECT * FROM orders WHERE status = 'shipped' AND placed_at < '2025-02-01';

Atomicity. An INSERT is all-or-nothing. If one row of a multi-row insert violates a constraint, no rows are inserted. Values taken from an identity sequence are not given back on failure, so gaps in ids are normal (3.1).

Key takeaways

• Always list target columns. Omitted columns get their default or NULL.
• DEFAULT and DEFAULT VALUES request defaults explicitly. Identity columns reject explicit values unless you use OVERRIDING SYSTEM VALUE.
• Batch rows with multi-row VALUES or INSERT ... SELECT instead of one statement per row.
• A failed INSERT inserts nothing, but sequence values it consumed are not reused.

🧪 Practice

1. (Beginner) Insert two new products in one statement, one with an explicit stock and one using the default. Query them to check the defaults.
2. (Intermediate) Create a customer_stats(customer_id, orders, last_order_at) table and fill it with one INSERT ... SELECT from orders.
3. (Interview) "An import job inserts 2 million rows one INSERT at a time and takes an hour. How would you speed it up?" Hint: Think about commits per row, round trips, multi-row statements, COPY, and indexes or constraints on the target table.

UPDATE with FROM

Updates frequently depend on another table: apply a price list from a supplier feed, decrease stock by the quantities in an order, mark customers as active based on their recent orders. PostgreSQL's UPDATE ... FROM joins the target table to other tables and uses their columns in SET and WHERE.

Basic UPDATE. SET assigns new values, and WHERE chooses the rows. Expressions on the right side see the old values of the row:

UPDATE products
SET price = round(price * 1.10, 2),          -- 10% increase, computed from the old price
    stock = stock + 5
WHERE category_id = 2;

An UPDATE without WHERE changes every row in the table. A habit that prevents disasters: write the WHERE first, run it as a SELECT to see which rows match, and run the UPDATE inside a transaction you can roll back.

UPDATE ... FROM. Lists additional tables, joined to the target through the WHERE clause:

CREATE TABLE price_changes (sku text, new_price numeric(10,2));
INSERT INTO price_changes VALUES ('ACC-001', 11.00), ('BK-001', 42.00), ('NOPE', 1.00);

UPDATE products AS p
SET price = pc.new_price
FROM price_changes AS pc
WHERE pc.sku = p.sku                         -- the join condition
RETURNING p.sku, p.price;
--    sku   | price
-- ---------+-------
--  ACC-001 | 11.00
--  BK-001  | 42.00
-- ('NOPE' matches no product and is ignored)

Do not repeat the target table in FROM. UPDATE products ... FROM products, price_changes joins a second, independent copy of products, which usually updates every row.

The multiple-match trap. If a target row joins to several rows in FROM, PostgreSQL updates it only once, using an unpredictable one of the matches. There is no error:

-- Intent: return all ordered quantities to stock
UPDATE products p
SET stock = p.stock + oi.quantity
FROM order_items oi
WHERE oi.product_id = p.id;
-- USB-C Cable appears in two order lines (quantities 2 and 1).
-- Its stock grows by 2 or by 1, never by 3.

-- Fix: aggregate first so each target row has exactly one match
UPDATE products p
SET stock = p.stock + t.qty
FROM (SELECT product_id, sum(quantity) AS qty
      FROM order_items
      GROUP BY product_id) AS t
WHERE t.product_id = p.id;

Alternatives

Form	Example	Notes
UPDATE ... FROM	As above	PostgreSQL extension. Efficient join
Correlated subquery in SET	SET price = (SELECT new_price FROM price_changes pc WHERE pc.sku = products.sku)	Standard SQL, but sets NULL for rows without a match unless WHERE restricts them. Errors if there are several matches
Row assignment	SET (price, stock) = (SELECT ..., ... FROM ...)	Several columns from one subquery
MERGE	See MERGE Statement	Standard SQL. Errors on multiple matches instead of silently picking one

Key takeaways

• SET expressions see the old row values. An UPDATE without WHERE changes every row.
• UPDATE t SET ... FROM other WHERE join_condition updates using columns of other tables. Do not list the target table in FROM again.
• If a target row matches several FROM rows, only one arbitrary match is applied. Pre-aggregate so each row has at most one match.
• Run the WHERE as a SELECT first, and wrap risky updates in a transaction.

🧪 Practice

1. (Beginner) Mark as discontinued every product that has zero stock, and return the affected SKUs.
2. (Intermediate) Decrease stock by the ordered quantities for all paid orders in one UPDATE ... FROM, making sure products ordered several times are decreased by the full total.
3. (Interview) "An UPDATE ... FROM updated fewer units than expected and raised no error. What could be wrong?" Hint: Count how many FROM rows match each target row, and compare with how MERGE behaves in the same situation.

DELETE with USING

Deleting rows often depends on other tables too: remove items belonging to cancelled orders, delete sessions of users who closed their accounts. DELETE ... USING is the counterpart of UPDATE ... FROM: it joins the target table to other tables to decide which rows to delete.

Basic DELETE. Removes the rows that match WHERE. Without WHERE, it removes every row:

DELETE FROM orders WHERE status = 'cancelled';

DELETE ... USING. Lists additional tables, joined through WHERE:

-- Delete the items of cancelled orders
DELETE FROM order_items AS oi
USING orders AS o
WHERE o.id = oi.order_id
  AND o.status = 'cancelled'
RETURNING oi.*;
--  order_id | product_id | quantity | unit_price
-- ----------+------------+----------+------------
--         4 |          2 |        1 |     999.00

The same with a subquery, in standard SQL:

DELETE FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE status = 'cancelled');

Both usually get the same plan. USING is convenient when the condition involves several tables or columns.

Deletes and foreign keys. A delete can be blocked or can spread, depending on the foreign keys that reference the table (2.2):

DELETE FROM customers WHERE id = 1;
-- ERROR:  update or delete on table "customers" violates foreign key constraint
--         "customers_referred_by_fkey" on table "customers"
-- Ana referred other customers; the default action (NO ACTION) blocks the delete

DELETE FROM orders WHERE id = 1;
-- Succeeds, and ON DELETE CASCADE also deletes the order's order_items rows

Make sure the referencing columns are indexed. Without an index on order_items (order_id), every deleted order requires a full scan of order_items to check or cascade. In this schema, the primary key (order_id, product_id) covers it.

Deleting many rows. A DELETE does not free space immediately: under MVCC, the old row versions stay until VACUUM removes them (8.3). Deleting tens of millions of rows in one statement also holds locks for a long time and generates a lot of WAL. Common approaches:

Situation	Approach
Delete all rows	TRUNCATE (TRUNCATE vs. DELETE)
Delete old data on a schedule	Partition by time and drop old partitions (6.4)
One-off large delete in a busy table	Delete in batches (for example 10,000 rows per transaction)
Keep only a small fraction of a large table	Copy the rows to keep into a new table and swap the tables

-- One batch: repeat until it deletes 0 rows
DELETE FROM orders_archive
WHERE id IN (SELECT id FROM orders_archive
             WHERE placed_at < '2025-01-01'
             LIMIT 10000);

Key takeaways

• DELETE FROM t USING other WHERE ... deletes rows based on a join. WHERE ... IN (subquery) is the standard alternative.
• Foreign keys can block a delete (NO ACTION, RESTRICT) or cascade it. Index referencing columns.
• Deleted rows occupy space until vacuumed. Use TRUNCATE, partition drops, or batches for very large deletes.

🧪 Practice

1. (Beginner) Delete all pending orders and return their ids. What happened to their order items, and why?
2. (Intermediate) Delete every customer who has no orders and was not referred by anyone and has not referred anyone, using NOT EXISTS. Explain why a simpler query would fail with a foreign key error.
3. (Interview) "You need to delete 300 million rows from a 1-billion-row table in production. How do you do it?" Hint: Compare one big DELETE, batching, copying the remaining rows to a new table, and partitioning. Consider locks, WAL volume, replication lag, and bloat.

RETURNING Clause

After inserting a row, an application usually needs something the database generated: the new id, the default created_at, or values computed by triggers. Without RETURNING, that would take a second query, which is slower and can race with other sessions. RETURNING makes INSERT, UPDATE, DELETE, and MERGE return the rows they changed, as if they were a SELECT.

Basic use

-- The new row's generated values
INSERT INTO customers (name, country)
VALUES ('Hana Sato', 'JP'), ('Ivan Petrov', 'BG')
RETURNING id, name, created_at;

-- The rows after the update
UPDATE products SET stock = stock - 1 WHERE id = 3
RETURNING id, stock;

-- The rows that were deleted (for logging, or to show the user what happened)
DELETE FROM orders WHERE status = 'cancelled'
RETURNING id, customer_id;

RETURNING accepts any expressions: RETURNING *, computed columns, and aliases. By default, INSERT and UPDATE return the new row values and DELETE returns the deleted values.

Old and new values (PostgreSQL 18+). PostgreSQL 18 lets RETURNING refer to both versions of the row through old and new. For INSERT, old columns are NULL. For DELETE, new columns are NULL:

UPDATE products
SET price = round(price * 1.10, 2)
WHERE category_id = 2
RETURNING sku, old.price AS old_price, new.price AS new_price;
--   sku   | old_price | new_price
-- --------+-----------+-----------
--  BK-001 |     39.00 |     42.90
--  BK-002 |     54.00 |     59.40

-- Rename them if old/new clash with table names
UPDATE products SET stock = stock - 1 WHERE id = 3
RETURNING WITH (OLD AS before, NEW AS after) before.stock, after.stock;   -- 200, 199

Before PostgreSQL 18, getting the old value required a self-join in UPDATE ... FROM or a trigger.

Chaining modifications with CTEs. A data-modifying statement with RETURNING can be used in a WITH clause, and its output feeds the next statement. This moves rows between tables atomically, in one statement (5.1):

-- Move old shipped orders to the archive in one atomic statement
WITH moved AS (
    DELETE FROM orders
    WHERE status = 'shipped' AND placed_at < '2025-02-01'
    RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

Key takeaways

• RETURNING makes INSERT, UPDATE, DELETE, and MERGE return the affected rows, saving a round trip and avoiding races.
• Use it to fetch generated ids, defaults, and trigger-computed values right after an insert.
• PostgreSQL 18 adds old.* and new.* in RETURNING to see values before and after the change.
• Inside a WITH clause, RETURNING output can feed another statement, so multi-step changes stay atomic.

🧪 Practice

1. (Beginner) Insert a new order for customer 6 and return its id and placed_at. Then insert its order lines using that id.
2. (Intermediate) In one statement, insert an order and its items: use a CTE that inserts the order with RETURNING id, and an INSERT ... SELECT that uses that id.
3. (Interview) "Why is INSERT ... RETURNING id better than INSERT followed by SELECT max(id)?" Hint: Consider two sessions inserting at the same time, and the number of round trips.

INSERT ON CONFLICT Upserts

A common requirement is "insert this row, but if it already exists, update it instead": syncing product data from a supplier, counting page views per day, storing the latest setting per user. Doing it in two steps (SELECT, then INSERT or UPDATE) has a race condition: two sessions can both see "not there" and both try to insert. INSERT ... ON CONFLICT performs this upsert atomically.

How it works. PostgreSQL tries to insert. If the row would violate a unique constraint or unique index (the conflict target), it performs the alternative action instead of raising an error:

-- Do nothing if the SKU already exists
INSERT INTO products (sku, name, price, stock)
VALUES ('ACC-001', 'USB-C Cable', 12.50, 50)
ON CONFLICT (sku) DO NOTHING;
-- INSERT 0 0: nothing inserted, no error

-- Insert new SKUs, update existing ones
INSERT INTO products (sku, name, price, stock)
VALUES ('ACC-001', 'USB-C Cable 1m', 11.90, 50),     -- exists: updated
       ('ACC-003', 'Laptop Sleeve',  19.90, 40)      -- new: inserted
ON CONFLICT (sku) DO UPDATE
SET name  = EXCLUDED.name,                          -- EXCLUDED = the row that was proposed
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock         -- products = the existing row
RETURNING sku, name, price, stock;
--    sku   |      name      | price | stock
-- ---------+----------------+-------+-------
--  ACC-001 | USB-C Cable 1m | 11.90 |   250
--  ACC-003 | Laptop Sleeve  | 19.90 |    40

The conflict target

Form	Meaning
ON CONFLICT (col, ...)	Any unique index or constraint on exactly these columns. The usual choice
ON CONFLICT (col) WHERE predicate	A matching partial unique index
ON CONFLICT ON CONSTRAINT name	A specific named constraint
ON CONFLICT DO NOTHING (no target)	Any unique violation. Only allowed with DO NOTHING

The target must match an existing unique index. ON CONFLICT (name) on a column without one fails with "there is no unique or exclusion constraint matching the ON CONFLICT specification".

Conditional updates. A WHERE on DO UPDATE skips the update for some conflicting rows, for example to avoid writing (and creating a new row version) when nothing changed:

INSERT INTO products (sku, name, price)
VALUES ('ACC-002', 'Wireless Mouse', 27.90)
ON CONFLICT (sku) DO UPDATE
SET price = EXCLUDED.price
WHERE products.price IS DISTINCT FROM EXCLUDED.price     -- only if the price changed
  AND NOT products.discontinued;                         -- and never for discontinued items

Telling inserts from updates. On PostgreSQL 18, RETURNING (old.sku IS NULL) AS inserted is true for inserted rows. On older versions, RETURNING (xmax = 0) AS inserted is a widely used trick, but it relies on an internal implementation detail.

Rules and pitfalls

• The same statement cannot propose two rows with the same key: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time. Deduplicate the input first (for example with DISTINCT ON).
• An identity or serial value is consumed for every attempted insert, including those that turn into updates or do nothing. Expect large gaps in ids in tables that are upserted often.
• DO NOTHING returns no row through RETURNING for skipped rows. To get the existing row's id, use DO UPDATE SET col = EXCLUDED.col (a no-op update, which still writes a new row version) or a follow-up SELECT.
• Under concurrency, ON CONFLICT guarantees an insert-or-update outcome without unique-violation errors. This is its main advantage over MERGE.

Key takeaways

• INSERT ... ON CONFLICT (key) DO NOTHING | DO UPDATE SET ... is an atomic, concurrency-safe upsert.
• EXCLUDED.col is the proposed value. The table name (or alias) refers to the existing row.
• The conflict target must correspond to a unique index or constraint.
• Deduplicate input keys within one statement, and expect sequence gaps.

🧪 Practice

1. (Beginner) Create page_views(page text PRIMARY KEY, views bigint NOT NULL). Write one statement that adds 1 to a page's views, creating the row with views = 1 if needed. Run it three times.
2. (Intermediate) Write an upsert that loads a batch of products from a VALUES list containing a duplicate SKU. Make it succeed by deduplicating the input so that the last occurrence of each SKU wins.
3. (Interview) "Why not implement upsert as SELECT, then INSERT or UPDATE in application code?" Hint: Walk through two concurrent sessions doing it, and consider what isolation level or locking would be needed to make it correct.

MERGE Statement

Synchronizing a table with a source of changes often requires more than insert-or-update: insert new rows, update changed ones, mark or delete rows that should no longer be active, and maybe ignore some rows entirely. MERGE (SQL standard, PostgreSQL 15+) expresses all of this in one statement: it joins a source to a target and runs a different action depending on whether and how each row matched.

Structure

MERGE INTO target AS t
USING source AS s ON <join condition>
WHEN MATCHED [AND <condition>] THEN
    UPDATE SET ... | DELETE | DO NOTHING
WHEN NOT MATCHED [BY TARGET] [AND <condition>] THEN
    INSERT (...) VALUES (...) | DO NOTHING
WHEN NOT MATCHED BY SOURCE [AND <condition>] THEN      -- PostgreSQL 17+
    UPDATE SET ... | DELETE | DO NOTHING

For each source row (and, with BY SOURCE, each unmatched target row), PostgreSQL checks the WHEN clauses in order and runs the first one whose kind and condition fit. If none fits, the row is left alone.

 source row matched a target row?  -- yes --> first WHEN MATCHED clause whose AND-condition holds
                                   -- no  --> first WHEN NOT MATCHED clause whose AND-condition holds
 target row matched by no source?  ------> first WHEN NOT MATCHED BY SOURCE clause (17+)

Example: applying a supplier feed

CREATE TABLE stock_feed (sku text PRIMARY KEY, name text, price numeric(10,2),
                         stock int, active boolean);
INSERT INTO stock_feed VALUES
  ('LAP-001', 'UltraBook 14',       1249.00,  8, true),    -- exists: new price and stock
  ('LAP-002', 'WorkBook 15',         999.00,  0, false),   -- exists: supplier dropped it
  ('BK-003',  'Indexing Deep Dive',   45.00, 25, true);    -- new product

MERGE INTO products AS p
USING stock_feed AS f ON f.sku = p.sku
WHEN MATCHED AND NOT f.active THEN                        -- checked first
    UPDATE SET discontinued = true, stock = 0
WHEN MATCHED THEN
    UPDATE SET price = f.price, stock = f.stock
WHEN NOT MATCHED AND f.active THEN
    INSERT (sku, name, price, stock) VALUES (f.sku, f.name, f.price, f.stock)
RETURNING merge_action(), p.sku, p.price, p.stock, p.discontinued;   -- RETURNING: 17+

--  merge_action |   sku   |  price  | stock | discontinued
-- --------------+---------+---------+-------+--------------
--  UPDATE       | LAP-001 | 1249.00 |     8 | f
--  UPDATE       | LAP-002 |  999.00 |     0 | t
--  INSERT       | BK-003  |   45.00 |    25 | f

merge_action() (PostgreSQL 17+) returns 'INSERT', 'UPDATE', or 'DELETE' for each returned row.

Full synchronization with NOT MATCHED BY SOURCE. When the source is the complete truth, target rows missing from it should be removed or flagged:

-- prices(sku, price) is the target; feed(sku, price) is the complete current price list
MERGE INTO prices AS t
USING feed AS s ON s.sku = t.sku
WHEN MATCHED THEN UPDATE SET price = s.price
WHEN NOT MATCHED BY TARGET THEN INSERT (sku, price) VALUES (s.sku, s.price)
WHEN NOT MATCHED BY SOURCE THEN DELETE;              -- rows not in the feed are removed

MERGE versus INSERT ON CONFLICT

Aspect	INSERT ... ON CONFLICT	MERGE
Standard	PostgreSQL extension	SQL standard (also in Oracle, SQL Server, and others)
Match condition	A unique index	Any join condition
Actions	Insert, or update / nothing on conflict	Insert, update, delete, nothing, several conditional branches
Rows missing from the source	Not possible	WHEN NOT MATCHED BY SOURCE (17+)
Concurrent inserts of the same key	Safe: never raises a unique violation	Can fail with a unique violation, because the match is decided before inserting
A target row matched by several source rows	Error (within one statement)	Error: MERGE command cannot affect row a second time

Use ON CONFLICT for high-concurrency upserts keyed by a unique constraint (counters, caches, idempotent API writes). Use MERGE for batch synchronization with several kinds of action, typically in ETL jobs that are not running concurrently with each other.

Key takeaways

• MERGE joins a source to a target and runs the first WHEN clause that fits each row: update, delete, insert, or do nothing.
• AND conditions on WHEN clauses and their order let one statement handle many cases.
• PostgreSQL 17 added RETURNING with merge_action() and WHEN NOT MATCHED BY SOURCE for full synchronization.
• Prefer INSERT ... ON CONFLICT for concurrent single-key upserts, and MERGE for batch synchronization.

🧪 Practice

1. (Beginner) Write a MERGE that applies price_changes (from UPDATE with FROM) to products, inserting nothing.
2. (Intermediate) Create a customer_stats table and write one MERGE that inserts stats for new customers, updates stats for existing ones, and deletes stats for customers that no longer exist. Return what happened to each row.
3. (Interview) "Your nightly sync uses MERGE, and occasionally fails with a duplicate key error when it overlaps with live traffic. Why, and what would you change?" Hint: Consider when MERGE decides that a row is not matched, what a concurrent insert does in between, and how ON CONFLICT avoids this.

TRUNCATE vs. DELETE

Emptying a table is common: clearing a staging table before each import, resetting test data, discarding a scratch table. DELETE FROM t without WHERE does it row by row. TRUNCATE t removes all the data at once by replacing the table's storage files. They lead to the same empty table but differ in speed, locking, and side effects.

How they differ internally. DELETE marks each row version as deleted, writes WAL for each row, fires per-row triggers, and leaves dead rows for VACUUM to clean up. The table file keeps its size. TRUNCATE creates a new empty file for the table and its indexes and discards the old one when the transaction commits. Its cost is roughly constant, whatever the table's size.

TRUNCATE order_items;                         -- instant, even for billions of rows

TRUNCATE orders;
-- ERROR:  cannot truncate a table referenced in a foreign key constraint
-- DETAIL:  Table "order_items" references "orders".
-- (Even though order_items is now empty: the check is on the constraint, not the data)

TRUNCATE orders, order_items;                 -- truncate both together
TRUNCATE customers RESTART IDENTITY CASCADE;  -- also truncates every referencing table, resets sequences

TRUNCATE is transactional in PostgreSQL: inside a transaction, ROLLBACK restores the data.

Comparison

Aspect	DELETE FROM t	TRUNCATE t
Can filter rows	Yes (WHERE)	No: all rows
Speed on large tables	Proportional to the number of rows	Nearly constant
Space	Freed for reuse only after VACUUM. The file does not shrink	Returned to the operating system immediately
WAL volume	Every row	Minimal
Lock	ROW EXCLUSIVE: other sessions can keep reading and writing	ACCESS EXCLUSIVE: blocks all access, even SELECT, until commit
Triggers	ON DELETE row and statement triggers	ON TRUNCATE statement triggers only
Foreign keys	Checked per row (cascades run)	Refused if referenced, unless all referencing tables are truncated too or CASCADE is used
Identity / sequences	Unchanged	Reset with RESTART IDENTITY
RETURNING	Yes	No
Rollback	Yes	Yes
MVCC	Concurrent transactions keep seeing the old rows in their snapshot	Not MVCC-safe: a transaction whose snapshot predates the truncate sees the table as empty
Logical replication	Replicated as row deletes	Replicated as a truncate (PostgreSQL 11+)

Choosing. Use TRUNCATE for staging tables, test resets, and any "empty the whole table" job where a short exclusive lock is acceptable. Use DELETE when you need a WHERE, row triggers (for example audit logging), RETURNING, or when the table is under constant concurrent use and even a brief ACCESS EXCLUSIVE lock would queue up traffic.

Key takeaways

• TRUNCATE replaces the table's files: near-instant, frees space immediately, and generates little WAL.
• It takes an ACCESS EXCLUSIVE lock, skips row triggers, and needs CASCADE or all referencing tables when foreign keys point to it.
• TRUNCATE is transactional in PostgreSQL, but not MVCC-safe for concurrent transactions with older snapshots.
• RESTART IDENTITY resets owned sequences. DELETE never does.

🧪 Practice

1. (Beginner) Create a copy of products, then empty it once with DELETE and once with TRUNCATE. Check pg_total_relation_size() after each.
2. (Intermediate) In one session, BEGIN; TRUNCATE products CASCADE; without committing. In a second session, run SELECT count(*) FROM products;. What happens, and what does it tell you about locks? Roll back and check the data.
3. (Interview) "A nightly job runs TRUNCATE on a table used by the website, and the site freezes for minutes even though the truncate itself is instant. Why?" Hint: Think about which lock TRUNCATE needs, a long-running query holding a conflicting lock, and how other sessions queue behind the waiting TRUNCATE. Look up lock_timeout.

COPY for Bulk Loading

Loading a CSV export with a million rows, migrating a table between systems, or dumping a query result for a spreadsheet are bulk operations. Row-by-row INSERTs pay per-statement overhead for every row. COPY streams data in or out of a table in a simple text, CSV, or binary format, and is the fastest way to move large amounts of data into PostgreSQL. Data loading basics were introduced in 1.4. This topic looks at COPY itself in more depth.

Directions and endpoints

Command	Data source / destination	Who can use it
COPY t FROM '/server/path'	A file on the database server	Superusers or members of pg_read_server_files
COPY t TO '/server/path'	A file on the server	Superusers or pg_write_server_files
COPY t FROM PROGRAM 'cmd'	Output of a shell command on the server	Superusers or pg_execute_server_program
COPY t FROM STDIN / TO STDOUT	The client connection	Anyone with table privileges. Drivers use this
\copy t FROM 'file' (psql)	A file on the client, sent over STDIN	Anyone with table privileges

Importing a CSV

CREATE TABLE import_products (sku text, name text, price numeric(10,2), stock int);

-- In psql, \copy reads the file from your machine:
-- \copy import_products FROM 'products.csv' WITH (FORMAT csv, HEADER true)

-- The same data streamed inline (as a driver would send it)
COPY import_products FROM STDIN WITH (FORMAT csv, HEADER true);
sku,name,price,stock
BK-010,"Query Tuning, 2nd Ed.",49.00,10
BK-012,Vacuum Explained,35.50,
\.
-- The quoted field may contain commas. An empty unquoted field becomes NULL.

Exporting

-- A whole table (default text format: tab-separated, \N for NULL)
COPY products (sku, name, price) TO STDOUT;

-- Any query, as CSV with a header
COPY (SELECT sku, name, price FROM products WHERE category_id = 2 ORDER BY sku)
TO STDOUT WITH (FORMAT csv, HEADER true);
-- sku,name,price
-- BK-001,SQL in Practice,39.00
-- BK-002,Postgres Internals,54.00

Important options

Option	Purpose
FORMAT text | csv | binary	Text (default), CSV, or PostgreSQL's binary format (fast, but version- and type-sensitive)
HEADER true | match	Skip (on input) or write (on output) a header line. match (15+) also checks the column names
DELIMITER, QUOTE, ESCAPE	Customize the CSV dialect
NULL 'string'	The string that represents NULL
FORCE_NOT_NULL, FORCE_NULL	Treat empty or quoted values in specific columns as empty strings or NULL
ENCODING 'name'	The file's encoding, if it differs from the client encoding
WHERE condition (on COPY FROM)	Load only matching rows (12+)
ON_ERROR ignore	Skip rows with values that do not convert to the column type, instead of failing (17+)
LOG_VERBOSITY verbose	Report each skipped row (17+)
REJECT_LIMIT n	Fail anyway if more than n rows are skipped (18+)
FREEZE	Write rows already frozen if the table was created or truncated in the same transaction. Saves a later vacuum

Handling bad rows. By default, one malformed value aborts the whole COPY, and nothing is loaded. PostgreSQL 17 added ON_ERROR ignore:

COPY import_products FROM STDIN
WITH (FORMAT csv, HEADER true, ON_ERROR ignore, LOG_VERBOSITY verbose);
sku,name,price,stock
BK-011,Replication Basics,abc,5
\.
-- NOTICE:  skipping row due to data type incompatibility at line 2 for column "price": "abc"

ON_ERROR ignore only covers data type conversion errors. Constraint violations (unique, foreign key, check) still abort the load. For messy data, a common pattern is to COPY into a staging table whose columns are all text, then clean, validate, and INSERT ... SELECT into the real table, using pg_input_is_valid() (Type Casting and Conversion) to find bad values.

Fast loading checklist

• Use COPY (or your driver's copy API, such as copy_expert in psycopg2 or COPY support in psycopg 3, JDBC's CopyManager, or pgx.CopyFrom in Go) instead of INSERTs.
• Load into a staging or new table without indexes, foreign keys, and triggers, then add them afterwards.
• Create or truncate the table in the same transaction as the COPY (with wal_level = minimal, this can skip WAL entirely, and it enables FREEZE).
• Run ANALYZE after the load so the planner has fresh statistics.

Key takeaways

• COPY streams rows into or out of a table and is the fastest bulk-loading method.
• COPY ... FROM 'file' reads on the server and needs special privileges. \copy and FROM STDIN read on the client.
• COPY (query) TO exports any query result. FORMAT csv, HEADER true covers most interchange needs.
• One bad value aborts a load unless you use ON_ERROR ignore (17+). Load messy data into a text staging table and validate with SQL.

🧪 Practice

1. (Beginner) Export all orders with their customer names to a CSV file using \copy (SELECT ...) TO 'orders.csv' WITH (FORMAT csv, HEADER true), and open it in a spreadsheet.
2. (Intermediate) Generate a CSV with 1,000,000 rows (for example with COPY (SELECT ... FROM generate_series(...)) TO). Time loading it into an empty table with COPY, with multi-row INSERT statements, and into a table that already has three indexes.
3. (Interview) "Design a daily import of a 5 GB CSV file from a partner, where a few rows are usually malformed and the table must stay queryable during the load." Hint: Consider a staging table, ON_ERROR or a text-typed staging layer, validation queries, and how to swap in the new data (one transaction, MERGE, or partition exchange) without blocking readers.

<a id="5-advanced-sql-in-postgres"></a>