# Advanced PostgreSQL: A Fast-Paced Revision Guide

*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

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/847405c8-a773-4de7-997f-c161169b3895.png align="center")

```sql
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

```sql
-- 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

```sql
-- 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`.

```sql
-- 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

```sql
-- 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

```sql
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

```sql
-- 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

```sql
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

```sql
-- 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

```sql
-- 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

```sql
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`.

```sql
-- 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 |