Re: SQL help with EXISTS

[Charles Wilt <charles.wilt@xxxxxxxxx>]

If the EXISTS subselect is correlated, ie.

select <> from FILEA A
where exists (select * from FILEB B where b.key = A.key)

Then the exists is evaluated whenever the correlated field(s) change.

Charles


On Thu, Dec 6, 2012 at 12:06 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

> I don't think that is correct.  It seems to do the subselect (return a set
> of rows) and THEN join the result to the outer select.  A few responses
> have stated what you said - that EXISTS simply returns TRUE or FALSE.  But
> I think that it must do this for each row in the outer select in the case
> where a join is occurring?
>
> Maybe Im wrong, I don't know SQL and it is elusive and powerful and
> complex so I don't understand all the subtleties.
>
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
> Sent: Thursday, December 06, 2012 11:01 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL help with EXISTS
>
> The EXISTS says "are there any". You get the same results in the outer
> select whether there's one or many results from the subselect.
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
> Sent: Thursday, December 06, 2012 10:54 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL help with EXISTS
>
> Thanks - you are correct - nothing to relate the outer query to the
> subselect.  I must have deleted it :(
>
> Please look at my recent post - if I drop the GROUP BY, result will have
> too many records I think - I only want ONE rec from outer select for each
> occurrence in file.
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
> Sent: Thursday, December 06, 2012 10:50 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL help with EXISTS
>
> You have nothing to relate the outer query to the subselect. Looks like
> you deleted the A. in front of ASHPNBR in the where clause. I think it
> should be:
>
> select * from miscchg A
> where
> exists
> (select ASHPNBR, ABLDNGD, AMSCHRG from MISCCHG  where A.ASHPNBR =
> 'BN123456'
> group by ASHPNBR,ABLDNGD,        AMSCHRG         )
>
> By the way there's no need for all the fields in the subselect. You'd get
> the same results quicker with:
>
> select * from miscchg A
> where
> exists
> (select 1 from MISCCHG  where A.ASHPNBR = 'BN123456'
> group by ASHPNBR,ABLDNGD,        AMSCHRG         )
>
> I don't think the group by is buying you anything, either.
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
> Sent: Thursday, December 06, 2012 10:23 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: SQL help with EXISTS
>
> 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______________________________________________________________________
> --
> 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.
>
>
>
> --
> 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.
>
>
> ________________________________________________________________________
> This inbound email has been scanned for all viruses by the MessageLabs
> SkyScan
> service.
> ________________________________________________________________________
>
> ______________________________________________________________________
> This outbound email has been scanned for all viruses by the MessageLabs
> Skyscan service.
> For more information please visit http://www.symanteccloud.com
> ______________________________________________________________________
> --
> 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.
>
>
>
> --
> 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.
>
>
> ________________________________________________________________________
> This inbound email has been scanned for all viruses by the MessageLabs
> SkyScan
> service.
> ________________________________________________________________________
>
> ______________________________________________________________________
> This outbound email has been scanned for all viruses by the MessageLabs
> Skyscan service.
> For more information please visit http://www.symanteccloud.com
> ______________________________________________________________________
> --
> 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.