Proper use of SELECT WHERE NOT IN

Question

I'm building a database for a small bookshop with a PHP web interface. For this problem I want to display all records in the 'products' table (prod_core) without those that have a value in the products field 'barcode' equal to the 'description'field in the 'sales' table (inc_core).

I actually managed to get this working for the first 'sales' mutation, but whenever I add a second record to the 'sales' table, the query doesn't work any more. (except for the first record added). This is my SQL code:

SELECT prod_core.prodID, prod_core.title, prod_core.location, prod_core.genre,
       prod_core.price, prod_core.barcode
FROM   prod_core
WHERE  prod_core.barcode NOT IN (
         SELECT `description` 
         FROM inc_core
         GROUP BY prod_core.prodID)

I suppose it has something to do with SQL not looping (it stops searching the table for records that match the requirements) but I'm not sure. I would very much appreciate any help!

This looks like it shouldn't parse. "description" isn't in the group by list. — Walter Mitty, Aug 14 '12 at 22:58
@WalterMitty . . . That is a "feature" of mysql. Look up "mysql hidden columns" on google. — Gordon Linoff, Aug 14 '12 at 23:02

Michael Fredrickson · Accepted Answer · 2012-08-14T23:16:27.033

3

I would suggest a LEFT JOIN instead of a NOT IN:

SELECT
    prod_core.prodID, 
    prod_core.title, 
    prod_core.location, 
    prod_core.genre, 
    prod_core.price, 
    prod_core.barcode
FROM 
    prod_core 
    LEFT JOIN inc_core ON inc_core.`description` = prod_core.barcode
WHERE
    inc_core.`description` IS NULL

Also, since none of the columns in your SELECT statement are part of an aggregate function, I removed the GROUP BY ~~and replaced it with DISTINCT... however, I don't see why this table should include duplicates.~~

EDIT: I misread your query and though that the group by was on the outer query, and not on the sub-query... +1 to the other answers that caught this.

edited Aug 14 '12 at 23:16

answered Aug 14 '12 at 22:57

Michael Fredrickson

36,839
5
92
109

Thanks a lot Michael Fredrickson! You managed to solve my little problem in just one minute, I've been breaking my back on this issue the entire day... This website is awesome. Thanks again! – Geoffrey Bergman Aug 14 '12 at 23:01
Just did! Had to wait 8 minutes. – Geoffrey Bergman Aug 14 '12 at 23:10

score 2 · Answer 2 · answered Aug 14 '12 at 23:06

The problem with your query is that the group by is by one field, but you are selecting another. That means that your subquery is selecting an arbitrary description for each product. There is no guarantee that you are getting the complete list you expect.

The easiest way to fix this is by removing the group by:

SELECT prod_core.prodID, prod_core.title, prod_core.location, prod_core.genre,
       prod_core.price, prod_core.barcode
FROM prod_core
WHERE prod_core.barcode NOT IN (SELECT `description` FROM inc_core)

However, this is likely to be inefficient. For an "in" with a subquery, the best optimization is to use exists. Also, be sure that an index exists on description. Here is an alternative version, specifically for mysql:

SELECT prod_core.prodID, prod_core.title, prod_core.location, prod_core.genre,
       prod_core.price, prod_core.barcode
FROM prod_core
WHERE not exists (select 1 from inc_core where inc_core.description = prod_core.barcode)

Personally, I'm not a big fan of the correlated subquery as a replacement for the "in". However, this is the approach best optimized by mysql.

score 1 · Answer 3 · answered Aug 14 '12 at 23:00

I don't think you need the group by clause, so you should be able to use:

SELECT prod_core.prodID, prod_core.title, prod_core.location, prod_core.genre, prod_core.price, prod_core.barcode
FROM prod_core
WHERE prod_core.barcode NOT IN (SELECT `description` FROM inc_core)

Proper use of SELECT WHERE NOT IN

3 Answers3