Scalar Subqueries in SELECT
What You'll Learn
You'll learn how to use subqueries that return a single value directly in your SELECT clause to compute derived columns alongside your main query results.
The Core Idea
A scalar subquery is a SELECT statement nested inside another query that returns exactly one value (one row, one column). When placed in the SELECT clause, it acts like a computed column that can reference the outer query's current row.
Think of it like a function call in programming: for each row the main query processes, the scalar subquery "runs" and returns one answer that becomes part of that row's output.
Plain English Example
Imagine you're selecting employee records and want to show each employee alongside the average salary of all employees:
SELECT
name,
salary,
(SELECT AVG(salary) FROM employees) AS avg_company_salary
FROM employees;
The subquery (SELECT AVG(salary) FROM employees) returns one number—the company-wide average. That same value appears in every row of your result set.
Referencing the Outer Query
Scalar subqueries become more powerful when they reference the outer query's current row:
SELECT
department_name,
(SELECT COUNT(*)
FROM employees
WHERE employees.dept_id = departments.id) AS employee_count
FROM departments;
Here, for each department, the subquery counts employees in that specific department by using departments.id from the outer query.
Key Rules
- The subquery must return exactly one value (or NULL)
- It can reference columns from the outer query's current row
- Useful for adding context or comparative data to each result row
Key Takeaway: Scalar subqueries in SELECT let you compute single-value results for each row, creating dynamic derived columns that can depend on the outer query's current row data.