----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: Prasanna_kakanoor Message 1 in Discussion Hi All, Here is the tips discussed in the last UG meeting. In SQL SERVER, a value of NULL indicates the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown. Here is the tips to get the records withour using any functions like ISNULL and the IS NULL. My simple requirement was to get all the vales in TableB that are not in TableA. I have crated TabelA and TableB. Table Strustutre. TableA ID Name 1 Prasanna 2 Kashi NULL Raghu TableB ID Name Dept 1 Prasanna SW 2 Kashi SW 3 Raghu SQ 4 Sainath HR 5 Bala SQ 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) GO 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) Go 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. Prasanna K L<o:p></o:p> Sr. Software Engineer<o:p></o:p> [MCAD(CM) | MCSD.NET(Early Achiever)]<o:p></o:p> <o:p> </o:p> ZapApp India Private Limited Level G & 1, Explorer Building, International TechPark, Whitefield Road, Bangalore - 560 066. INDIA 91-80- 5115 6050 extn. 4061 (phone) 91-80- 841 1858 (fax) [EMAIL PROTECTED]<o:p></o:p> <o:p> </o:p> ----------------------------------------------------------- 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]
