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...
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.