This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Thursday, May 5, 2011

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

1 comment:

chandra said...

Other way of solving this is by using left join, see below

Select * from @Seats
Select * from @ExcludeSeats

Select * From @Seats S
LEFT join @ExcludeSeats E ON E.SeatNumber = S.SeatNumber
WHERE E.SeatNumber is null