PostgreSQL Zero to Advanced #7: Subqueries, CTEs & Views

Some questions cannot be answered in a single step. "Which customers spent more than the average customer?" requires you to calculate the average first and then compare against it. In this part we learn three tools for multi-step queries: subqueries, CTEs (Common Table Expressions, the WITH clause) including recursive CTEs, and views to save queries for reuse. We keep using the course platform with the foreign keys and instructors we added in Part 6.

Scalar subqueries

A subquery is a SELECT inside another statement. A scalar subquery returns exactly one value, so you can use it anywhere a single value is allowed. First, the average course price:

SELECT ROUND(AVG(price)) AS avg_price FROM courses;

Output:

 avg_price 
-----------
    255625
(1 row)

Now let the database compute the average for us inside the WHERE clause:

SELECT title, price
FROM courses
WHERE price > (SELECT AVG(price) FROM courses)
ORDER BY price DESC;

Output:

             title             | price  
-------------------------------+--------
 Next.js Full Stack            | 400000
 PostgreSQL Performance Tuning | 350000
 Python for Data Analysis      | 300000
 Advanced SQL Queries          | 275000
(4 rows)

The inner query runs once, returns 255625, and the outer query filters against it. If the prices change tomorrow, the query still works without editing a hard-coded number.

Subqueries with IN and EXISTS

A subquery that returns a list of values works with IN. Subqueries can be nested. Who has ordered any database course?

SELECT name
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE course_id IN (SELECT id FROM courses WHERE category = 'database')
)
ORDER BY name;

Output:

     name     
--------------
 Andi Wijaya
 Budi Santoso
 Rina Putri
(3 rows)

EXISTS checks whether the subquery returns at least one row. It is the natural way to ask "has any related row?". With NOT EXISTS, we find users without orders, the same answer as the anti-join from Part 6:

SELECT u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Output:

    name    
------------
 Agus Salim
(1 row)

The subquery refers to u.id from the outer query. That makes it a correlated subquery: logically it is evaluated for each user. PostgreSQL is smart enough to turn NOT EXISTS into an efficient anti-join behind the scenes.

Correlated subqueries in SELECT

You can also put a correlated subquery in the select list to compute a per-row value:

SELECT c.title,
       (SELECT COUNT(*)
        FROM orders o
        WHERE o.course_id = c.id
          AND o.status = 'paid') AS paid_orders
FROM courses c
ORDER BY paid_orders DESC, c.title;

Output:

              title               | paid_orders 
----------------------------------+-------------
 SQL Fundamentals                 |           3
 JavaScript Basics                |           2
 Next.js Full Stack               |           2
 Advanced SQL Queries             |           1
 PostgreSQL Performance Tuning    |           1
 Python for Data Analysis         |           1
 React for Beginners              |           1
 Docker Essentials for Developers |           0
(8 rows)

This is easy to read, but on large tables a LEFT JOIN with GROUP BY is usually faster, because the subquery runs once per course.

Subqueries in FROM (derived tables)

A subquery in FROM acts like a temporary table. It must have an alias. Average spending per customer, grouped by city:

SELECT city,
       COUNT(*)         AS buyers,
       ROUND(AVG(spent)) AS avg_spent
FROM (
    SELECT u.id, u.city, SUM(o.amount) AS spent
    FROM users u
    JOIN orders o ON o.user_id = u.id
    WHERE o.status = 'paid'
    GROUP BY u.id, u.city
) AS per_user
GROUP BY city
ORDER BY avg_spent DESC;

Output:

   city   | buyers | avg_spent 
----------+--------+-----------
 Jakarta  |      3 |    565000
 Medan    |      1 |    300000
 Bandung  |      2 |    260000
 Surabaya |      1 |    150000
(4 rows)

Two levels of aggregation: first sum per user (inner query), then average per city (outer query). Agus is not here because he has no paid orders.

CTEs: the WITH clause

Nested subqueries get hard to read quickly. A CTE lets you name each step and write it top-down, like variables in a program. Here are the customers who spent more than the average customer:

WITH user_spending AS (
    SELECT u.name, SUM(o.amount) AS total_spent
    FROM users u
    JOIN orders o ON o.user_id = u.id
    WHERE o.status = 'paid'
    GROUP BY u.id, u.name
)
SELECT name, total_spent
FROM user_spending
WHERE total_spent > (SELECT AVG(total_spent) FROM user_spending)
ORDER BY total_spent DESC;

Output:

     name     | total_spent 
--------------+-------------
 Budi Santoso |      775000
 Dewi Lestari |      520000
 Maya Sari    |      400000
(3 rows)

The CTE user_spending is used twice: once as the main source and once to compute the average (2,665,000 / 7 = 380,714). With nested subqueries you would have to write that logic twice.

Multiple CTEs

Separate CTEs with commas; each one can use the ones defined before it. Revenue share per category:

WITH paid_orders AS (
    SELECT o.course_id, o.amount
    FROM orders o
    WHERE o.status = 'paid'
),
category_revenue AS (
    SELECT c.category, SUM(p.amount) AS revenue
    FROM paid_orders p
    JOIN courses c ON c.id = p.course_id
    GROUP BY c.category
)
SELECT category,
       revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct
FROM category_revenue
ORDER BY revenue DESC;

Output:

 category | revenue | pct  
----------+---------+------
 web      | 1290000 | 48.4
 database | 1075000 | 40.3
 data     |  300000 | 11.3
(3 rows)

SUM(revenue) OVER () is a window function that computes the grand total next to each row. We cover window functions properly in Part 10.

Recursive CTEs

