There are cases, but most of the time IN is a better predicate than EXISTS. Either of these will get a list of employees who are in departments with total salary greater than $10,000.
select * from employees a where EXISTS (select 1 from employees b where a.dept = b.dept having sum(b.salary) > 10000);
select * from employees where dept IN (select dept from employees group by dept having sum(salary) > 10000);
Somebody run it over a big dataset and see which one is quicker.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Naughton
Sent: Friday, December 07, 2012 10:58 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL help with EXISTS
Hi Vern,
I agree with you. I have see rare cases where using the same file in EXISTs and the main element makes sense, but it's always because of some sort of funky database design (customers who have sales reps, but sales reps are also customers, so they're all in the same file, and you're trying to find sales reps that actually have customers -- something like that). In general, it doesn't make any sense (IMHO :-).
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> writes:
My issue with the example is that it is using the same file both in the
main statement and in the EXISTS test - that is also happening in your
modified form.
There might be some uses for that, but it still feels a little strange.
I see the more general case - your modification - as this kind of thing
- the EXISTS SELECT is over __file2__, while the main SELECT is over
__file1__. (Underlines for emphasis)
SELECT * FROM file1 a WHERE <some tests> AND EXISTS (SELECT * FROM
file2 b WHERE b.field3=a.field1 and b.field4=a.field2 ....)
Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA 01376
413-676-3144
Internal: x 444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that any use is strictly prohibited. If you have received this e-mail in error, please notify us immediately by replying to it and then delete it from your computer.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
