Join Syntax multiple on
in [Programming SQL]
|
From: tshad on 29 Mar 2010 16:11 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 29 Mar 2010 17:09 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 29 Mar 2010 17:58 "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 29 Mar 2010 18:32 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 29 Mar 2010 19:01
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 |