Subquery basics
A subquery is a query inside another query, used as a value, a list or a table.
In this page:
Syntax
SELECT columns
FROM table_name
WHERE column > (SELECT AVG(column) FROM other_table);Subquery basics
A scalar subquery returns one value, such as (SELECT AVG(price) FROM products). A subquery in IN returns a list, and a subquery in FROM acts as a temporary table (it needs an alias).
Subqueries can often be rewritten as joins, and the planner usually optimises both similarly.
Note:
A subquery in FROM must have an alias in PostgreSQL.
Example: Subquery basics
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER);
INSERT INTO products VALUES (1,'Pen',3),(2,'Book',12),(3,'Lamp',45),(4,'Desk',150);
SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products) ORDER BY price;
SELECT name FROM products WHERE price IN (SELECT MAX(price) FROM products);
SELECT AVG(price) AS avg_of_expensive FROM (SELECT price FROM products WHERE price > 10) AS expensive;
-- Output:
-- name | price
-- Desk | 150
-- name
-- Desk
-- avg_of_expensive
-- 69.0Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Scalar subqueries returning several rows
- Missing the alias on a FROM subquery
- Nesting deeply when a join is clearer
Chapter Summary
- Scalar, list and table subqueries
- FROM subqueries need an alias
- Often equal to joins
- A scalar subquery must return one value
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: