How to include count subquery inside main MySQL query

Other Questions MySQL
1

I’m trying to figure out how to write a MySQL query that gets data from one table and also counts related records from another table at the same time.

Here’s what I want to do:

SELECT 
  writers.pen_name,
  (SELECT COUNT(*) FROM novels WHERE novels.writer_id = writers.id) as book_count
FROM 
  writers;

Basically I need to get a list of all writers and show how many novels each one has published. I want both pieces of information in a single result set.

I think there might be a better way to do this with joins or something but I’m not sure about the syntax. Any help would be great!

2

Your correlated subquery works fine, no issues there. But since you asked about joins - LEFT JOIN with GROUP BY is definitely faster for bigger datasets:

SELECT 
  w.pen_name,
  COUNT(n.id) as book_count
FROM 
  writers w
LEFT JOIN novels n ON n.writer_id = w.id
GROUP BY w.id, w.pen_name;

Use LEFT JOIN (not INNER) so writers with zero books still show up. This scales way better with thousands of records - you’re not running a separate subquery for every writer. Don’t forget the GROUP BY or the count won’t work right.

3

your subquery looks good! no need to stress about joins if the dataset isn’t huge. i find subqueries easier to read too. just check that writer_id is indexed, and you’re good to go!

4

Your approach works great and beats joins in some cases. I’ve used correlated subqueries like this for years when I need exact counts without duplicate headaches. Just watch out - adding more subqueries for different counts tanks performance fast. Try COUNT(novels.id) instead of COUNT(*) for cleaner code, though both do the same thing here. You’ll get exactly what you want - writers with their novel counts, including zeros for writers with no novels. Performance is solid on moderate tables if you index the foreign key properly.

5

Both work, but here’s the performance breakdown. Your correlated subquery actually wins when you’re filtering the writers table heavily. If you’re only checking a few writers, the subquery runs way fewer times than doing a full LEFT JOIN across thousands of records. But if you need all writers (or most), the JOIN crushes it. I’ve seen the correlated subquery run 3x faster on filtered results but 10x slower on full scans. Run EXPLAIN on both with your actual data - see what MySQL’s optimizer picks. And definitely add an index on novels.writer_id if you haven’t. Huge difference for both approaches.

6

This topic was automatically closed 4 days after the last reply. New replies are no longer allowed.