Using subqueries in SELECT

Let’s circle back to the topic of subqueries in SELECT. Especially focusing on the fact that the inner query can reference data from the outer query. Sounds simple, but it’s not always that obvious. Let’s dig a little deeper into this topic...

Subqueries in SELECT let you add extra columns with calculated values or data that depends on other records or tables. For example, you can show a list of students with their average grade, the number of courses they’re enrolled in, or the current max grade in their group. This is super handy when you want to analyze data “on the fly,” creating summary columns without prepping the data in advance.

Subquery basics in SELECT

Before we jump into examples, let’s break down the general syntax. Subqueries in SELECT look like this:

SELECT column1,
       column2,
       (SELECT aggregation_or_condition FROM another_table WHERE condition) AS new_column_name
FROM main_table;

Notice that the subquery returns a single value, which shows up in the result set as a new column. The condition can reference columns from the main_table.

Example 1: Adding a student’s average grade

Let’s start with a simple and useful query: we have a students table and a grades table where student grades are stored.

students table:

grades table:

Now we want to get a list of students with their names and average grade. We’ll use a subquery in SELECT for this:

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade) 
     FROM grades g 
     WHERE g.student_id = s.id) AS average_grade
FROM students s;

Result:

Here, the subquery (SELECT AVG(g.grade) FROM grades g WHERE g.student_id = s.id) calculates the average grade for each student. It returns one value for each row from the students table, and it’s super convenient when you don’t want to mess with JOINs or create views ahead of time.

Example 2: Counting courses for each student

Now let’s add info about how many courses each student is taking. For this, we have some extra tables:

enrollments table:

Let’s show a list of students with the number of courses they’re enrolled in:

SELECT
    s.id,
    s.name,
    (SELECT COUNT(*)
     FROM enrollments e
     WHERE e.student_id = s.id) AS course_count -- reference to students table from the outer query
FROM students s;

Result:

The subquery (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.id) counts the number of records in the enrollments table for each student.

Aggregating data in subqueries

Subqueries in SELECT are often used to calculate aggregated data. Functions like AVG, SUM, COUNT, MAX, MIN let you process data right inside other queries.

Example 3: Student’s total grade

Let’s add the total grade for each student. We’ll use a subquery that sums up all grades from the grades table:

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Result:

This subquery (SELECT SUM(g.grade) FROM grades g WHERE g.student_id = s.id) sums up each student’s grades. If a student has no grades, the result will be NULL, since SUM returns NULL when there are no values.

Limitations and tips

1. Performance. Subqueries in SELECT run separately for every row in the main table. This can cause serious slowdowns on big datasets. If you can, swap them out for a JOIN or use pre-aggregated data. For example:

SELECT
    s.id,
    s.name,
    g.total_grade
FROM students s
LEFT JOIN (
    SELECT student_id, SUM(grade) AS total_grade
    FROM grades
    GROUP BY student_id
) g ON s.id = g.student_id;

This JOIN approach is more optimal, since grouping and counting happen just once.

2. NULL issues.

If there’s no data in the subquery, the result will be NULL. This can be surprising. Example:

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

If a student has no records in grades, the total_grade result will be NULL. To replace NULL with 0, use the COALESCE function:

SELECT
    s.id,
    s.name,
    COALESCE((SELECT SUM(g.grade)
              FROM grades g
              WHERE g.student_id = s.id), 0) AS total_grade
FROM students s;

Yeah, here as the first parameter of the COALESCE function we pass

(
    SELECT SUM(g.grade)
    FROM grades g
    WHERE g.student_id = s.id
)

Optimizing subqueries in SELECT

To avoid extra calculations and boost performance:

1. Use indexes on columns that are involved in subqueries. For example, indexing student_id in the grades table will speed up filtering.
2. Swap subqueries for pre-aggregated data with a JOIN if you can.
3. Limit the amount of data processed by subqueries using filtering (WHERE).

Final example: combining subqueries

Let’s put all our knowledge together and make a query that shows the student’s name, average grade, course count, and total grade:

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS average_grade,
    (SELECT COUNT(*) 
     FROM enrollments e 
     WHERE e.student_id = s.id) AS course_count,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

This query returns a full student profile, built using the power of subqueries. We see the average and total grade, plus the number of courses each student is enrolled in. This kind of setup is an awesome way to quickly get aggregated info without making separate VIEWs or JOINs.