Help explaining query results.

Question

Help explaining query results.

Don.

Ten Centuries

Points: 1293
More actions
January 29, 2013 at 6:40 am

#394050

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

demonfox SSCertifiable Points: 6289 More actions · Answer 1

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 :ermm:

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 2

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

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/

Don. Ten Centuries Points: 1293 More actions · Answer 5

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)