Re: Help with SQL

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Exception join only brings back records from the parent when there's no
match at all in the child.

That's the definition of exception.

Charles


On Fri, Dec 20, 2019 at 8:58 AM Darren Strong <darren@xxxxxxxxx> wrote:

> It depends on the dataset.  If your exception join brings in 5 child
> records that match what you're looking for, you are then ignoring 4 records
> because what you really wanted was to just know if there was a match to
> your primary record. This is also why a "FETCH FIRST ROW ONLY" will perform
> better on a column sub-select, even when there is only one record ever
> returned, because you've told the optimizer that you're only interested in
> one record so it won't look for more than one, which would incidentally
> also crash the sub-select.
>
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Charles Wilt
> Sent: Friday, December 20, 2019 10:53 AM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: Re: Help with SQL
>
> CAUTION: This email originated from outside of the organization. Do not
> click links or open attachments unless you recognize the sender and know
> the content is safe.
>
>
> The query engine is optimized for joins.
>
> Many times, EXISTS or a IN will be re-written as a join.
>
> An exception join, as shown, will not bring try to bring in any data from
> the child table.
>
> Charles
>
>
> On Fri, Dec 20, 2019 at 8:47 AM Darren Strong <darren@xxxxxxxxx> wrote:
>
> > I've seen NOT EXISTS and JOINS suggested. My opinion is that a NOT
> > EXSISTS will perform the best for you.  If you don't actually need a
> > value from the child table, a join brings the possibility of a higher
> performance hit vs.
> > the NOT EXISTS, which is accurately telling the optimizer that you
> > don't want anything from the secondary table except to know if a
> > record exists there.
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
> > subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives at
> > https://archive.midrange.com/midrange-l.
> >
> > Please contact support@xxxxxxxxxxxx for any subscription related
> > questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link: https://amazon.midrange.com
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com