InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 23:41:39 GMT20http://www.sqlservercentral.com/Forums/Topic1413009-391-1.aspxHi Guys, Thanks for the replies.Adding the where clause below returned the correct results.[code="sql"]WHERE Client_Accountno is not null[/code][code="sql"]Select * from Contact1Where Company <> 'Z_CANDIDATE' And Accountno not in (Select Distinct Client_Accountno from RSM_KPI WHERE Client_Accountno is not null)[/code]Its still going to take some getting used to the script below not working though...[code="sql"]Select * from Contact1Where Company <> 'Z_CANDIDATE' And Accountno not in (Select Distinct Client_Accountno from RSM_KPI)[/code]Thu, 31 Jan 2013 11:26:34 GMTDon.http://www.sqlservercentral.com/Forums/Topic1413009-391-1.aspxDo you have the value NULL in column client_accountno? It seems that you have it. You have to take into account NULLs. Check the code bellow that shows it:[code]--Creating the tablescreate table Demo (I INT NULL)gocreate table Demo2 (I INT NULL)go--Inserting the datainsert into Demo (I) select 1 union select 2 goinsert into Demo2 (I) select 1 union select 3 go--At this point Demo2 does not --contain the value NULL, so this should workselect * from Demowhere I not in (select I from Demo2)select * from Demowhere not exists (select Demo2.I from Demo2 where Demo2.I = Demo.I)--After inserting the value Null the first query--won't return any recordsinsert Demo2 (I) values (NULL)select * from Demowhere I not in (select I from Demo2)--I need to exclude the value Null from the subqueryselect * from Demowhere not exists (select Demo2.I from Demo2 where Demo2.I = Demo.I)--cleanupdrop table Demodrop table Demo2[/code]AdiTue, 29 Jan 2013 07:35:22 GMTAdi Cohn-120898http://www.sqlservercentral.com/Forums/Topic1413009-391-1.aspx[quote][b]Jason-299789 (1/29/2013)[/b][hr]This URL may explain it better [url]http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[/url][/quote]I believe that this article explains better this situationhttp://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/The article is really interesting and worth reading but the conclusion is this:"Most importantly, NOT EXISTS and NOT IN do not have the same behaviour when there are NULLs involved."Your task is to read it to understand why.;-)Tue, 29 Jan 2013 07:34:26 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1413009-391-1.aspxThis URL may explain it better [url]http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[/url]Tue, 29 Jan 2013 07:28:52 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1413009-391-1.aspx[quote][b]Don. (1/29/2013)[/b][hr]Hi Guys,I was working on a script that would show all records in Table A that didnt have corresponding records in Table B.I thought it would be simple enough and wrote the script below, which returned 0 rows.There should definitely be a couple thousand rows returned and am fairly concerned \ curious as to why the script below is incorrect.[code="sql"]Select * from Contact1Where Company <> 'Z_CANDIDATE' And Accountno not in (Select Distinct Client_Accountno from RSM_KPI)[/code]I amended the script to the script below, and its returned the correct results.[code="sql"]Select * from Contact1Where Company <> 'Z_CANDIDATE' AND NOT EXISTS (Select Client_Accountno from RSM_KPI Where RSM_KPI.Client_Accountno = Contact1.Accountno)[/code]Could someone explain \ point me to some links that would explain the discrepancy?ThanksDon[/quote]Second Query does row by row check for the Contact table record in RSM_KPI table record ;First query , just check if any record of account_no in contacts table is not present in RSM_KPI table ; I guess it should return true for both Exists and Not Exists..Tue, 29 Jan 2013 07:13:03 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1413009-391-1.aspxHi Guys,I was working on a script that would show all records in Table A that didnt have corresponding records in Table B.I thought it would be simple enough and wrote the script below, which returned 0 rows.There should definitely be a couple thousand rows returned and am fairly concerned \ curious as to why the script below is incorrect.[code="sql"]Select * from Contact1Where Company <> 'Z_CANDIDATE' And Accountno not in (Select Distinct Client_Accountno from RSM_KPI)[/code]I amended the script to the script below, and its returned the correct results.[code="sql"]Select * from Contact1Where Company <> 'Z_CANDIDATE' AND NOT EXISTS (Select Client_Accountno from RSM_KPI Where RSM_KPI.Client_Accountno = Contact1.Accountno)[/code]Could someone explain \ point me to some links that would explain the discrepancy?ThanksDonTue, 29 Jan 2013 06:40:26 GMTDon.