Single Row Subquery
A single-row subquery returns exactly one row with one column (a scalar value). Use standard comparison operators (=, >, <, >=, <=, !=) with single-row subqueries. If the subquery returns more than one row, it causes an error.
Single Row Subquery Rules
- Must return exactly one row and one column
- Use with: =, !=, <, >, <=, >=
- If returns 0 rows → comparison returns NULL (no match)
- If returns >1 rows → runtime error
- Common uses: compare to aggregate (AVG, MAX, MIN) or lookup a single value
Single Row Subquery Examples
-- Orders above average order value
SELECT id, total_amount, status
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);
-- Product with the highest price
SELECT name, price FROM products
WHERE price = (SELECT MAX(price) FROM products);
-- Result: Laptop, 999.99
-- Most recent order
SELECT * FROM orders
WHERE created_at = (SELECT MAX(created_at) FROM orders);
-- Orders by the newest user
SELECT o.* FROM orders o
WHERE o.user_id = (
SELECT id FROM users ORDER BY created_at DESC LIMIT 1
);
-- Using result in UPDATE
UPDATE products
SET price = price * 0.9
WHERE price > (SELECT AVG(price) FROM products);
-- Reduce price of above-average products by 10%Quick Quiz
Tip
Tip
Practice Single Row Subquery in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
CTEs for readability. Scalar for simple values. Avoid correlated subqueries (N+1 perf issue).
Common Mistake
Warning
A common mistake with Single Row Subquery 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 Single Row Subquery 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 single-row subquery returns exactly one row with one column (a scalar value).
- Must return exactly one row and one column
- Use with: =, !=, <, >, <=, >=
- If returns 0 rows → comparison returns NULL (no match)