× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.