Find uncommon data from two tables in mysql

Question

i am using a category , product relation to find data. I am in situation like i have a category table and product table with categoryid and product type. No suppose i have a particular type and i want to find those category which a product of a specific type does not have. Table struture are like

Category Table
____________
|Id|Category|
| 1|    X   |
_____________

Product Table
____________________________
|ID | Product|Category| Type|
| 1 | Y      | 1      | 2   |
_____________________________

I can find it by using a sub query like

SELECT * 
FROM   category 
WHERE  id NOT IN(SELECT category 
                 FROM   product 
                 WHERE  type = 2); 

Is there a way to get it by another way

I also use a JOIN like

SELECT * 
FROM   category AS c 
       JOIN products AS p 
         ON c.id <> p.category 
WHERE  p.type = 2 

Why does this not give the appropriate result.

Answer 1

Use LEFT JOIN and use IS NULL predicate:

SELECT * 
FROM   category AS c 
       LEFT JOIN products AS p 
              ON c.id = p.category 
                 AND p.type = 2 
WHERE  p.category IS NULL; 

The unmatched rows from the second table will have null values in the category field, the WHERE p.category IS NULL will filter and give you them