how to select COUNT in subquery in optimized way

Question

I have a question about count in subquery in mysql database. Suppose I have a book table that has a author_id and an author table with id and name. I want to get author list with number of books for each author. So I use this query:

SELECT a.id as id, a.name as name,
(SELECT COUNT(*) FROM book b WHERE b.author_id = a.id) as count
From author a

This works without any problem. But I want to know, Is this solution is best and optimized solution for such examples? If not, Is there any better workaround for these problems?

Thank you for your attention.

You're missing a comma before the subquery. – Barmar Dec 23 '17 at 07:27 — Barmar, Dec 23 '17 at 07:27

score 1 · Accepted Answer · answered Dec 23 '17 at 08:04

You can use join and group by since you will need to aggregate data when counting the number of books written by each author.

SELECT a.id AS id, a.name AS name, count(b.id) AS `count` FROM author a
LEFT JOIN BOOK b ON b.author_id = a.id
GROUP BY a.id, a.name;

Using a left join will be better if not all the authors have books in the book table, else inner join will be optimal INNER JOIN vs LEFT JOIN performance in SQL Server

how to select COUNT in subquery in optimized way

1 Answers1