MySQL find authors without posts (one-to-many relationship)

Question

Hi guys... I've a MySQL problem:

I've two tables (posts and authors) in a one to many relationship (since each post is written by an author and an author can write multiple posts).

So here are the tables:

Authors: id:BIGINT, name:VARCHAR(255) Posts: id:BIGINT, author_id:BIGINT, body:TEXT.

How can I retrieve all the authors with no posts?

What I've been trying to do is:

SELECT * 
FROM Authors 
WHERE id NOT IN (SELECT author_id 
                 FROM Posts 
                 GROUP BY author_id);

But it's taking ages!!!

In the two tables there are 300,000 Authors and 1,000,000 Posts!

Any quicker way?

Thanks guys!

Answer 1

See this question:
MySQL - Query All users WITHOUT an appointment

The answer should be the same.

Also, make sure you have indexes on ID and Author_ID.

Answer 2

SELECT *
FROM Authors
LEFT JOIN Posts ON (Authors.id = Posts.author_id)
WHERE Posts.author_id IS NULL

credit to @john for the first correct answer :)

Answer 3

Maybe this?

SELECT * FROM Authors a
WHERE NOT EXISTS (SELECT *
                  FROM Posts p
                  WHERE p.author_id=a.id)