Use Exist

Question

Use Exist

Viewing 3 posts - 16 through 17 (of 17 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Mithun
I have one query like
select * from emp where dept_no in(10,20,30)
Can anyone help me in using EXISTS instead of IN ?

Mithun,

Everyone else has posted the sensible answers, so I won't repeat them.

Taking your question absolutely literally - take this example from AdventureWorks:

SELECT*

FROMHumanResources.Employee

WHEREManagerId IN (11,21,31);

...which can be rewritten as:

SELECT*

FROMHumanResources.Employee AS E

WHEREEXISTS(SELECT 1 WHERE E.ManagerId = 11)

OREXISTS(SELECT 1 WHERE E.ManagerId = 21)

OREXISTS(SELECT 1 WHERE E.ManagerId = 31);

...or (if you prefer UNION [ALL] to OR):

SELECT*

FROMHumanResources.Employee AS E

WHEREEXISTS

(

SELECT 1 WHERE E.ManagerId = 11

UNION

SELECT 1 WHERE E.ManagerId = 21

UNION

SELECT 1 WHERE E.ManagerId = 31

);

The query optimizer is clever enough to see that the two rewrites are equivalent. It generates exactly the same plan for both.

All three versions happen to require 9 logical reads (on my machine). However the plan for the rewritten query requires more compilation effort and will require more CPU than the original.

I only mention all this on the off-chance that this is in fact a 'SQL puzzle' rather than a practical exercise: "rewrite an IN clause using only EXISTS, and without referencing the same table twice..."

Cheers

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

mithun.gite SSCrazy Points: 2333 More actions · Answer 2

Paul White (4/17/2009)
Mithun
I have one query like
select * from emp where dept_no in(10,20,30)
Can anyone help me in using EXISTS instead of IN ?
Mithun,
Everyone else has posted the sensible answers, so I won't repeat them.
Taking your question absolutely literally - take this example from AdventureWorks:
SELECT*
FROMHumanResources.Employee
WHEREManagerId IN (11,21,31);
...which can be rewritten as:
SELECT*
FROMHumanResources.Employee AS E
WHEREEXISTS(SELECT 1 WHERE E.ManagerId = 11)
OREXISTS(SELECT 1 WHERE E.ManagerId = 21)
OREXISTS(SELECT 1 WHERE E.ManagerId = 31);
...or (if you prefer UNION [ALL] to OR):
SELECT*
FROMHumanResources.Employee AS E
WHEREEXISTS
(
SELECT 1 WHERE E.ManagerId = 11
UNION
SELECT 1 WHERE E.ManagerId = 21
UNION
SELECT 1 WHERE E.ManagerId = 31
);
The query optimizer is clever enough to see that the two rewrites are equivalent. It generates exactly the same plan for both.
All three versions happen to require 9 logical reads (on my machine). However the plan for the rewritten query requires more compilation effort and will require more CPU than the original.
I only mention all this on the off-chance that this is in fact a 'SQL puzzle' rather than a practical exercise: "rewrite an IN clause using only EXISTS, and without referencing the same table twice..."
Cheers
Paul

Dear Paul,,,,

It is really the productive one.... this is really working as good as with IN, and really i was not aware that we can write this way alsoooo

with this

SELECT *

FROM HumanResources.Employee AS E

WHERE EXISTS(SELECT 1 WHERE E.ManagerId = 11 or E.ManagerId = 21 or E.ManagerId = 31)

even the cpu time is also same for both...........

Thanks a lot Paul, it was really coolllll

Mithun

Paul White SSC Guru Points: 150467 More actions · Answer 3

Hey Mithin,

That was just a bit of fun though on my part - I hope that was clear!

I sometimes use = ANY rather than IN, just for variety 😀

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi