Is there a better way to run this query?

I'm trying to find values that aren't being used in one table, but are located in another table.

My below query works, but I don't know if it is the optimal solution. I would also like to use only one query.

select distinct(article_cats.category_id) from articles, article_cats
where article_cats.category_id NOT IN (select category_id from articles)

Code (markup):

sample tables:

articles:
article_id
title
category_id

article_cats:
category_id
category_name

sample data:

articles:
article_id category_id
1 2
2 3
3 1
4 1

article_cats:

category_id category_name
1 A
2 B
3 C
4 E

sample query results would just return the value of 4

 

i think its the optimal.. try to look for its loading time,
based on my experience it more slower if you use left join..

recent experience
table with more than 100k rows
with left join 10 secs - 15 secs, then i revised it like your query its just .5 - 3 secs

 

why not do that query into this:

select distinct(category_id) from article_cats
where category_id NOT IN (select category_id from articles)

Code (markup):

 

barto:
thanks for the information. It's funny, I have been programming and working with mysql for a long time now and just occurred to me that I was able to do this all in one query. I always think of passing IN, a simple list value and never it's own seperate query.

hireme:
That is a good question. Since, I'm no longer connecting to two tables with where and and statements, I don't actually need to reference the articles table.

Thanks everyone.

 

try this

"select articles.category_id from articles inner join articles.category_id = article_cats.category_id"

use inner join.. if table a does not match with table b it wont return a record.