= , != and Not Exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

= , != and Not Exists

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-17 : 09:10:46

Some body please tell me whats wrong here.


Table - karunatest
int         nvarchar(100) - both are not null     
IdNo        CompanyName
1234        Test Company
5678        Another Test Company
8888        One More Test Company

Table - karunatest2
int(PK)     nvarchar(100) - both are not null     
IdNo        CompanyName
1234        Test Company
5678        Another Test Company

I need to fetch the records from karunatest if its not there in karunatest2. So I write a query like this:


select distinct A.IdNo from karunatest A, karunatest2 B where  A.Idno <> b.Idno
The result is 
IdNo
-----
1234
5678
8888
select distinct A.IdNo from karunatest A, karunatest2 B where  A.Idno != b.Idno This also gives the same above result.

select distinct A.IdNo from karunatest A, karunatest2 B where  A.Idno = b.Idno This gives expected result.
IdNo
----
1234
5678

select A.Idno from karunatest A where not exists (select IdNo from karunatest2) This gives nothing.

Whats wrong in the queries?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-17 : 09:38:15

[code]
--====================================================================================
select distinct A.IdNo from karunatest A, karunatest2 B where A.Idno <> b.Idno
select distinct A.IdNo from karunatest A, karunatest2 B where A.Idno != b.Idno --This also gives the same above result.
--These produce a cartisian product excluding the matching results. Its the same as:
select a.IDNo
from karunatest a
CROSS JOIN karunatest2 b
Where a.idno <> b.idno

--====================================================================================
select distinct A.IdNo from karunatest A, karunatest2 B where A.Idno = b.Idno This gives expected result.
--This is a typical eque-join. Same as

select a.IDNo
from karunatest a
INNER JOIN karunatest2 b
ON a.idno = b.idno

--====================================================================================

select A.Idno from karunatest A where not exists (select IdNo from karunatest2) This gives nothing.
--this is syntactically correct but I think you wanted this instead:

Select idno
from Karunatest a
Where NOT Exists
(Select * from karunatest2
where idno = a.idno)

--====================================================================================
--This is another way to achieve the NOT exists (using left outer join):
Select a.idno
From Karunatest a
Left OUTER JOIN Karunatest b
ON a.idno = b.idno
where b.idno is NULL

--the old syntax for left outer join is: a.idno *= b.idno
--However, when combined with the where clause: where b.idno is NULL
--gives incorrect results. I'm sure that's one reason why the sql 92 syntax
--has replaced the old syntax you are using. You should swith to the new.

--notice this give different results (it's wrong)
Select a.idno
From Karunatest a, Karunatest b
Where a.idno *= b.idno
and b.idno is NULL
[/code]

Be One with the Optimizer
TG

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-17 : 09:48:07

I can't use != or <> in inner joins? Like this:


select	a.IDNo 
from	karunatest a 
INNER JOIN karunatest2 b
		ON a.idno != b.idno

select	a.IDNo 
from	karunatest a 
INNER JOIN karunatest2 b
		ON a.idno <> b.idno

If I use this it should fetch the records which are not matching a.idno with b.idno right?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-17 : 09:59:26

quote:

Originally posted by TG


select A.Idno from karunatest A where not exists (select IdNo from karunatest2) This gives nothing.
--this is syntactically correct but I think you wanted this instead:

Select idno
from	Karunatest a
Where NOT Exists
	(Select * from karunatest2
	where idno = a.idno)

Thanks TG, It did worked


select A.Idno from karunatest A where not exists (select IdNo from karunatest2)

But Still I dont understand why this query didnt work

. Whats wrong in it...

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-17 : 10:00:28

That will execute fine with no syntax errors, however the ON clause is defining the how the records are joined. That will give the same results as my cross join example and your old syntax version:
select distinct A.IdNo from karunatest A, karunatest2 B where A.Idno <> b.Idno

In both cases you are saying, FOR EACH RECORD in tblA show me all the records in tblB that don't match the "current" idno in tblA.

I think what you are tying to say is, match up all the idNOs in both tables and show me the missing ones from tblB.
If you want the missing ones from Both tables you would need a FULL OUTER JOIN:


Select	a.idno, b.idno
From	Karunatest a
FULL OUTER JOIN Karunatest b
	ON a.idno = b.idno
where b.idno is NULL OR a.idno is NULL

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-17 : 10:04:31

quote:

select A.Idno from karunatest A where not exists (select IdNo from karunatest2)

But Still I dont understand why this query didnt work . Whats wrong in it...

For Where NOT Exists to work you need to JOIN to the outer select from the inner one:
from karunatest1 a where not exists(Select * from Karunatest2 where idno = a.idno)

Be One with the Optimizer
TG

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-17 : 10:12:51

So the where NOT Exists always need a join in the outer select query?
This will also give the desired returns right?
select distinct a.idno from karuna a where a.idno not in (select distinct b.idno from karunatest2)

Any performance issues using one over the other?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-17 : 10:22:17

Okay..
I checked both the NOT Exists and NOT IN queries in Execution plan, While the NOT Exists has one nested loop, The NOT IN query has 2 nested loops and more select/scan than the NOT Exists query.

For me it looks like NOT Exists will be the better performer with large number of datas

Any comments?

TG thanks for all your help and patience in explaining

...

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-17 : 10:32:49

You're quite welcome, however you're still not quite there.

quote:
select distinct a.idno from karuna a where a.idno not in (select distinct b.idno from karunatest2)

This is not correct. It won't even run because you're using an alias "b" but not defining it anywhere.

NOT EXISTS as a sub query - and NOT IN has two different purposes.

Books on Line will do a much better job than me at explaining this. Start with "NOT EXISTS" and "NOT IN" topics. Then move on to JOIN fundamentals.

Have fun and keep looking at execution plans. Not enough people do :)

Be One with the Optimizer
TG

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-17 : 10:51:55

quote:
Originally posted by TG

You're quite welcome, however you're still not quite there.
quote:
select distinct a.idno from karuna a where a.idno not in (select distinct b.idno from karunatest2)

This is not correct. It won't even run because you're using an alias "b" but not defining it anywhere.

Actually thats a typo TG. I missed out the b during typing. I'm not so bad in queries... Still Joins and Multiple Joins are my very weak areas..

NOT EXISTS as a sub query - and NOT IN has two different purposes.

Books on Line will do a much better job than me at explaining this. Start with "NOT EXISTS" and "NOT IN" topics. Then move on to JOIN fundamentals.

I'll look into it.

Have fun and keep looking at execution plans. Not enough people do :)

Atleast this one I do regularly when I have to work with variations of queries.
Be One with the Optimizer
TG

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources