Loreno Loreno - 5 months ago 14
SQL Question

Subquery in IN does not work

I'm using TransactSQL (Microsoft SQL Server 2014) and here's my problem:
I'm using the standard "Northwind" database for practice and I wanted to see a list of Categories that don't have any Products.

Firstly I created a new Category, which wouldn't have any Products:

insert into Categories (CategoryName) values ('TestCategory')


Then I wrote this:

SELECT CategoryName
FROM Categories
WHERE CategoryID NOT IN (select CategoryID from Products)


Unfortunately it gives nothing.

Here's how I understand it:

I want to find categories without products connected with them, so I used
WHERE CategoryID NOT IN (select CategoryID from Products)
, because I want to compare every CategoryID from Categories table with a list which I get from
select CategoryID from Products
- this list includes every category that HAS products. So I think that using NOT IN should let me to see categories that don't have any products.

I hope you understood what I was trying to describe. It would be great if you could help.

Answer

NOT IN is not a good construct. If any of the returned values from the subquery are NULL, then it never returns any rows. A naive way to fix this is to use WHERE:

SELECT CategoryName 
FROM Categories
WHERE CategoryID NOT IN (select CategoryID from Products where CategoryId IS NOT NULL);

A better way to fix it is to use NOT EXISTS:

SELECT c.CategoryName 
FROM Categories c
WHERE NOT EXISTS (SELECT 1
                  FROM Products p
                  WHERE p.CategoryID = c.CategoryID 
                 );

This behaves in a more intuitive manner.

Note that this behavior is ANSI standard behavior. When a NULL value is present, then the engine does not know if c.categoryId is in the list. Hence, it returns NULL which is treated as false.