Re: SQL help with EXISTS

Hi Joel

Exists returns "true" if there are any records in the inner SELECT at all. In this case, it seems there is at least one record there, so the WHERE is always satisfied, hence, you get all the records from MISCCHG.

Usually you would not do an EXISTS against the same table in the outer SELECT. What is the point? You could use a simple WHERE clause to do that?

You use it best when you have some correlation - take a value from the outer SELECT for the comparison within the inner SELECT - correlation is the term you can look up. And this outer reference would be used as a test value for a column in the table in the inner SELECT.

Your inner SELECT typically would also never have GROUP BY - why would you, there is no selectivity done in a GROUP BY without a HAVING clause as well.

HTH
Vern

On 12/6/2012 10:23 AM, Stone, Joel wrote:

I am pretty sure that the following selected only a few records from a large file (ASHPNBR = 'BN123456' ).

Then I changed a paren or something small and I cant get it to select only a few records - now it selects ALL values of ASHPNBR.

The sub-select works great and only selects a few records.

When I add the outer select, the entire file is selected (all ASHPNBR values).

How does EXISTS know which keys to join between the inner & outer query? Do I have to explicitly state the join? Or does "EXISTS" make assumption about which fields to join?

Thanks







select * from miscchg A
where
exists
(select ASHPNBR, ABLDNGD, AMSCHRG from MISCCHG
where
ASHPNBR = 'BN123456'
group by ASHPNBR,ABLDNGD, AMSCHRG )

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________