Why do these queries return different results?

Question

SELECT DISTINCT productName, listPrice 
FROM product 
WHERE productID IN (SELECT productID FROM saleItem WHERE quantity = 2)
  AND productID NOT IN (SELECT productID FROM saleItem WHERE quantity <> 2)

Hey everyone, I'm querying a database for a class to recap the lessons from the semester and am a bit confused. The query above returns a different number of results than the query below. It's an entry level course and I still can't figure out how quantity = 2 differs from NOT in (SELECT productID FROM saleItem WHERE quantity <> 2). The way I am reading the syntax seems they should only return a quantity of 2. If anyone could help, It would be hugely appreciated! If this a poorly formatted question I apologize.

SELECT DISTINCT productName, listPrice 
FROM product 
WHERE productID IN (SELECT productID FROM saleItem WHERE quantity = 2)

Answer 1

Consider product IDs that appear in multiple rows of saleItem:

• Some rows would have quantity of 2
• Some other rows would have quantity different from 2

If you have two saleItem rows for the same productId, and at least one of these rows has quantity not equal to 2, the row corresponding to that productId would be excluded from the first query.

For example, if your saleItem looks like this

saleItemId ProductId Quantity
---------- --------- --------
       100         1        2
       101         2        3
       102         2        2

then the first query would exclude productId=2, while the second query would include it.

Answer 2

I still can't figure out how quantity = 2 differs from NOT in (SELECT productID FROM saleItem WHERE quantity <>2)

Probably NULL values, you could simple check it with:

SELECT distinct productName, listPrice 
FROM product 
WHERE productID NOT IN (SELECT COALESCE(productID,-1) 
                        FROM saleItem 
                        WHERE quantity <> 2) --assuming that quantity is NOT NULL

Answer 3

Let's assume your tables with following sample data:

Products:

productID | productName | listprice
-------------------------------------
   1      |      A      |   100
-------------------------------------
   2      |      B      |   200
-------------------------------------
   3      |      C      |   300
-------------------------------------
   4      |      D      |   400

saleItem:

productId | quantity
----------------------
    1     |   2
----------------------
    2     |   2
----------------------
    3     |   5
----------------------
    4     |   Null
----------------------
    1     |    3

Note: I have assumed here that quantity field doesn't have Not Null constraint.

Query :

       SELECT  productID FROM saleItem WHERE quantity =2

Result:

   productID
  -----------
       1
  -----------
       2
  -----------
       1

Query :

       SELECT distinct productID FROM saleItem WHERE quantity =2

Result:

   productID
  -----------
       1
  -----------
       2

Query:

SELECT  productID FROM saleItem WHERE quantity <> 2 // <> operator won't include rows with quantity having NULL values 

Result:

   productID
  ------------
       1
  -----------
       3
  -----------
       1    

Query:

SELECT  distinct productID FROM saleItem WHERE quantity <> 2 

Result:

   productID
  ------------
       1
  -----------
       3

Hence, Query 1 can be converted in:

SELECT distinct productName, listPrice 
FROM product 
WHERE productID IN (1,2,1) 
AND productID NOT in (1,3,1);

Results:

    productID | productName | listprice
    -------------------------------------
       2      |      B      |   200

So, Query 1 is showing results of productID which only has one value for quantity and that is 2.

Query 2:

SELECT distinct productName, listPrice 
FROM product 
WHERE productID IN (1,2,1);

Results:

    productID | productName | listprice
    -------------------------------------
       1      |      A      |   100
    -------------------------------------
       2      |      B      |   200

So, Query2 will return info for those productID who has 2 as quantity but may or may not have other values quantity as well.

Last Note:

1) quantity <> 2 won't include rows with NULL quantity. But Not IN ( quantity <> 2) will include rows that has value for quantity other than 2 including NULL.

Hope it helps: