Using SELECT inside SELECT

Imagine you’re working as an analyst at a university (remember, we’re building a university database, right?). You’ve been asked not just to show students and their grades, but also to add a column with the max grade in their group, so it’s easy to compare results. How do you solve this? Of course, with subqueries in SELECT!

A subquery in SELECT lets you calculate values right when the main query runs. That’s awesome, because you can combine aggregate calculations, complex filters, and even another data collection in one query.

Basics of nested queries in SELECT

A subquery in SELECT literally works like it sounds: you stick the result of one SELECT inside another. This lets you calculate extra values for each row in the result.

Here’s a simple example. Let’s say we have a students table with this structure:

And a grades table:

Example 1: Adding the max grade in the group

Task: show student names, their grades, and the max grade in their group, so you can see how each student’s grades stack up against the best in the group.

SQL code:

SELECT
    s.name AS student_name,
    g.grade AS student_grade,
    (
        SELECT MAX(grade) -- this query returns just one value 
        FROM grades 
        INNER JOIN students ON grades.student_id = students.student_id
        WHERE students.group_id = s.group_id
    ) AS max_group_grade 
FROM 
    students s
INNER JOIN 
    grades g ON s.student_id = g.student_id;

What’s going on here:

1. For each student, we get their name and grade (s.name, g.grade).
2. SELECT MAX(grade) — this subquery returns the max grade inside the student’s group.
3. The subquery runs for every row of the main query and uses WHERE students.group_id = s.group_id to limit the selection to one group.

Example 2: Average grade in the group

Want to be even more helpful for analysts? Let’s add not just the max grade, but also the average grade in the group to the output.

SQL code:

SELECT
    s.name AS student_name,
    g.grade AS student_grade,
    (
        SELECT AVG(grade) 
        FROM grades 
        INNER JOIN students ON grades.student_id = students.student_id
        WHERE students.group_id = s.group_id
    ) AS avg_group_grade
FROM 
    students s
INNER JOIN 
    grades g ON s.student_id = g.student_id;

Now:

• Instead of MAX() we use AVG() to calculate the average grade in the group.
• You get “live” data analysis.

Limitations and tips

Subqueries in SELECT are powerful, but you gotta use them carefully:

1. Performance. Each subquery runs for every row of the main query. This can slow down your SQL if the tables are big. For example, if there are 1000 students, the subquery runs 1000 times!
2. Indexes. To speed up these queries, make sure to index the columns used in the subquery’s WHERE clause.
3. Readability. Try to avoid too much nesting. If your subqueries get too gnarly, think about moving them to FROM or making temp tables.

Use cases

Let’s check out a few more cool cases.

Example 3: Number of courses for each student

We’ll show a table where each student has the number of courses they’re enrolled in. The enrollments table is linked to students by student_id:

SQL code:

SELECT
    s.name AS student_name,
    (
        SELECT COUNT(*) 
        FROM enrollments
        WHERE enrollments.student_id = s.student_id
    ) AS course_count
FROM 
    students s;

Here the subquery counts the number of records in enrollments for each student.

Example 4: “Excellent student” flag for each student

Let’s show if a student is an excellent student. Let’s say the criteria for an excellent student is having all their grades at 5.

SQL code:

SELECT
    s.name AS student_name,
    (
        SELECT CASE 
            WHEN MIN(g.grade) = 5 THEN 'Excellent student'
            ELSE 'Not excellent'
        END
        FROM grades g
        WHERE g.student_id = s.student_id
    ) AS status
FROM 
    students s;

Here we use a nested CASE to assign the status “Excellent student” only to those whose all grades are 5.

Optimizing subqueries in SELECT

We already mentioned that performance can be an issue. Here are some tips to make it better:

1. Use indexes. If your subqueries filter data, make sure the columns you use have indexes.
2. Cache results. Sometimes it makes sense to move subqueries into VIEWs or temp tables.
3. Less nesting. Don’t go overboard with deep nesting if you can use a simpler approach.

Subqueries in SELECT open up a ton of possibilities for calculations and data analysis. Even though they can be resource-hungry, using them right makes SQL way more expressive and flexible. So don’t be shy—experiment and find your own ways to make your queries better!