WITH RECURSIVE lets a CTE refer to itself. It has two parts joined by UNION ALL: an anchor query that runs once, and a recursive query that runs repeatedly on the previous result until it returns no rows. The simplest example counts from 1 to 5:

WITH RECURSIVE counter AS (
    SELECT 1 AS n           -- anchor
    UNION ALL
    SELECT n + 1            -- recursive step
    FROM counter
    WHERE n < 5             -- stop condition
)
SELECT n FROM counter;

Output:

 n 
---
 1
 2
 3
 4
 5
(5 rows)

Now a real use case. In Part 6 our self join could only show one level of prerequisites. A recursive CTE follows the whole chain. What is the full learning path to "Next.js Full Stack"?

WITH RECURSIVE learning_path AS (
    SELECT id, title, prerequisite_id, 1 AS depth
    FROM courses
    WHERE title = 'Next.js Full Stack'
    UNION ALL
    SELECT c.id, c.title, c.prerequisite_id, lp.depth + 1
    FROM courses c
    JOIN learning_path lp ON c.id = lp.prerequisite_id
)
SELECT depth, title
FROM learning_path
ORDER BY depth DESC;

Output:

 depth |        title        
-------+---------------------
     3 | JavaScript Basics
     2 | React for Beginners
     1 | Next.js Full Stack
(3 rows)

The anchor finds Next.js (depth 1). The recursive step joins each found course to its prerequisite: React (depth 2), then JavaScript Basics (depth 3). JavaScript has no prerequisite, so the recursion stops. The same technique handles category trees, org charts and comment threads.

Views and materialized views

When you keep writing the same three-table JOIN, save it as a view. A view is a stored query that behaves like a table:

CREATE VIEW order_details AS
SELECT o.id     AS order_id,
       u.name   AS customer,
       u.city,
       c.title  AS course,
       c.category,
       o.amount,
       o.status,
       o.ordered_at
FROM orders o
JOIN users   u ON u.id = o.user_id
JOIN courses c ON c.id = o.course_id;

Output:

CREATE VIEW

Now anyone can query clean, readable order data without repeating the joins:

SELECT order_id, customer, course, amount
FROM order_details
WHERE status = 'paid'
  AND category = 'web'
ORDER BY order_id;

Output:

 order_id |   customer   |       course        | amount 
----------+--------------+---------------------+--------
        3 | Siti Rahma   | JavaScript Basics   | 120000
        4 | Siti Rahma   | React for Beginners | 250000
        7 | Dewi Lestari | JavaScript Basics   | 120000
        9 | Dewi Lestari | Next.js Full Stack  | 400000
       13 | Maya Sari    | Next.js Full Stack  | 400000
(5 rows)

A view stores no data; it runs the underlying query every time. For heavy reports you can use a materialized view, which stores the result physically:

CREATE MATERIALIZED VIEW category_revenue AS
SELECT category,
       COUNT(*)    AS paid_orders,
       SUM(amount) AS revenue
FROM order_details
WHERE status = 'paid'
GROUP BY category;

SELECT * FROM category_revenue ORDER BY revenue DESC;

Output:

SELECT 3
 category | paid_orders | revenue 
----------+-------------+---------
 web      |           5 | 1290000
 database |           5 | 1075000
 data     |           1 |  300000
(3 rows)

A materialized view is a snapshot. It does not update automatically when new orders arrive; you refresh it on a schedule (for example with a cron job):

REFRESH MATERIALIZED VIEW category_revenue;

Output:

REFRESH MATERIALIZED VIEW

Use REFRESH MATERIALIZED VIEW CONCURRENTLY (which requires a unique index on the view) to avoid blocking readers during the refresh.

Common mistakes

• NOT IN with a subquery that can return NULL. This is a famous trap. Which courses are not a prerequisite of any other course?

SELECT title
FROM courses
WHERE id NOT IN (SELECT prerequisite_id FROM courses);

Output:

 title 
-------
(0 rows)

Zero rows! Some prerequisite_id values are NULL, and x NOT IN (1, 3, NULL) is never true, only false or unknown. Use NOT EXISTS, which handles NULL correctly:

SELECT c.title
FROM courses c
WHERE NOT EXISTS (
    SELECT 1 FROM courses x WHERE x.prerequisite_id = c.id
)
ORDER BY c.id;

Output:

              title               
----------------------------------
 PostgreSQL Performance Tuning
 Next.js Full Stack
 Python for Data Analysis
 Docker Essentials for Developers
(4 rows)

• A scalar subquery that returns more than one row. PostgreSQL raises "more than one row returned by a subquery used as an expression". Make sure it is aggregated or filtered by a unique key.
• Recursive CTEs without a stop condition. Cyclic data (A requires B, B requires A) loops forever. Add a depth limit or use the CYCLE clause.
• Forgetting to refresh materialized views. Stale reports are worse than slow ones.

Exercise

1. Using a scalar subquery, find orders whose amount is higher than the average paid order.
2. Write a CTE that computes revenue per instructor (via courses), then select instructors above Rp1,200,000. (Only Lina qualifies.)
3. Write a recursive CTE that shows the full learning path to "PostgreSQL Performance Tuning".
4. Create a view active_courses that shows only published courses with their instructor's name.

Summary

You learned scalar, list, correlated and derived-table subqueries; EXISTS and NOT EXISTS; readable multi-step logic with CTEs; hierarchical queries with WITH RECURSIVE; and how to save queries as views and materialized views. You also learned to avoid the NOT IN plus NULL trap.

This wraps up the intermediate section. In Part 8 we enter the advanced section with performance: reading EXPLAIN ANALYZE and choosing the right indexes for a table with half a million rows.