From: tshad on
I haven't seen this syntax before:

RIGHT OUTER JOIN dbo.WORKSPACE WS WITH ON dbo.Security.UserID =
WS.CheckoutUserID ON WS.WorkSpaceId = WSEd.WorkSpaceId

Is this the same as:

RIGHT OUTER JOIN dbo.WORKSPACE WS WITH ON dbo.Security.UserID =
WS.CheckoutUserID AND WS.WorkSpaceId = WSEd.WorkSpaceId

Where I replaced the 2nd ON with an AND?

Thanks,

Tom


From: Plamen Ratchev on
No. You did not post the complete FROM clause. This is a case of nesting joins. The ON clause is matched to the closest
JOIN, then the next ON clause to the next JOIN, etc.

For example,

FROM Foo AS WSEd
JOIN dbo.Security
RIGHT OUTER JOIN dbo.WORKSPACE AS WS
ON dbo.Security.UserID = WS.CheckoutUserID
ON WS.WorkSpaceId = WSEd.WorkSpaceId

In this case the first ON clause is matched to the join between Security and Workspace, then the next ON clause to Foo
and Workspace.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:qbednTXjT_K6iizWnZ2dnUVZ_o8AAAAA(a)speakeasy.net...
> No. You did not post the complete FROM clause. This is a case of nesting
> joins. The ON clause is matched to the closest JOIN, then the next ON
> clause to the next JOIN, etc.
>
> For example,
>
> FROM Foo AS WSEd
> JOIN dbo.Security
> RIGHT OUTER JOIN dbo.WORKSPACE AS WS
> ON dbo.Security.UserID = WS.CheckoutUserID
> ON WS.WorkSpaceId = WSEd.WorkSpaceId
>
> In this case the first ON clause is matched to the join between Security
> and Workspace, then the next ON clause to Foo and Workspace.
>

So how does this work with the question I was asking before about which
table or virtual table a join was referencing?

1) first ON joining Security and Workspace tables.
2) 2nd ON joins the Workspace table and the Foo table

OR

3) 2nd ON joins the results from the previous OUTER JOIN (#1) of Security
and Workspace and Foo

Could I have done:

FROM Foo AS WSEd
JOIN dbo.Security
RIGHT OUTER JOIN dbo.WORKSPACE AS WS
ON WS.WorkSpaceId = WSEd.WorkSpaceId
ON dbo.Security.UserID = WS.CheckoutUserID

Where I reversed the order of the ONs and gottent the same result?

Thanks,

Tom

> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Tom Cooper on
Although it is the almost universal practice to place each ON with it's
corresponding join, it is not required. The rule is the you read until you
find an ON, then use that ON to match the last unused INNER or OUTER JOIN
(CROSS JOINS do not have ON's). So

FROM Foo AS WSEd
JOIN dbo.Security
RIGHT OUTER JOIN dbo.WORKSPACE AS WS
ON dbo.Security.UserID = WS.CheckoutUserID
ON WS.WorkSpaceId = WSEd.WorkSpaceId

would first do the

RIGHT OUTER JOIN dbo.WORKSPACE AS WS
ON dbo.Security.UserID = WS.CheckoutUserID

and that result would contain all rows from dbo.WORKSPACE even if there were
no matches. It would then take the result and do the INNER join to Foo.
Since this is an INNER JOIN, only matching rows would be kept.

So the above is equivalent to

FROM dbo.Security
RIGHT OUTER JOIN dbo.WORKSPACE AS WS
ON dbo.Security.UserID = WS.CheckoutUserID
INNER JOIN Foo AS WSEd
ON WS.WorkSpaceId = WSEd.WorkSpaceId

Since the latter version is (to me and I think most SQL programmers) much
easier to read and therefore easier to maintain, I consider it to be by far
the better version.

Tom
"tshad" <t(a)dslextreme.com> wrote in message
news:eQYTtr4zKHA.1236(a)TK2MSFTNGP06.phx.gbl...
>
> "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
> news:qbednTXjT_K6iizWnZ2dnUVZ_o8AAAAA(a)speakeasy.net...
>> No. You did not post the complete FROM clause. This is a case of nesting
>> joins. The ON clause is matched to the closest JOIN, then the next ON
>> clause to the next JOIN, etc.
>>
>> For example,
>>
>> FROM Foo AS WSEd
>> JOIN dbo.Security
>> RIGHT OUTER JOIN dbo.WORKSPACE AS WS
>> ON dbo.Security.UserID = WS.CheckoutUserID
>> ON WS.WorkSpaceId = WSEd.WorkSpaceId
>>
>> In this case the first ON clause is matched to the join between Security
>> and Workspace, then the next ON clause to Foo and Workspace.
>>
>
> So how does this work with the question I was asking before about which
> table or virtual table a join was referencing?
>
> 1) first ON joining Security and Workspace tables.
> 2) 2nd ON joins the Workspace table and the Foo table
>
> OR
>
> 3) 2nd ON joins the results from the previous OUTER JOIN (#1) of Security
> and Workspace and Foo
>
> Could I have done:
>
> FROM Foo AS WSEd
> JOIN dbo.Security
> RIGHT OUTER JOIN dbo.WORKSPACE AS WS
> ON WS.WorkSpaceId = WSEd.WorkSpaceId
> ON dbo.Security.UserID = WS.CheckoutUserID
>
> Where I reversed the order of the ONs and gottent the same result?
>
> Thanks,
>
> Tom
>
>> --
>> Plamen Ratchev
>> http://www.SQLStudio.com
>
>

From: Plamen Ratchev on
tshad wrote:
>
> 3) 2nd ON joins the results from the previous OUTER JOIN (#1) of Security
> and Workspace and Foo
>

This is more accurate description of how it will work. Tom Cooper did a good explanation of the details.

> Could I have done:
>
> FROM Foo AS WSEd
> JOIN dbo.Security
> RIGHT OUTER JOIN dbo.WORKSPACE AS WS
> ON WS.WorkSpaceId = WSEd.WorkSpaceId
> ON dbo.Security.UserID = WS.CheckoutUserID
>
> Where I reversed the order of the ONs and gottent the same result?
>

No, you should get an error if you do that. The ON clause does not match the closest join tables and you will get
invalid column error.

--
Plamen Ratchev
http://www.SQLStudio.com