CODEWITHSUNDEEP
sqlsql-server
mnj
mnj

Reputation: 3453

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.

Upvotes: 0

Views: 59

Answers (3)

mnj
mnj

Reputation: 3453

I also found another solution:

select CategoryName from Categories c LEFT OUTER JOIN Products p
ON c.CategoryID = p.CategoryID
where ProductName is null

I think that answers above are more intuitive, but I just wanted to show another way of handling this.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

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.

Upvotes: 2

Mani
Mani

Reputation: 344

Sql works on three value based logic.Your table must contains null in category ID column thats why its not displaying anything,Modify subquery as

 SELECT CategoryName 
  FROM Categories
  WHERE CategoryID NOT IN (select CategoryID from Products where catogoryID is not null)

Upvotes: 2

Related Questions