Using subqueries in FROM

Let’s go even deeper down the rabbit hole: let’s see how to use subqueries in the FROM clause. This is one of the most common tricks SQL devs use, because it lets you build powerful temporary tables right “on the spot” and reuse them as if they actually exist in your database.

Imagine you’re building a report that needs calculations, grouping, or filtering, but you don’t want to create extra temp tables on the server. What do you do? That’s where subqueries in FROM come to the rescue. They let you:

• Temporarily join or aggregate data before your main query runs.
• Create structured data sets “on the fly.”
• Cut down on operations, asking the database to store as little intermediate data as possible.

Subqueries in FROM act like mini-tables you can use in your main query. It’s like building with Legos: fast, flexible, and without extra overhead :)

Subquery basics in FROM

With subqueries in FROM, we use a subquery to create a temporary table (or subtable) that becomes part of the overall query. Here’s what you need to do, step by step:

1. Write your subquery inside the FROM clause in parentheses.
2. Give the subquery an alias (nickname).
3. Use that alias just like it’s a real table.

Syntax

SELECT columns
FROM (
    SELECT columns
    FROM table
    WHERE condition
) AS alias
WHERE outer_condition;

Sounds a bit scary? Let’s jump into some examples.

Example: Students and average grade

Let’s say we have two tables:

students (student data — their name and ID):

grades (student grades data):

Now the task: get a list of students and their average grade.

We can start with a simple subquery that calculates each student’s average grade, then use it in the main query.

SELECT s.student_name, g.avg_grade
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS g
JOIN students AS s ON s.student_id = g.student_id;

Result:

On-the-fly temporary tables

Subqueries in FROM are especially handy when you need more than one level of data processing. For example, if you want not just the average grade, but also the max grade for each student — all in one query.

SELECT g.student_id, g.avg_grade, g.max_grade
FROM (
    SELECT student_id, 
           AVG(grade) AS avg_grade, 
           MAX(grade) AS max_grade
    FROM grades
    GROUP BY student_id
) AS g;

Result:

Notice how this works just like a real temporary table with its own columns: avg_grade and max_grade.

When are subqueries in FROM best to use?

For aggregated data. If you want to do calculations first (like averages, sums, or maxes), then join the results with other tables.

For filtering data. When you need to filter data before joining it with the main table.

For simplifying complex queries. Breaking up tough tasks into steps helps you avoid confusion.

Example: Student report with two levels of processing

Let’s say we want to find students whose average grade is above 80. We’ll start with a subquery that calculates averages, then use it in a filter.

SELECT s.student_name, g.avg_grade
FROM students AS s
JOIN (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS g ON s.student_id = g.student_id
WHERE g.avg_grade > 80;

Result:

Usage tips and recommendations

Alias is required. Always give your subquery an alias (like AS g), otherwise PostgreSQL won’t know how to refer to this “temporary table.”

Optimization. Subqueries in FROM can be slower than table JOINs, especially if you’re filtering data inside the subquery.

Indexing. Make sure the fields you use for joining, indexes, and filters are optimized — it really affects performance.

Example of a complex query: courses and student count

Now let’s do a real-world task that’s a bit tougher. Imagine we have this table:

courses (list of courses):

And enrollments (student enrollments in courses):

Now we want to know how many students are enrolled in each course.

SELECT c.course_name, e.students_count
FROM courses AS c
JOIN (
    SELECT course_id, COUNT(student_id) AS students_count
    FROM enrollments
    GROUP BY course_id
) AS e ON c.course_id = e.course_id;

Result:

Hope you liked the lecture... but the next one will be even cooler :)