Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #773 > unrolled thread

Non-Relating Join Criteria

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Non-Relating Join Criteria "Fred." <ghrno-google@yahoo.com> - 2011-11-02 06:53 -0700
    Re: Non-Relating Join Criteria Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALID> - 2011-11-02 15:08 +0100
    Re: Non-Relating Join Criteria "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-11-02 09:10 -0500

#773 — Non-Relating Join Criteria

Transact SQL apparently supports one-sided critera, 
such as the "a.SEL=15" in (1) below. in the join
expression. I would expect that both (1) and (2)
would return the same result set, and the examples
I have tried match this expectation.

My question is whether or not there can be a strong
reason to prefer one over the other, such as impact
on the order of evaluation.

CREATE TABLE a (APK int PRIMARY KEY, SEL int, ... )
CREATE TABLE b (BPK int PRIMARY KEY, AFK int, ... )

(1) SELECT ... FROM a INNER JOIN b 
    ON a.APK=b.AFK AND a.SEL=15

(2) SELECT ... FROM a INNER JOIN b 
    ON a.APK=b.AFK
    WHERE a.SEL=15

Fred.

[toc] | [next] | [standalone]

#774

Hi Fred,

>My question is whether or not there can be a strong
>reason to prefer one over the other, such as impact
>on the order of evaluation.

No. Order of evaluation can and will be shuffled by the optimizer if
that can improve performance.

The only reason for using one and not the other is readability of the
query.

Note, though, that for OUTER JOINs, the two queries are NOT
equivalent!
-- 
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[toc] | [prev] | [next] | [standalone]

#775

Fred. wrote:
> Transact SQL apparently supports one-sided critera,
> such as the "a.SEL=15" in (1) below. in the join
> expression. I would expect that both (1) and (2)
> would return the same result set, and the examples
> I have tried match this expectation.
>
> My question is whether or not there can be a strong
> reason to prefer one over the other, such as impact
> on the order of evaluation.
>
> CREATE TABLE a (APK int PRIMARY KEY, SEL int, ... )
> CREATE TABLE b (BPK int PRIMARY KEY, AFK int, ... )
>
> (1) SELECT ... FROM a INNER JOIN b
>     ON a.APK=b.AFK AND a.SEL=15
>
> (2) SELECT ... FROM a INNER JOIN b
>     ON a.APK=b.AFK
>     WHERE a.SEL=15
>
There is a nice article about this in BOL (Books Online - it's installed
when you install the client tools).
The short story is:
with inner joins there is no difference - the same query execution plan is
used for both variations.

With outer joins, however, different results can be obtained depending on
where you put the criterion, especially when the predicate applies to the
table on the right side of a left outer join:

 (1) SELECT ... FROM b LEFT JOIN a
     ON a.APK=b.AFK AND a.SEL=15

 (2) SELECT ... FROM b LEFT JOIN a
     ON a.APK=b.AFK
     WHERE a.SEL=15

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web