select data from table which dont have row in another table I have 2 tables. First "product" (id). Second - "product_has_option_value" (product_id, option_value_id).

How can I get product ids which doesn't have some option_value_id (for example 10)? "product_has_option_value" has many to many relationships.

Ok. I think I found the solution:

select p.id 
from product p 
where p.id not in 
  (select distinct(product_id)
   from product_has_option_value 
   where option_value_id=543)
What you want is called "anti-join" (or "anti-semijoin"). There are 3 main ways to do this kind of query in MySQL (and some more in other DBMS, that have implemented EXCEPT operator):


NOT IN subquery (no need for DISTINCT here but you may need to add the check AND pov.product_id IS NOT NULL if that column is nullable):

select p.id 
from product as p 
where p.id not in 
  ( select pov.product_id
    from product_has_option_value as pov 
    where pov.option_value_id = 543
      and pov.product_id is not null
  ) ;

LEFT JOIN with IS NULL check:

select p.id 
from product as p 
    left join  product_has_option_value as pov 
      on  pov.option_value_id = 543
      and pov.product_id = p.id
where pov.product_id is null ;

NOT EXISTS correlated subquery:

select p.id 
from product as p 
where not exists 
  ( select 1
    from product_has_option_value as pov 
    where pov.option_value_id = 543
      and pov.product_id = p.id
  ) ;