Join Syntax multiple on
in [Programming SQL]
|
From: tshad on 29 Mar 2010 19:56 "Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:ej4jD$4zKHA.6112(a)TK2MSFTNGP05.phx.gbl... > 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. That makes sense to me. I had never seen the other syntax. Why would you even think of doing it that way. As you say, it is easier to read and maintain. Much more logical. Thanks, Tom > > 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 >> >> > |