How Dangerous Null values are in Subquery

Today I came across a strange problem while using simple subquery, everything was right but no data was returning.


here is that query :

Declare @Seats Table (SeatNumber Varchar(4))
Declare @ExcludeSeats Table (SeatNumber Varchar(4))

Insert Into @Seats
Select '44K'
UNION
Select '44J'


Insert Into @ExcludeSeats
Select '44K'
UNION
Select NULL


Select * from @Seats
Select * from @ExcludeSeats

Select * From @Seats
Where SeatNumber Not In (Select SeatNumber From @ExcludeSeats)

-------------------------------------------------------------------
First table containing seat Numbers 44K & 44J and other containing 44K and a Null value.
And now requirement was to find out the seats that are available in table Seats but does not exists in table ExcludeSeats, I wrote a very simple query using a subquery and  run . I was surprised to see no data.

However, one row should return.

After sometime I got the problem of NULL value returning from subquery and when I filtered the NULL value, worked fine .

The query was after modified :


Select * From @Seats
Where SeatNumber Not In (Select SeatNumber From @ExcludeSeats Where SeatNumber Is Null)

So today learning is that If a subquery returns NULL value, the query failed.


Thanks