how to get orphans from a join table in MySQL

Question

Imagine 2 tables, the first one is a list of products (products), the second one a join table between products and another table (categories), called products-categories

products:

id   |  name
------------
1       Lorem 
2       Ipsum 
3       Dolor 
4       Sit 

products-categories

product_id  | categories_id
---------------------------
1             3
1             6
4             1
2             2

How to get the orphan elements, I mean the elements in no category, so in this case: 3, in a efficient way (+30k records) using MyISAM?

This is somehow like showing all rows that are not joinable, but this syntax seams weird to me...

Answer 1

select * from products p 
left join product_categories pc on p.id=pc.product_id 
where pc.product_id is null

will return all products in table products that are not found in product_Category. LEft join and where is very fast. 30k records is also very little, so don't worry there.