Course contentsShow
← Computer Science
Lesson 1602 of 2,87236. Database FundamentalsFree lesson

Scalar Subqueries in SELECT

Using subqueries that return a single value in the SELECT clause to compute derived columns.

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.