Well, I am a part of the Bangalore User Group. And it has been conducting interesting sessions for a while now. And I try to make the sessions interesting by chipping in some SQL Server Tips and tricks. Yesterdays tip was to the use of NULL in SQL Server. And this tip was useful to most of them. They never knew now NULLs can give different results in SQL Server. Just to give all a small insight into the tip …
Create table tableA (id Int)
Create table tableB (id Int)
Insert into tableA values (1)
Insert into tableA values (2)
Insert into tableA values (NULL)
Insert into tableB values (1)
Insert into tableB values (2)
Insert into tableB values (3)
Insert into tableB values (4)
Select * from tableA
Select * from tableB
Now that we have created two tables. My simple requirement was to get all the vales in TableB that are not in TableA and the first solution that came to everyones mind was to use the following Query.
Select b.id from tableB b
Where b.id NOT IN (Select a.id from TableA a)
And that was a win for me to make them understand what this really means. Since the presense of NULL would make the query false for all the values and hence you will get no. rows. To avoid this we mave a couple of options. Starting from using ISNULL and the IS NULL expressions to eliminate the NULL values. Or the best solution that I would recommend is:
Select id from tableB b
Where NOT Exists (Select 1 from TableA a Where a.id = b.ID)
Faster and more efficient … This is something we need to keep in mind while coding for our applications. I also understand that if we were to SET ANSI_NULLS OFF then also this behaviour is observed. But I donot recommend that approach.
This entry was posted on Saturday, September 20th, 2003 at 18:32 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.