Re: Non-Relating Join Criteria

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


.

Relevant Pages

  • Re: Select from 4 tables with COUNT(*)
    ... I only updated the IPs table to include a device varchar ... INNER JOIN dbo.Networks n ... Company_ID INT PRIMARY KEY, ... Company_Name VARCHARNOT NULL DEFAULT 'unknown', ...
    (microsoft.public.sqlserver.programming)
  • Non-Relating Join Criteria
    ... Transact SQL apparently supports one-sided critera, ... CREATE TABLE a (APK int PRIMARY KEY, SEL int, ... ... FROM a INNER JOIN b ...
    (comp.databases.ms-sqlserver)
  • Re: SET ANSI_NULLS OFF
    ... Read SET ANSI_NULLS article in the BOL ... > a int primary key not null, ... > Why the following query does not return anything? ... > inner join t2 ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem With Nested Joins
    ... FROM tblApartments AS A ... INNER JOIN tblResidents AS R ... > The problem is not so much an issue if a resident has no numbers listed (I> think this is what OUTER JOINS really address), but if they only have> numbers other than 'Work' listed. ...
    (microsoft.public.access.formscoding)
  • Re: Problem With Nested Joins
    ... FROM tblApartments AS A ... INNER JOIN tblResidents AS R ... > The problem is not so much an issue if a resident has no numbers listed (I> think this is what OUTER JOINS really address), but if they only have> numbers other than 'Work' listed. ...
    (microsoft.public.access.queries)