Subquery in SELECT
A subquery in the SELECT clause computes a value per row - called a scalar subquery. It runs once per output row of the outer query. Use it to add computed columns without a JOIN, like showing the overall average next to each row, or looking up a related value.
Scalar Subquery Rules
- Subquery in SELECT must return exactly 1 row and 1 column
- Runs once per row of the outer query (can be slow)
- Great for adding a global comparison value to each row
- If subquery returns 0 rows → NULL in that column
- Modern databases often optimize repeated scalar subqueries
Scalar Subquery Examples
-- Show each product's price vs overall average
SELECT
name,
price,
(SELECT ROUND(AVG(price), 2) FROM products) AS catalog_avg,
price - (SELECT ROUND(AVG(price), 2) FROM products) AS vs_avg
FROM products;
-- Result:
-- name | price | catalog_avg | vs_avg
-- Laptop | 999.99 | 563.32 | 436.67
-- Phone | 599.99 | 563.32 | 36.67
-- Headphones | 89.99 | 563.32 | -473.33
-- Each order's status + customer name (scalar subquery as lookup)
SELECT
o.id,
o.total_amount,
(SELECT u.name FROM users u WHERE u.id = o.user_id) AS customer_name
FROM orders o;
-- Note: a JOIN is usually more efficient for this pattern
-- Count related records for each user
SELECT
u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;Quick Quiz
Tip
Tip
Practice Subquery in SELECT in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
SQL doesn't run top-to-bottom. FROM executes first.
Common Mistake
Warning
A common mistake with Subquery in SELECT is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Subquery in SELECT from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A subquery in the SELECT clause computes a value per row - called a scalar subquery.
- Subquery in SELECT must return exactly 1 row and 1 column
- Runs once per row of the outer query (can be slow)
- Great for adding a global comparison value to each row