Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help explaining query results. Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 6:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 7, 2014 6:01 PM
Points: 46, Visits: 227
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.

Select * from Contact1
Where Company <> 'Z_CANDIDATE'
And Accountno not in (Select Distinct Client_Accountno from RSM_KPI)

I amended the script to the script below, and its returned the correct results.

Select * from Contact1
Where Company <> 'Z_CANDIDATE'
AND NOT EXISTS (Select Client_Accountno from RSM_KPI Where RSM_KPI.Client_Accountno = Contact1.Accountno)

Could someone explain \ point me to some links that would explain the discrepancy?

Thanks

Don
Post #1413009
Posted Tuesday, January 29, 2013 7:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
Don. (1/29/2013)
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.

Select * from Contact1
Where Company <> 'Z_CANDIDATE'
And Accountno not in (Select Distinct Client_Accountno from RSM_KPI)

I amended the script to the script below, and its returned the correct results.

Select * from Contact1
Where Company <> 'Z_CANDIDATE'
AND NOT EXISTS (Select Client_Accountno from RSM_KPI Where RSM_KPI.Client_Accountno = Contact1.Accountno)

Could someone explain \ point me to some links that would explain the discrepancy?

Thanks

Don

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..


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1413033
Posted Tuesday, January 29, 2013 7:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
This URL may explain it better http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1413042
Posted Tuesday, January 29, 2013 7:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:16 PM
Points: 3,313, Visits: 7,147
Jason-299789 (1/29/2013)
This URL may explain it better http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/


I believe that this article explains better this situation
http://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.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1413047
Posted Tuesday, January 29, 2013 7:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
Do 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:

--Creating the tables
create table Demo (I INT NULL)
go
create table Demo2 (I INT NULL)
go

--Inserting the data
insert into Demo (I)
select 1 union select 2
go

insert into Demo2 (I)
select 1 union select 3
go

--At this point Demo2 does not
--contain the value NULL, so this should work
select * from Demo
where I not in (select I from Demo2)

select * from Demo
where not exists (select Demo2.I from Demo2 where Demo2.I = Demo.I)

--After inserting the value Null the first query
--won't return any records
insert Demo2 (I) values (NULL)

select * from Demo
where I not in (select I from Demo2)

--I need to exclude the value Null from the subquery
select * from Demo
where not exists (select Demo2.I from Demo2 where Demo2.I = Demo.I)

--cleanup
drop table Demo
drop table Demo2




Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1413049
Posted Thursday, January 31, 2013 11:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 7, 2014 6:01 PM
Points: 46, Visits: 227
Hi Guys,

Thanks for the replies.

Adding the where clause below returned the correct results.

WHERE Client_Accountno is not null

Select * from Contact1
Where Company <> 'Z_CANDIDATE'
And Accountno not in (Select Distinct Client_Accountno from RSM_KPI WHERE Client_Accountno is not null)


Its still going to take some getting used to the script below not working though...
Select * from Contact1
Where Company <> 'Z_CANDIDATE'
And Accountno not in (Select Distinct Client_Accountno from RSM_KPI)

Post #1414267
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse