Scalar Subqueries
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 6 of 25.
A parenthesized SELECT can supply a single value to an outer query. Such a scalar subquery must return one column and at most one row. An aggregate without GROUP BY is a useful way to produce one value. A scalar subquery returning no rows supplies NULL; more than one row is an error.
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
SELECT id, amount FROM orders WHERE amount > (SELECT AVG(amount) FROM orders) ORDER BY id;The average amount is 28, so orders 12, 13 and 14 pass. The inner query computes the comparison value from the whole orders table. It does not need to be copied into each row by hand.
Use a scalar subquery when a condition depends on one value computed by another query.
Challenge
MediumReturn id and name for products priced above the average product price. Order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
2Queries in Stages
Scalar SubqueriesCheck ExistenceName a Query with WITHChain CTEsAbove the Customer AveragePractice on your own: PostgreSQL playground