Using NOT IN function

Question

SELECT products.name from products WHERE products.id 
NOT IN ( SELECT DISTINCT(products.id) FROM timer_completed
INNER JOIN com_jobcard ON com_jobcard.id = timer_completed.job_card_id
INNER JOIN products ON products.id = com_jobcard.product_id)

In the above query SELECT DISTINCT(products.id) FROM timer_completed will return multiple values but I need the products which are not in that result set, so I have used NOT IN function but it returns error. Could you suggest me a better way to achieve this.

what error does it return? – Mike Dinescu Nov 21 '15 at 05:45 — Mike Dinescu, Nov 21 '15 at 05:45

score 0 · Answer 1 · edited May 23 '17 at 10:27

I suspect your sub-query returns some NULL values which will make the NOT IN to fail(will not throw error). Use NOT EXISTS to do this

SELECT products.NAME 
FROM   products p 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   timer_completed 
                          INNER JOIN com_jobcard 
                                  ON com_jobcard.id = timer_completed.job_card_id 
                   WHERE  p.id = com_jobcard.id)

score 0 · Answer 2 · answered Nov 21 '15 at 06:10

USE NOT EXISTS instead of NOT IN. I hope this will work..

NOT IN operation would fail if the result set being probed returns a NULL. In such a case, the results of a NOT IN query is 0 rows while a NOT EXISTS query would still show the rows present in the one table but not in the other table.

Subin Chalil · Answer 3 · 2015-11-21T07:19:02.690

The best way to search for missing values in MySQL is using a LEFT JOIN / IS NULL or NOT IN rather than NOT EXISTS.

You can make use of LEFT JOIN here.

SELECT P.name
FROM 
    products P
LEFT JOIN 
     (SELECT DISTINCT(products.id) FROM timer_completed
      INNER JOIN com_jobcard ON com_jobcard.id = timer_completed.job_card_id
      INNER JOIN products ON products.id = com_jobcard.product_id) T
     ON P.product_id <> T.product_id
WHERE T.product_id IS NULL

Hope this helps.

Using NOT IN function

3 Answers3