How would you construct this SQL query? (MySQL)

Question

Let assume we have these tables:

product

product_id
product_name

category

category_id
category_name

product_in_category

product_in_category_id
product_id
category_id

how would you get all products that are not in specific category in the product_in_category table (without duplicates).

In other words, all products that are not been assigned to category 10, for instance. ALso, if one product is in categories 1, 5 and 10, it shouldn't come up the result.

Tks.

Answer 1

Using LEFT JOIN/IS NULL:

   SELECT p.*
     FROM PRODUCT p
LEFT JOIN PRODUCT_IN_CATEGORY pic ON pic.product_id = p.product_id
                                 AND pic.category_id = 10
    WHERE pic.product_in_category_id IS NULL

Using NOT IN

SELECT p.*
  FROM PRODUCT p
 WHERE p.product_id NOT IN (SELECT pic.product_id
                              FROM PRODUCT_IN_CATEGORY pic
                             WHERE pic.category_id = 10)

Using NOT EXISTS

SELECT p.*
  FROM PRODUCT p
 WHERE NOT EXISTS (SELECT NULL
                     FROM PRODUCT_IN_CATEGORY pic
                    WHERE pic.product_id = p.product_id
                      AND pic.category_id = 10)

Which is best?

It depends on if the columns being compared are nullable (values can be NULL) or not. If they are nullable, then NOT IN/NOT EXISTS are more efficient. If the columns are not nullable, then LEFT JOIN/IS NULL is more efficient (MySQL only).

Answer 2

SELECT * FROM product_in_category WHERE category_id!=10

or am I missing something? I guess I was missing the no duplicates part, look at InSane's better answer.

Answer 3

SELECT product_id FROM product
LEFT OUTER JOIN product_in_category
ON product.product_id = product_in_category.product_id
WHERE product_in_category.product_id IS NULL
GROUP BY product_id