SQL 44 🛢️ Subqueries in SELECT and FROM Clauses
The WHERE clause is where subqueries are most common, but it is not where they are most powerful. A subquery in the SELECT list can compute a per-row value from another table. A subquery in the FROM clause can act as a temporary table, a derived table that exists only for the duration of the query. These two positions unlock patterns that WHERE subqueries cannot express: correlated lookups, pre-aggregated joins, and multi-step transformations expressed as a single statement.
This chapter covers subqueries in the SELECT and FROM clauses. You will learn how scalar subqueries in the SELECT list compute values per row, how derived tables in the FROM clause function as inline views, and how these patterns compare to joins and common table expressions. You will also see the performance implications of each and the situations where one form is clearly preferable.
By the end, you will understand how to use subqueries to structure complex queries as a sequence of logical steps, each expressed as a separate SELECT, and how to choose between a derived table, a CTE, and a join for a given problem.
Key point: A subquery in the SELECT list must be scalar—it must return exactly one value per outer row. A subquery in the FROM clause can return any number of rows and columns; it becomes a derived table that the outer query treats as if it were a real table.
Why subqueries in SELECT and FROM exist
The per-row lookup problem. Sometimes you need to compute a value for each row of a result based on data in another table, without joining. For example, for each customer, show the date of their most recent order. A join would multiply rows. A WHERE subquery cannot return a value into the result. But a scalar subquery in the SELECT list can: it runs once per outer row and returns a single value, which appears as a column in the output.
The derived table problem. Some queries require multiple stages of aggregation or transformation. You need to aggregate first, then filter or join on the aggregated result. You cannot filter on an aggregate in the same WHERE clause that computes it, because WHERE is evaluated before GROUP BY. The solution is to compute the aggregate in a subquery in the FROM clause, then filter the derived table in the outer query. This pattern turns a two-step problem into a single SQL statement.
The readability problem. A query with a derived table often reads more naturally than the equivalent query with a HAVING clause or a complex join. The derived table expresses a logical unit—”the total sales per customer”—which the outer query then uses. This separation of concerns makes the query easier to understand and modify.
The CTE alternative. Modern SQL provides common table expressions (CTEs) with the WITH clause, which serve much the same purpose as derived tables but with better readability and sometimes better performance. The relationship between derived tables and CTEs is worth understanding: a derived table is a subquery in the FROM clause, while a CTE is a named query defined before the main query. Both create a temporary result set, but the CTE can be referenced multiple times and is often easier to read.
The performance question. Subqueries in SELECT and FROM have different performance characteristics. A scalar subquery in SELECT runs once per outer row, which can be slow if the outer table is large and the subquery is not well-indexed. A derived table in FROM is typically materialized once and then scanned by the outer query, which can be efficient if the derived table is small. Understanding these characteristics helps you write queries that scale.
a. Scalar subqueries in SELECT
A scalar subquery in the SELECT list returns exactly one value for each row of the outer query. It is typically correlated—it references a column from the outer query—so it computes a different value for each row.
-- For each customer, show the date of their most recent order
SELECT
c.customer_id,
c.name,
(SELECT MAX(o.order_date)
FROM orders o
WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers c;The subquery runs once per customer row. For each customer, it finds the maximum order date from the orders table. If a customer has no orders, the subquery returns NULL, and the last_order_date column shows NULL for that customer.
The scalar subquery must return exactly one value per outer row. If it returns more than one row, the query fails with an error. If it returns no rows, the result is NULL. These behaviors are consistent with scalar subqueries in WHERE clauses.
A common use case is computing a count or sum per row:
-- For each customer, show the number of orders
SELECT
c.customer_id,
c.name,
(SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;This pattern is equivalent to a LEFT JOIN with GROUP BY, but it avoids the grouping and produces the same result. Which is better depends on the specific query and the database’s optimizer.
A subtle issue with scalar subqueries in SELECT is that they may execute once per outer row, which can be slow on large tables. An index on the correlation column—orders.customer_id in the examples above—makes the subquery efficient because the database can look up the matching rows directly.
b. Derived tables in FROM
A subquery in the FROM clause is called a derived table. It acts as a temporary table that exists only for the duration of the query. The outer query can reference it like any other table.
-- Find customers whose total order value exceeds 1000
SELECT customer_id, total_value
FROM (
SELECT customer_id, SUM(amount) AS total_value
FROM orders
GROUP BY customer_id
) AS order_totals
WHERE total_value > 1000;The subquery in the FROM clause computes the total order value per customer. The outer query filters that result to find customers whose total exceeds 1000. The AS order_totals alias is required in most databases—the derived table must have a name.
The derived table can be joined to other tables:
-- Join customers to their order totals
SELECT c.name, ot.total_value
FROM customers c
INNER JOIN (
SELECT customer_id, SUM(amount) AS total_value
FROM orders
GROUP BY customer_id
) AS ot ON ot.customer_id = c.customer_id;This pattern is useful when you need to combine aggregated data with details from the original tables. The derived table provides the aggregate; the join brings in the customer name.
Derived tables can be nested, though deeply nested subqueries become hard to read. A general rule is that more than two levels of nesting should be refactored into CTEs.
c. Derived tables versus CTEs and joins
Modern SQL provides three ways to express “compute something first, then use it”: a derived table in FROM, a common table expression with WITH, and a join with a subquery.
Derived table:
SELECT customer_id, total_value
FROM (
SELECT customer_id, SUM(amount) AS total_value
FROM orders
GROUP BY customer_id
) AS order_totals
WHERE total_value > 1000;CTE:
WITH order_totals AS (
SELECT customer_id, SUM(amount) AS total_value
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_value
FROM order_totals
WHERE total_value > 1000;Join with subquery:
SELECT o.customer_id, SUM(o.amount) AS total_value
FROM orders o
GROUP BY o.customer_id
HAVING SUM(o.amount) > 1000;All three produce the same result. The derived table and CTE are essentially equivalent in structure and performance on most databases. The CTE is more readable because the subquery is named and separated from the main query. The join with HAVING is the most compact form when the aggregate is computed from the same table being filtered.
The choice among these is primarily a matter of readability. Performance differences exist but are usually the optimizer’s concern, not the query writer’s. Modern databases—PostgreSQL, SQL Server, Oracle—often produce identical execution plans for all three forms.
One consideration is that CTEs can be referenced multiple times in the same query, while a derived table cannot. If you need the same aggregated result in multiple places, a CTE is the better choice.
Complete Example Session
-- ============================================
-- PART 1: SAMPLE DATA
-- ============================================
CREATE TABLE customers (
customer_id INT,
name VARCHAR(50)
);
CREATE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
amount DECIMAL(10,2)
);
INSERT INTO customers VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol'),
(4, 'Dave');
INSERT INTO orders VALUES
(100, 1, '2026-01-05', 250.00),
(101, 1, '2026-01-15', 400.00),
(102, 2, '2026-01-10', 150.00),
(103, 3, '2026-01-20', 1200.00);-- ============================================
-- PART 2: SCALAR SUBQUERY IN SELECT
-- ============================================
SELECT
c.customer_id,
c.name,
(SELECT MAX(o.order_date)
FROM orders o
WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c;
-- Dave has NULL because he has no orders-- ============================================
-- PART 3: COUNT SUBQUERY IN SELECT
-- ============================================
SELECT
c.customer_id,
c.name,
(SELECT COUNT(*) FROM orders o
WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;-- ============================================
-- PART 4: SUM SUBQUERY IN SELECT
-- ============================================
SELECT
c.customer_id,
c.name,
(SELECT COALESCE(SUM(o.amount), 0)
FROM orders o
WHERE o.customer_id = c.customer_id) AS total_spent
FROM customers c;
-- COALESCE turns NULL into 0 for customers with no orders-- ============================================
-- PART 5: DERIVED TABLE IN FROM
-- ============================================
SELECT customer_id, total_value
FROM (
SELECT customer_id, SUM(amount) AS total_value
FROM orders
GROUP BY customer_id
) AS order_totals
WHERE total_value > 300;
-- Result: customer 1 (650), customer 3 (1200)-- ============================================
-- PART 6: JOIN WITH DERIVED TABLE
-- ============================================
SELECT c.name, ot.total_value
FROM customers c
INNER JOIN (
SELECT customer_id, SUM(amount) AS total_value
FROM orders
GROUP BY customer_id
) AS ot ON ot.customer_id = c.customer_id
ORDER BY ot.total_value DESC;-- ============================================
-- PART 7: DERIVED TABLE WITH MULTIPLE AGGREGATES
-- ============================================
SELECT customer_id, order_count, total_value, avg_value
FROM (
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_value,
AVG(amount) AS avg_value
FROM orders
GROUP BY customer_id
) AS stats
WHERE order_count > 1;-- ============================================
-- PART 8: SAME RESULT WITH CTE
-- ============================================
WITH order_totals AS (
SELECT customer_id, SUM(amount) AS total_value
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_value
FROM order_totals
WHERE total_value > 300;-- ============================================
-- PART 9: DERIVED TABLE WITH WINDOW FUNCTION
-- ============================================
SELECT customer_id, order_id, amount, rank_in_customer
FROM (
SELECT
customer_id,
order_id,
amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank_in_customer
FROM orders
) AS ranked
WHERE rank_in_customer = 1;
-- Highest order per customer-- ============================================
-- PART 10: COMPARISON WITH HAVING
-- ============================================
SELECT customer_id, SUM(amount) AS total_value
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 300;
-- Same result as the derived table versionThe ten parts covered the essential patterns: scalar subqueries in SELECT for lookup values, count and sum subqueries with COALESCE, derived tables in FROM, joins with derived tables, multiple aggregates in a derived table, the CTE equivalent, derived tables with window functions, and the HAVING alternative.
Quick Reference
Subquery Positions
| Position | Returns | Use Case |
|---|---|---|
SELECT | One value per outer row | Per-row lookup |
FROM | Any rows and columns | Derived table |
WHERE | Any rows (for IN) or one value (for =) | Filtering |
HAVING | One value | Filtering aggregates |
Scalar Subquery Rules
| Rule | Behavior |
|---|---|
| Returns one row | Value used |
| Returns no rows | NULL |
| Returns more than one row | Error |
| Correlated | Runs per outer row |
| Uncorrelated | Runs once |
Derived Table Requirements
| Requirement | Notes |
|---|---|
| Must have alias | AS derived_name required in most databases |
| Can have columns | Any number |
| Can be joined | Referenced like a table |
| Can be nested | Limited by readability |
| Scope | Only within the query |
Derived Table vs CTE
| Aspect | Derived Table | CTE |
|---|---|---|
| Syntax | Subquery in FROM | WITH name AS (...) |
| Reference count | Once | Multiple times |
| Readability | Moderate | High |
| Recursion | No | Yes |
| Performance | Same on modern DBs | Same on modern DBs |
Best Practices
✅ Do This:
-- Use scalar subquery for per-row lookup
SELECT c.name,
(SELECT MAX(o.order_date) FROM orders o
WHERE o.customer_id = c.customer_id) AS last_order -- ✅
FROM customers c;
-- Use COALESCE for subqueries that may return NULL
SELECT c.name,
COALESCE((SELECT SUM(o.amount) FROM orders o
WHERE o.customer_id = c.customer_id), 0) AS total -- ✅
FROM customers c;
-- Use derived table for pre-aggregated joins
SELECT c.name, ot.total
FROM customers c
JOIN (SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id) AS ot
ON ot.customer_id = c.customer_id; -- ✅
-- Use CTE instead of derived table for readability
WITH totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
)
SELECT * FROM totals; -- ✅❌ Don’t Do This:
-- Scalar subquery returning multiple rows
SELECT c.name,
(SELECT o.amount FROM orders o
WHERE o.customer_id = c.customer_id) AS amount -- ❌ if multiple orders
FROM customers c;
-- Derived table without alias
SELECT * FROM (SELECT 1 AS x); -- ❌ (most DBs)
-- Deeply nested derived tables
SELECT * FROM (
SELECT * FROM (
SELECT * FROM (
SELECT * FROM orders
) AS a
) AS b
) AS c; -- ❌
-- Scalar subquery in SELECT when a join is clearer
SELECT c.name,
(SELECT COUNT(*) FROM orders o
WHERE o.customer_id = c.customer_id) AS cnt -- ⚠️ (join may be faster)
FROM customers c;Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Scalar subquery error | Returns more than one row | Use IN, ANY, or aggregate |
NULL in result | Subquery found no rows | Use COALESCE |
| Missing alias on derived table | Required by most databases | Add AS derived_name |
| Slow per-row subquery | No index on correlation column | Add index or rewrite as join |
| Hard-to-read query | Deeply nested derived tables | Refactor to CTEs |
| Duplicate column names | Derived table columns conflict | Alias columns explicitly |
Real-World Examples
1. Last Order Date per Customer
SELECT c.name,
(SELECT MAX(o.order_date) FROM orders o
WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c;2. Order Count per Customer
SELECT c.name,
(SELECT COUNT(*) FROM orders o
WHERE o.customer_id = c.customer_id) AS orders
FROM customers c;3. Total Spent per Customer
SELECT c.name,
COALESCE((SELECT SUM(o.amount) FROM orders o
WHERE o.customer_id = c.customer_id), 0) AS total
FROM customers c;4. Top Customer by Total
SELECT customer_id, total
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
) AS totals
ORDER BY total DESC
LIMIT 1;5. Average Order Value per Customer
SELECT c.name,
(SELECT AVG(o.amount) FROM orders o
WHERE o.customer_id = c.customer_id) AS avg_order
FROM customers c;6. Customers Above Average Total
SELECT customer_id, total
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id
) AS totals
WHERE total > (SELECT AVG(total) FROM (
SELECT SUM(amount) AS total FROM orders GROUP BY customer_id
) AS sub);7. Highest Order per Customer
SELECT customer_id, order_id, amount
FROM (
SELECT customer_id, order_id, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY amount DESC) AS rn
FROM orders
) AS ranked
WHERE rn = 1;8. Derived Table Joined to Products
SELECT p.name, s.total_sold
FROM products p
JOIN (SELECT product_id, SUM(quantity) AS total_sold
FROM order_items GROUP BY product_id) AS s
ON s.product_id = p.product_id;9. Employees Above Department Average
SELECT e.name, e.salary, dept_avg.avg_salary
FROM employees e
JOIN (SELECT department, AVG(salary) AS avg_salary
FROM employees GROUP BY department) AS dept_avg
ON dept_avg.department = e.department
WHERE e.salary > dept_avg.avg_salary;10. CTE with Multiple References
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS total
FROM orders GROUP BY 1
)
SELECT m1.month, m1.total, m2.total AS prev_total
FROM monthly m1
LEFT JOIN monthly m2 ON m2.month = m1.month - INTERVAL '1 month';Visual
Subquery Positions in a Query
┌─────────────────────────────────────────────────────────────┐
│ WHERE SUBQUERIES CAN APPEAR │
│ │
│ SELECT │
│ (SELECT ...) AS scalar_col, ← scalar subquery │
│ col1, col2 │
│ FROM │
│ (SELECT ...) AS derived, ← derived table │
│ table2 │
│ WHERE col IN (SELECT ...) ← WHERE subquery │
│ GROUP BY col1 │
│ HAVING SUM(col) > (SELECT ...) ← HAVING subquery │
│ ORDER BY col1; │
│ │
└─────────────────────────────────────────────────────────────┘Scalar Subquery Execution
┌─────────────────────────────────────────────────────────────┐
│ SCALAR SUBQUERY IN SELECT │
│ │
│ SELECT c.name, │
│ (SELECT MAX(order_date) FROM orders │
│ WHERE customer_id = c.customer_id) AS last_order │
│ FROM customers c; │
│ │
│ Execution: │
│ For each customer row: │
│ 1. Read c.customer_id │
│ 2. Run subquery with that id │
│ 3. Append result as last_order column │
│ │
│ Index on orders.customer_id makes this fast. │
│ │
└─────────────────────────────────────────────────────────────┘Derived Table Flow
┌─────────────────────────────────────────────────────────────┐
│ DERIVED TABLE EXECUTION │
│ │
│ SELECT customer_id, total_value │
│ FROM ( │
│ SELECT customer_id, SUM(amount) AS total_value │
│ FROM orders │
│ GROUP BY customer_id │
│ ) AS order_totals │
│ WHERE total_value > 1000; │
│ │
│ Step 1: Execute inner query │
│ ┌─────────────────────────────┐ │
│ │ customer_id │ total_value │ │
│ │ 1 │ 650.00 │ │
│ │ 2 │ 150.00 │ │
│ │ 3 │ 1200.00 │ │
│ └─────────────────────────────┘ │
│ │
│ Step 2: Filter derived table │
│ WHERE total_value > 1000 │
│ → customer_id 3 only │
│ │
└─────────────────────────────────────────────────────────────┘Derived Table vs CTE vs Join
┌─────────────────────────────────────────────────────────────┐
│ THREE WAYS TO PRE-AGGREGATE │
│ │
│ 1. DERIVED TABLE │
│ SELECT * FROM ( │
│ SELECT customer_id, SUM(amount) AS total │
│ FROM orders GROUP BY customer_id │
│ ) AS t WHERE total > 1000; │
│ │
│ 2. CTE │
│ WITH t AS ( │
│ SELECT customer_id, SUM(amount) AS total │
│ FROM orders GROUP BY customer_id │
│ ) │
│ SELECT * FROM t WHERE total > 1000; │
│ │
│ 3. JOIN WITH HAVING │
│ SELECT customer_id, SUM(amount) AS total │
│ FROM orders GROUP BY customer_id │
│ HAVING SUM(amount) > 1000; │
│ │
│ All produce the same result. │
│ CTE is most readable; HAVING is most compact. │
│ │
└─────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Scalar subquery in SELECT | Returns one value per outer row |
| Derived table in FROM | Acts as temporary table |
| Derived table alias | Required in most databases |
| Correlated scalar subquery | Runs once per outer row |
| Uncorrelated scalar subquery | Runs once |
| CTE alternative | WITH name AS (...) |
| Readability | CTE > derived table > join with HAVING |
| Performance | Similar on modern databases |
| Window function use | Derived table for ranking per group |
| Multiple aggregates | Derived table can return many columns |
Key takeaways:
- Scalar subqueries in
SELECTcompute a value per row. They are correlated by nature and run once per outer row. They must return exactly one value per row; more than one row produces an error. COALESCEhandlesNULLresults. A scalar subquery that finds no matching rows returnsNULL. UseCOALESCEto substitute a default value like0for sums and counts.- Derived tables in
FROMact as temporary tables. They can return any number of rows and columns, and the outer query can filter, join, or aggregate them like a real table. - Derived tables require an alias. Most databases require
AS nameafter the subquery in theFROMclause. Without it, the query is a syntax error. - CTEs are often more readable than derived tables. The
WITHclause names the subquery and separates it from the main query. CTEs can also be referenced multiple times, which derived tables cannot. - Performance is usually equivalent. On modern databases, derived tables, CTEs, and joins with
HAVINGoften produce the same execution plan. Choose based on readability and the specific needs of the query. - Index the correlation column. A scalar subquery in
SELECTthat correlates on a column without an index will be slow. The index allows the database to find matching rows directly. - Use derived tables for window functions. When you need to filter on a window function result, wrap the windowed query in a derived table and filter in the outer query. Window functions cannot be used directly in
WHERE.
Remember: Subqueries in SELECT and FROM are about structuring queries as a sequence of logical steps. A scalar subquery in SELECT adds a computed column to each row. A derived table in FROM creates a named intermediate result that the outer query can use. Both forms let you express complex logic without temporary tables or procedural code. The choice between a derived table, a CTE, and a join is usually a matter of readability. Write the version that most clearly expresses the logic, then test performance, then adjust if needed. Clarity first, correctness always, performance when it matters.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!