Subqueries in SELECT

Overview

Subqueries in `SELECT` — Calculations Inside the Result Set

A subquery in the SELECT clause is a query that runs for each row and returns a single value.

It behaves like a computed column.

This pattern is perfect for:

Counting related rows
Calculating averages or totals per row
Checking related information without joining
Adding “extra facts” to each row

Subqueries in SELECT must return exactly one value per row (these are called scalar subqueries).

The Basic Pattern


SELECT column1,
       column2,
       (SELECT value
        FROM other_table
        WHERE condition) AS new_column
FROM table_name;

The subquery runs once per row of the outer query.

Examples

Example 1: Count enrollments for each student


SELECT s.student_id,
       s.first_name,
       s.last_name,
       (SELECT COUNT(*)
        FROM enrollments e
        WHERE e.student_id = s.student_id) AS TotalEnrollments
FROM students s;

This adds a computed column showing how many courses each student is taking.

Example 2: Count students enrolled in each course


SELECT c.course_id,
       c.course_name,
       (SELECT COUNT(*)
        FROM enrollments e
        WHERE e.course_id = c.course_id) AS NumStudents
FROM courses c;

Shows course popularity without using GROUP BY.

Example 3: Count courses offered in each instructor’s department


SELECT i.first_name,
       i.last_name,
       i.department,
       (SELECT COUNT(*)
        FROM courses c
        WHERE c.department = i.department) AS NumCourses
FROM instructors i;

This adds department-level context to each instructor.

Example 4: Highest grade a student has earned


SELECT s.student_id,
       s.first_name,
       s.last_name,
       (SELECT MAX(grade)
        FROM enrollments e
        WHERE e.student_id = s.student_id) AS HighestGrade
FROM students s;

A clean way to compute per-student metrics.

Why Use Subqueries in `SELECT`?

They avoid joins when you only need a single value
They make queries more readable for per-row calculations
They support complex logic inside a single SELECT
They are perfect for dashboards and reporting columns