You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
Subqueries and CTEs let you break complex problems into manageable pieces. This lesson covers scalar subqueries, correlated subqueries, EXISTS, CTEs, recursive CTEs, and CTE materialisation behaviour in PostgreSQL.
A scalar subquery returns a single value and can be used anywhere a single value is expected:
-- Compare each order's total to the overall average
SELECT
order_id,
total,
(SELECT AVG(total) FROM orders) AS avg_total,
total - (SELECT AVG(total) FROM orders) AS diff_from_avg
FROM orders;
-- Orders above the average total
SELECT order_id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);
Tip: Scalar subqueries are executed once if they are uncorrelated (no reference to the outer query). PostgreSQL caches the result.
A correlated subquery references a column from the outer query, so it is re-evaluated for each outer row:
-- For each product, find how many orders include it
SELECT
p.id,
p.name,
(
SELECT COUNT(*)
FROM order_items oi
WHERE oi.product_id = p.id
) AS order_count
FROM products p;
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.