Advanced PostgreSQL: A Fast-Paced Revision Guide
JOINs to LATERAL, on one schema. Not for beginners.
TL;DR: this post is not meant for beginners. if you have basic understanding of SQL then this post is understandable to you
One schema (acme), one dataset, every pattern below runs as-is on PostgreSQL 14+. Load the setup, then work through the sections.
0. Schema and Seed Data
First check the ER Diagram
CREATE SCHEMA acme;
SET search_path = acme;
CREATE TABLE departments (
dept_id int PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE employees (
emp_id int PRIMARY KEY,
name text NOT NULL,
manager_id int REFERENCES employees(emp_id),
dept_id int REFERENCES departments(dept_id),
salary numeric(10,2) NOT NULL,
hired_on date NOT NULL
);
CREATE TABLE customers (
customer_id int PRIMARY KEY,
name text NOT NULL,
email text,
country text,
referred_by int REFERENCES customers(customer_id)
);
CREATE TABLE categories (
category_id int PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE products (
product_id int PRIMARY KEY,
name text NOT NULL,
category_id int REFERENCES categories(category_id),
price numeric(10,2) NOT NULL
);
CREATE TABLE orders (
order_id int PRIMARY KEY,
customer_id int REFERENCES customers(customer_id),
order_date date NOT NULL,
status text NOT NULL CHECK (status IN ('pending','completed','cancelled'))
);
CREATE TABLE order_items (
order_id int REFERENCES orders(order_id),
product_id int REFERENCES products(product_id),
qty int NOT NULL,
unit_price numeric(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE payments (
payment_id int PRIMARY KEY,
order_id int REFERENCES orders(order_id),
amount numeric(10,2) NOT NULL,
paid_at timestamptz NOT NULL,
method text NOT NULL
);
INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'Support'),(4,'Legal');
INSERT INTO employees VALUES
(1,'Alice',NULL,1,250000,'2018-01-15'),
(2,'Bob',1,1,180000,'2019-03-01'),
(3,'Carol',1,2,170000,'2019-06-10'),
(4,'Dan',2,1,120000,'2021-02-01'),
(5,'Eve',2,1,125000,'2021-08-20'),
(6,'Frank',3,2,90000,'2022-01-05'),
(7,'Grace',4,1,95000,'2023-04-17'),
(8,'Heidi',3,NULL,70000,'2024-09-01');
INSERT INTO customers VALUES
(1,'Ana','ana@mail.com','US',NULL),
(2,'Ben','ben@mail.com','UK',1),
(3,'Chen',NULL,'SG',1),
(4,'Dina','dina@mail.com','BD',2),
(5,'Eli','eli@mail.com','US',NULL);
INSERT INTO categories VALUES (1,'Hardware'),(2,'Software'),(3,'Services');
INSERT INTO products VALUES
(1,'Keyboard',1,49.00),
(2,'Monitor',1,299.00),
(3,'IDE License',2,99.00),
(4,'Backup Plan',2,19.99),
(5,'Consulting Hour',3,150.00),
(6,'Webcam',1,79.00);
INSERT INTO orders VALUES
(1,1,'2026-01-10','completed'),
(2,1,'2026-02-14','completed'),
(3,2,'2026-02-20','completed'),
(4,2,'2026-03-05','cancelled'),
(5,3,'2026-03-15','completed'),
(6,4,'2026-04-01','pending'),
(7,1,'2026-04-12','completed');
INSERT INTO order_items VALUES
(1,1,2,49.00),(1,3,1,99.00),
(2,2,1,299.00),
(3,5,4,150.00),(3,4,1,19.99),
(4,1,1,49.00),
(5,3,3,99.00),(5,5,2,150.00),
(6,2,2,299.00),
(7,4,12,19.99),(7,1,1,49.00);
INSERT INTO payments VALUES
(1,1,197.00,'2026-01-10 10:00+00','card'),
(2,2,299.00,'2026-02-14 11:30+00','card'),
(3,3,619.99,'2026-02-21 09:15+00','wire'),
(4,5,597.00,'2026-03-16 14:00+00','card'),
(5,7,100.00,'2026-04-12 16:45+00','card'),
(6,7,188.88,'2026-04-20 08:00+00','card');
Seeded on purpose: a department with no employees (Legal), an employee with no department (Heidi), a customer with no orders (Eli), a product never sold (Webcam), an order with no payment (6), an order with two payments (7), and a NULL email (Chen). Each one exposes a different bug later.
1. JOINs: INNER, LEFT, RIGHT, FULL OUTER, CROSS
-- INNER: only matching rows
SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d USING (dept_id); -- Heidi and Legal vanish
-- LEFT: keep all customers, even without orders
SELECT c.name, count(o.order_id) AS orders -- count(col), not count(*)
FROM customers c
LEFT JOIN orders o USING (customer_id)
GROUP BY c.name;
-- RIGHT: keep all departments (same as LEFT with tables swapped)
SELECT d.name, count(e.emp_id) AS headcount
FROM employees e
RIGHT JOIN departments d USING (dept_id)
GROUP BY d.name;
-- FULL OUTER: orphans on both sides in one pass
SELECT e.name AS employee, d.name AS dept
FROM employees e
FULL JOIN departments d USING (dept_id)
WHERE e.emp_id IS NULL OR d.dept_id IS NULL; -- Heidi and Legal
-- CROSS: build a dense grid, then LEFT JOIN facts onto it (gap filling)
SELECT m::date AS month, cat.name AS category,
coalesce(sum(oi.qty * oi.unit_price), 0) AS revenue
FROM generate_series('2026-01-01'::date, '2026-04-01', interval '1 month') m
CROSS JOIN categories cat
LEFT JOIN orders o
ON date_trunc('month', o.order_date) = m AND o.status = 'completed'
LEFT JOIN order_items oi ON oi.order_id = o.order_id
LEFT JOIN products p ON p.product_id = oi.product_id
AND p.category_id = cat.category_id
GROUP BY 1, 2
ORDER BY 1, 2;
Rule: a filter on the outer side of a LEFT JOIN belongs in ON, not WHERE. In WHERE it silently turns the join into an INNER JOIN.
2. Self JOIN
-- Employee with manager name; the CEO has no manager, so LEFT
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;
-- Employees earning more than their manager
SELECT e.name, e.salary, m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary;
-- Unique pairs: use < to avoid (a,b) and (b,a) duplicates and self-pairs
SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.dept_id = b.dept_id AND a.emp_id < b.emp_id;
3. Multiple JOINs without Wrong Results
The real danger is not syntax; it is fan-out. Joining two one-to-many tables to the same parent multiplies rows and inflates every SUM.
-- Safe chain (each hop is many-to-one going down): 5 tables
SELECT c.country, cat.name AS category,
sum(oi.qty * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id
WHERE o.status = 'completed'
GROUP BY c.country, cat.name;
-- WRONG: items AND payments both hang off orders. Order 7 has 2 items x 2
-- payments = 4 rows, so item totals are double counted.
SELECT o.order_id, sum(oi.qty * oi.unit_price) AS billed, sum(p.amount) AS paid
FROM orders o
JOIN order_items oi USING (order_id)
LEFT JOIN payments p USING (order_id)
GROUP BY o.order_id;
-- RIGHT: aggregate each branch to one row per order BEFORE joining
WITH billed AS (
SELECT order_id, sum(qty * unit_price) AS billed
FROM order_items GROUP BY order_id
), paid AS (
SELECT order_id, sum(amount) AS paid
FROM payments GROUP BY order_id
)
SELECT o.order_id, b.billed, coalesce(p.paid, 0) AS paid,
b.billed - coalesce(p.paid, 0) AS outstanding
FROM orders o
JOIN billed b USING (order_id)
LEFT JOIN paid p USING (order_id);
Before adding any join, ask: does this change the grain of my result? Sanity check with count(*) before and after.
4. Subqueries: Scalar, Correlated, Nested
-- Scalar: returns exactly one value
SELECT name, price, price - (SELECT avg(price) FROM products) AS vs_avg
FROM products;
-- Correlated: re-evaluated per outer row
SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (SELECT avg(salary) FROM employees WHERE dept_id = e.dept_id);
-- Nested: customers who ever bought a Software product
SELECT name FROM customers
WHERE customer_id IN (
SELECT o.customer_id FROM orders o
WHERE o.order_id IN (
SELECT oi.order_id FROM order_items oi
JOIN products p USING (product_id)
JOIN categories c USING (category_id)
WHERE c.name = 'Software'));
Correlated subqueries are often rewritable as window functions or joins, and the planner decorrelates many of them. Check EXPLAIN instead of assuming.
5. CTEs: WITH, Multiple CTEs
WITH completed AS (
SELECT o.order_id, o.customer_id, o.order_date
FROM orders o WHERE o.status = 'completed'
), totals AS (
SELECT c.customer_id, c.order_date,
sum(oi.qty * oi.unit_price) AS order_total
FROM completed c JOIN order_items oi USING (order_id)
GROUP BY c.customer_id, c.order_date, c.order_id
)
SELECT cu.name, count(*) AS orders, sum(t.order_total) AS lifetime_value
FROM totals t JOIN customers cu USING (customer_id)
GROUP BY cu.name
ORDER BY lifetime_value DESC;
Since PG12, non-recursive CTEs referenced once are inlined. Force the old behaviour with AS MATERIALIZED (optimisation fence, or when the CTE is expensive and reused) and AS NOT MATERIALIZED to force inlining.
6. Recursive CTE
-- Org chart with depth and breadcrumb path
WITH RECURSIVE org AS (
SELECT emp_id, name, manager_id, 0 AS depth, name::text AS path
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, e.manager_id, o.depth + 1, o.path || ' > ' || e.name
FROM employees e
JOIN org o ON e.manager_id = o.emp_id
)
SELECT repeat(' ', depth) || name AS tree, path
FROM org
ORDER BY path;
-- Same pattern on customer referrals; CYCLE (PG14+) guards bad data
WITH RECURSIVE chain AS (
SELECT customer_id, name, referred_by, 1 AS hops
FROM customers WHERE customer_id = 4
UNION ALL
SELECT c.customer_id, c.name, c.referred_by, ch.hops + 1
FROM customers c JOIN chain ch ON c.customer_id = ch.referred_by
) CYCLE customer_id SET is_cycle USING visited
SELECT * FROM chain WHERE NOT is_cycle;
Structure: anchor, UNION ALL, recursive term. Always have a termination guarantee (depth limit or CYCLE).
7. CASE, COALESCE, NULLIF, CAST
SELECT
c.name,
-- CASE: classification
CASE
WHEN sum(oi.qty * oi.unit_price) >= 500 THEN 'gold'
WHEN sum(oi.qty * oi.unit_price) >= 200 THEN 'silver'
ELSE 'bronze'
END AS tier,
-- COALESCE: first non-NULL
coalesce(c.email, 'no-email') AS email,
-- NULLIF: turns 0 into NULL so division returns NULL, not an error
round(sum(oi.qty * oi.unit_price)
/ nullif(count(DISTINCT o.order_id), 0), 2) AS avg_order_value,
-- CAST / :: operator
to_char(min(o.order_date)::timestamp, 'YYYY-MM') AS first_order_month,
CAST(sum(oi.qty) AS numeric) / nullif(count(*), 0) AS items_per_line
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'completed'
LEFT JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.customer_id, c.name, c.email;
Notes: CONVERT is SQL Server. In PostgreSQL use CAST(x AS type) or x::type. Integer division truncates, so cast one side to numeric first.
8. DISTINCT, UNION, UNION ALL
-- Distinct pairs
SELECT DISTINCT customer_id, status FROM orders;
-- PostgreSQL-only: DISTINCT ON keeps the first row per group
SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date
FROM orders
ORDER BY customer_id, order_date DESC; -- latest order per customer
-- UNION ALL: concatenate, no dedup (fast). UNION: sort/hash dedup (slower).
SELECT 'customer' AS kind, name, email FROM customers
UNION ALL
SELECT 'employee', name, NULL FROM employees;
-- Set operations that are easy to forget
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders; -- customers with no orders
Default to UNION ALL. Use UNION only when you need dedup and have measured the cost.
9. EXISTS, NOT EXISTS, ANY, ALL
-- EXISTS: semi-join, stops at first match, NULL-safe
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id AND o.status = 'completed');
-- NOT EXISTS: anti-join (customers who never ordered)
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
-- ANY: true if the comparison holds for at least one value
SELECT name FROM products WHERE price = ANY (ARRAY[49, 99, 150]);
SELECT name FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 2);
-- ALL: true only if it holds for every value
SELECT name FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2);
NULL trap: x NOT IN (subquery) returns no rows if the subquery yields even one NULL. Prefer NOT EXISTS. <> ALL has the same trap.
10. FILTER: Conditional Aggregation
SELECT
c.name,
count(*) AS total_orders,
count(*) FILTER (WHERE o.status = 'completed') AS completed,
count(*) FILTER (WHERE o.status = 'cancelled') AS cancelled,
sum(oi.qty * oi.unit_price) FILTER (WHERE o.status = 'completed') AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.name;
FILTER replaces the older sum(CASE WHEN ... THEN x END) and also works on window aggregates. It is cleaner, and it reads as intent.
11. LATERAL JOIN
A correlated subquery in the FROM clause: it can reference earlier tables, return multiple rows and columns, and use LIMIT.
-- Top 2 most expensive items per order (top-N-per-group)
SELECT o.order_id, top.product, top.line_total
FROM orders o
CROSS JOIN LATERAL (
SELECT p.name AS product, oi.qty * oi.unit_price AS line_total
FROM order_items oi JOIN products p USING (product_id)
WHERE oi.order_id = o.order_id
ORDER BY line_total DESC
LIMIT 2
) top;
-- LEFT JOIN LATERAL keeps parents with no children: last payment per order
SELECT o.order_id, lp.amount, lp.paid_at
FROM orders o
LEFT JOIN LATERAL (
SELECT amount, paid_at FROM payments p
WHERE p.order_id = o.order_id
ORDER BY paid_at DESC LIMIT 1
) lp ON true; -- order 6 appears with NULLs
With an index on (order_id, paid_at DESC), LATERAL top-N beats row_number() over the full table.
Cheat Sheet
| Need | Reach for |
|---|---|
| Keep unmatched rows | LEFT JOIN, with filters in ON |
| Two child tables on one parent | Pre-aggregate in CTEs |
| Existence or absence | EXISTS / NOT EXISTS, never NOT IN on nullable columns |
| Hierarchies | WITH RECURSIVE + CYCLE |
| Divide safely | x / nullif(y, 0) |
| Pivot-style counts | FILTER |
| Top-N per group | LATERAL + LIMIT, or DISTINCT ON for N=1 |
| Combine result sets | UNION ALL by default |
Comments
Join the discussionNo comments yet. Be the first to comment.
