SQL Joins vs Subqueries: One Reporting Question, Three Ways to Answer It

Someone hands you a report to build. "Show each course with the number of students enrolled and the total fees collected." Sounds easy. Then you open the editor, stare at it, and freeze: JOIN or subquery? Both will get you the right numbers. So does the choice even matter?

It does, and here's the honest reason why: speed, and whether you can read your own query a week later without groaning. In this post we'll solve the exact same report three different ways, and then show you how to stop guessing which one is better and actually ask your database.

The setup

Three small tables. Nothing more, so keep them in your head:

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name       VARCHAR(100),
  course_id  INT
);

CREATE TABLE courses (
  course_id   INT PRIMARY KEY,
  course_name VARCHAR(100)
);

CREATE TABLE fee_payments (
  payment_id INT PRIMARY KEY,
  student_id INT,
  amount     DECIMAL(10,2),
  paid_on    DATE
);

Students belong to a course. Every fee payment is tied to a student. The report needs three things per course: the course name, how many students are enrolled, and the total money collected. That's it.

Way 1: the classic JOIN

Join all three tables, group by course, aggregate. Like this:

SELECT c.course_name,
       COUNT(DISTINCT s.student_id) AS students_enrolled,
       COALESCE(SUM(p.amount), 0)   AS total_collected
FROM courses c
LEFT JOIN students s     ON s.course_id = c.course_id
LEFT JOIN fee_payments p ON p.student_id = s.student_id
GROUP BY c.course_id, c.course_name;

If GROUP BY feels fuzzy to you, here's the short version: it takes every row with the same value and squashes them into one row, computing an aggregate over each group. One course, one output row. That's the whole trick.

Now spot the trap hiding in that query, because it's a good one. Once you join students to their payments, a student who paid in five instalments appears five times in the joined result. If you write COUNT(s.student_id)that student gets counted five times and your enrolment number is quietly wrong. That's exactly why the query uses COUNT(DISTINCT ...).

And this trap is nasty because of how it fails. No error message. No crash. The report just looks plausible and is wrong. Our trainers see students get bitten by this constantly, and honestly it's the worst kind of bug, because nobody notices until someone checks the numbers against the office register.

The LEFT JOINs matter too. A course with zero students should still appear in the report with 0 and 0. Swap those for INNER JOIN and it silently disappears, and the dean asks why the new welding course isn't on the sheet.

Way 2: subqueries in the FROM clause

Instead of one big join, compute each aggregate separately and glue the results back on:

SELECT c.course_name,
       COALESCE(e.students_enrolled, 0) AS students_enrolled,
       COALESCE(p.total_collected, 0)   AS total_collected
FROM courses c
LEFT JOIN (
  SELECT course_id, COUNT(*) AS students_enrolled
  FROM students
  GROUP BY course_id
) e ON e.course_id = c.course_id
LEFT JOIN (
  SELECT s.course_id, SUM(pay.amount) AS total_collected
  FROM fee_payments pay
  JOIN students s ON s.student_id = pay.student_id
  GROUP BY s.course_id
) p ON p.course_id = c.course_id;

Each subquery sitting in FROM (the fancy name is derived table) produces exactly one row per course, already aggregated. Then a plain LEFT JOIN attaches each one to the course list. No fan-out anywhere, so no need for COUNT(DISTINCT), and payments physically can't inflate your enrolment count.

Is it more readable? People argue about this one. Some find it clearer because each block does one job and you can test each block on its own. Others find the nesting visually heavy and miss the one-query feel. The database engine doesn't care what you prefer, but the teammate who inherits your code might.

One side note worth knowing. This pattern got much nicer when databases added CTEs. A CTE (a WITH query) lets you name these subqueries before the main query instead of nesting them inline, so you write WITH courses_enrolled AS (...) and then reference it by name. MySQL only added support for this in version 8.0. So if your friend's WITH query refuses to run on your laptop but runs fine on theirs, check your MySQL version before you question your whole education. Old college lab machines running 5.7 are still very real.

Way 3: the correlated subquery

The third version looks almost innocent:

SELECT c.course_name,
       (SELECT COUNT(*) FROM students s
        WHERE s.course_id = c.course_id) AS students_enrolled,
       (SELECT COALESCE(SUM(pay.amount), 0)
        FROM fee_payments pay
        JOIN students s ON s.student_id = pay.student_id
        WHERE s.course_id = c.course_id) AS total_collected
FROM courses c;

Notice how each subquery references c.course_id from the outer query? That's what "correlated" means. It behaves like a little function that gets called once for every row the outer query produces. And because these values sit in the SELECT list, each subquery has to return exactly one value, a single number, or the database throws an error (in MySQL you'd see something like "Subquery returns more than 1 row").

For a report at this size it works fine, and it's genuinely the shortest to type. The problem shows up when the outer table gets big. Say your outer query produces 50,000 rows and each row fires off two subqueries that scan thousands of payment rows. That's 100,000 extra lookups. Your query goes from instant to "did it hang?" and you're sitting there wondering if the WiFi dropped.

So which one is actually faster?

Here's where we're supposed to say "joins beat subqueries" and you go memorise it. Don't. That rule is wrong often enough to be dangerous, because the real answer depends on your indexes, your table sizes, which engine you're on, and sometimes just the shape of the query.

So skip the rule and ask the database directly:

EXPLAIN ANALYZE
SELECT c.course_name, COUNT(DISTINCT s.student_id) AS students_enrolled
FROM courses c
LEFT JOIN students s ON s.course_id = c.course_id
GROUP BY c.course_id, c.course_name;

EXPLAIN is a statement that shows you the query plan: the steps the engine intends to take to answer you. In PostgreSQL, EXPLAIN pairs with ANALYZE to actually run the query and report real timings for every step, which is about as close to an honest answer as you'll get. MySQL has its own EXPLAIN that shows the plan too, so you can put both versions of your report on the same data and compare.

Run your JOIN version and your correlated version through it. On a table with 200 courses you'll probably see almost no difference, and either choice is fine. On half a million rows you'll watch the correlated version light up with a cost that finally explains why the report takes 40 seconds. That's the actual lesson: the answer is measurable, not memorisable.

When a subquery is clearly the better pick

Joins don't win everything. Sometimes the question itself is subquery-shaped. Take this one: "show me payments above the average payment amount." A join gets genuinely awkward here, because you're comparing each row against an aggregate of the whole table:

SELECT payment_id, amount
FROM fee_payments
WHERE amount > (SELECT AVG(amount) FROM fee_payments);

The subquery runs once, produces one number, and the WHERE clause does the rest. You could force that into a join: compute the average separately, cross join it onto every row, then filter. It's legal. But why.

There's a subtler reason to like FROM subqueries too. When each block computes one aggregate at its own grain, you sidestep the duplicated-rows mess that forced the DISTINCT in Way 1. For reports mixing counts and sums, that's a real safety win, not just a style preference.

Try this yourself

Pick two related tables you already have, even from a college project. Write the same report three ways, stick EXPLAIN in front of each one, and compare what you see. Ten minutes of this teaches you more than a week of memorising syntax rules.

If you want to go deeper on joins, subqueries and reading query plans properly, our SQL Training course at Rise Institute is a good place to do it. And the next time a report hands you a plausible-looking wrong number, you'll know exactly which mistake to go hunting for.