Query realated to EXISTS and NOT EXISTS

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

SQL Server Development (2000)

Query realated to EXISTS and NOT EXISTS

Author

Topic

raj_arc
Starting Member

6 Posts

Posted - 2006-09-27 : 03:09:05

Hello all,

My question is

There are certain operators which r redundant. like S IN R can be repaced with S =ANY R. we need to show that EXISTS and NOT EXISTS can also be replaced with somethibng that doent involve EXISTS.
--- we can use some constants in SELECT clause.

One thing I thought was

Select X.....
From T
where 0 < count(R)

is there any better way to show this.

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-27 : 03:17:43

Most probably, yes.
Show us some sample data and I am sure we will come up with something clever.

Peter Larsson
Helsingborg, Sweden

raj_arc
Starting Member

6 Posts

Posted - 2006-09-27 : 03:21:45

we have to replace S EXISTS R with some thing else.

Thats it. No data.... We have to present it in this way only.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-27 : 03:26:32

There are many alternatives.

1) LEFT JOIN
2) INNER JOIN
3) RIGHT JOIN
4) WHERE
5) HAVING

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-27 : 03:27:07

First of all, Exists is unary operator..so S Exists R is wrong.
It can be transformed into something which does not uses Exists operator in many ways:

1) 

Select * from tbl where 0 = (Select count(1) from Tbl2) -- not exists
Select * from tbl where 1 = (Select count(1) from Tbl2) -- exists

2)

Select * from Tbl where tbl.id not in (select ID from tbl2) -- not exists
Select * from Tbl where tbl.id in (select ID from tbl2) -- exists

3)
Select t1.* from tbl left t1 join tbl2 t2 on t1.id = t2.id
where t2.id is NULL -- not exists
Select t1.* from tbl left t1 join tbl2 t2 on t1.id = t2.id
where t2.id is not NULL -- exists

...and many more!!

Harsh Athalye
India.
"Nothing is Impossible"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-09-27 : 08:03:44

With regards to solution 2), keep in mind that IN (NOT IN) handles NULL differently than EXISTS (NOT EXISTS).

Raj, isn't the answer in your textbook somewhere?

Jay White

raj_arc
Starting Member

6 Posts

Posted - 2006-10-01 : 20:22:14

Thanks a lot guys

raj_arc
Starting Member

6 Posts

Posted - 2006-10-02 : 10:34:41

Hello Harsh

can I write this way

Select * from tbl where 0 = (Select distinct(1) from Tbl2) -- not exists
Select * from tbl where 1 = (Select distinct(1) from Tbl2) -- exists

distinct() instead of count().

i.e without aggregate functions but with constants.

Thanks,
Raj

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-02 : 12:49:58

quote:
Originally posted by raj_arc

Hello Harsh

can I write this way

Select * from tbl where 0 = (Select distinct(1) from Tbl2) -- not exists
Select * from tbl where 1 = (Select distinct(1) from Tbl2) -- exists

distinct() instead of count().

i.e without aggregate functions but with constants.

Thanks,
Raj

Yes, you can do that...although it's bit costly due to DISTINCT operator...here is one more variation that is close to EXISTS operator in terms of performance(although syntactically it looks weird):

Select * from tbl where 0 = (Select top 1 1 from Tbl2) -- not exists
Select * from tbl where 1 = (Select top 1 1 from Tbl2) -- exists

Harsh Athalye
India.
"Nothing is Impossible"

raj_arc
Starting Member

6 Posts

Posted - 2006-10-02 : 15:19:50

Thanks Harsh.

Hey by the way how actually top works????

Raj

raj_arc
Starting Member

6 Posts

Posted - 2006-10-02 : 15:27:03

and also what actually top 1 1 does??

and also if u can explain me how this can actually replace exists and not exists. means how this works like Exists.

Thanks.

Raj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources