Re: SQL help with EXISTS

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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
> ______________________________________________________________________