Skip to main content
Practice

Subqueries in SELECT

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

It runs once for each 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:​

namecourse_count
Sofia2
Ethan1
Aisha2
Noah1
Liam3

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 contained in a single query

Notes​

  • Make sure each subquery returns just one value (or SQL will error out)
  • Useful for quick summaries, though joins are usually more efficient on large 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.