----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: VinodK [TLabs, SCT] Message 3 in Discussion Hi All, Good Job of compiling the Tips and Tricks Section we had in the UG Meet. As said this was a Tip I took rather than a Trick which I normally have. A couple of points I wanted to stress with this tip was: 1. NULLs in SQL Server have the function of showing as "Not Applicable" and "Not Defined" / "Unknown". 2. When Comapring NULLS with any query results in a False statement only. Just to supply you the complete .sql I had for Demo. Create table tableA (id Int) Create table tableB (id Int) GO 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) GO Select * from tableA Select * from tableB GO And the aim to get all the child rows: Select b.id from tableB b Where b.id NOT IN (Select a.id from TableA a) GO Can get translated internally as : Select b.id from tableB b Where NOT IN ( b.id = (Select a.id from TableA a)) GO Then as Select b.id from tableB b Where NOT IN ( (b.id = 1) or (b.id = 2) or (b.id = NULL) ) GO Since the last clause will yield false even for values 3 and 4. We willnot get any rows. This is just a breakdown of the query for better understanding. On the contrary if we were to use the Exists Clause the query becomes something like: Select id from tableB b Where NOT Exists (Select 1 from TableA a Where a.id = b.ID) Go The query gets short circuited for every false value. Hence you will be able to get the values 3 and 4 .... Understand that the obvious is not sometime visible to us. Hence be careful when dealing with NULLs. Hope this tip helped all. Regards, Vinod Kumar www.ExtremeExperts.com ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/BDotNet/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
