Select SQL where value does not exist

Question

I have a two columns product_id and category_id. I want to select all product_id that don't have the category_id = 90

I use this query, but it returns null. What am I doing wrong?

SELECT * 
FROM  `oc_product_to_category` 
WHERE NOT EXISTS (
  SELECT * 
  FROM  `oc_product_to_category` 
  WHERE category_id = 90
)

Answer 1

You aren't correlating the inner and outer queries. If there's at least one product with category_id = 90 the inner query will return some rows, and thus the NOT EXISTS condition will always be false, and the outer query will return no rows. You need to add a condition to specify the inner query and the outer query refer to the same product:

SELECT * 
FROM  `oc_product_to_category` a
WHERE NOT EXISTS (
  SELECT * 
  FROM   `oc_product_to_category` b
  WHERE  category_id = 90 AND
         a.product_id = b.product_id -- Here!
)

Answer 2

Try This

SELECT * 
FROM  `oc_product_to_category` 
WHERE category_id <> **90**