Subqueries in SELECT

A subquery in the SELECT clause adds a calculated value to each row, such as a count, sum, or lookup result.

It runs once for every row in the outer query and must return exactly one value (a scalar).

Syntax

Subqueries in the SELECT clause are written like this:

Subquery in SELECT

SELECT column1,
  (SELECT some_value FROM another_table WHERE ...) AS new_column
FROM main_table;

Example: Total Courses Per User

The following query returns the number of courses each user selected.

Subquery in SELECT clause

SELECT 
  u.name,
  (
    SELECT COUNT(*)
    FROM user_courses uc
    WHERE uc.user_id = u.user_id
  ) AS course_count
FROM users u;

Output:

name	course_count
Sofia	2
Ethan	1
Aisha	2
Noah	1
Liam	3

This query counts how many rows each user has in the user_courses table.

Why It's Useful

Use subqueries in SELECT when you want to:

• Add extra calculated columns
• Avoid joins for one-off metrics
• Keep reporting logic in one place

Notes

• Make sure each subquery returns just one value (or SQL will error out)
• Good for quick summaries — but joins are often more efficient for big datasets
• You can use COUNT, SUM, AVG, or even nested subqueries

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In