ON clauses of View do not appear in order with JOINs

Question

In SSMS 2012 I'm looking at a large view created in MS Access. When I script the view to a query window the FROM clause looks like this:

FROM         dbo.oa_projecttask_type RIGHT OUTER JOIN
    dbo.oa_project_task RIGHT OUTER JOIN
    dbo.[Functional Area] RIGHT OUTER JOIN
    dbo.oa_category ON dbo.[Functional Area].[Product Code] = dbo.oa_category.name RIGHT OUTER JOIN
    dbo.oa_project RIGHT OUTER JOIN
    dbo.vw_People_All_Ever RIGHT OUTER JOIN
    dbo.oa_task ON dbo.vw_People_All_Ever.[User ID] = dbo.oa_task.user_id ON dbo.oa_project.id = dbo.oa_task.project_id ON 
    dbo.oa_category.id = dbo.oa_task.category_id ON dbo.oa_project_task.id = dbo.oa_task.project_task_id ON 
    dbo.oa_projecttask_type.id = dbo.oa_task.projecttask_type_id LEFT OUTER JOIN
    dbo.upl_approval_status RIGHT OUTER JOIN
    dbo.oa_timesheet ON dbo.upl_approval_status.Code = dbo.oa_timesheet.status ON dbo.oa_task.timesheet_id = dbo.oa_timesheet.id
WHERE     (CONVERT(datetime, oa_task.created) > GETDATE() - 366) AND (oa_task.deleted IS NULL)

When I format the clause so the JOIN's and ON's are on separate lines, it looks like this:

FROM oa_projecttask_type 
   RIGHT OUTER JOIN oa_project_task 
   RIGHT OUTER JOIN [Functional Area] 
   RIGHT OUTER JOIN oa_category 
      ON [Functional Area].[Product Code] = oa_category.name 
   RIGHT OUTER JOIN oa_project 
   RIGHT OUTER JOIN vw_People_All_Ever 
   RIGHT OUTER JOIN oa_task 
      ON vw_People_All_Ever.[User ID] = oa_task.user_id 
      ON oa_project.id = oa_task.project_id 
      ON oa_category.id = oa_task.category_id 
      ON oa_project_task.id = oa_task.project_task_id 
      ON oa_projecttask_type.id = oa_task.projecttask_type_id 
   LEFT OUTER JOIN upl_approval_status 
   RIGHT OUTER JOIN oa_timesheet 
      ON upl_approval_status.Code = oa_timesheet.status 
      ON oa_task.timesheet_id = oa_timesheet.id
WHERE     (CONVERT(datetime, oa_task.created) > GETDATE() - 366) AND (oa_task.deleted IS NULL)

Many of the JOINs do not have ON statements, yet the view compiles and returns data without errors. What does a RIGHT OUTER JOIN without an ON clause mean -- is it a Cartesian join?

UPDATE: Based on this question -- Wierd SQL Server view definition -- and its links, we cannot simply rearrange the JOINs/ONs to more sane order -- the order affects the logic of the retrieval. I'm just going to rewrite this query with easier-to-understand logic. Thanks to @MartinSmith and all!

The position of the `on` clause controls the logical join order and the virtual tables participating in the join. I can't be bothered to rewrite your lengthy query but all the info you need can be found in the links in this answer. http://stackoverflow.com/a/7312901/73226 — Martin Smith, Aug 22 '14 at 23:50

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

If you double check you will see that every Right Outer Join has his ON statement, just not in the place.
I guess that all joins will find their own ONs as ON statements are required for RIGHT/LEFT Outer Join to work properly.

More about this topic you can find HERE as Martin Smith wrote in comments.

edited May 23 '17 at 10:25

Community

1
1

answered Aug 22 '14 at 22:52

Hoh

1,196
1
12
30

The placement of the `on` clause controls the logical join order and changes the semantics. You can see a simple example of this here. http://stackoverflow.com/a/7313507/73226 – Martin Smith Aug 22 '14 at 22:56
Well I suppose right as far as it goes but missing the most important point that it changes the virtual tables that participate in the join. – Martin Smith Aug 22 '14 at 23:01
@MartinSmith It's no big deal, but I disagree with your marking as duplicate. While your linked question is a very simple explanation of joins, there is absolutely *no* mention of ONs appearing in *any* order in the WHERE clause, which is a surprise feature of SQL Server...plus there is further screwiness in my Clarification edit of the question. – user906802 Aug 22 '14 at 23:40
@user906802 The question is a duplicate. Admittedly my answer there doesn't do much explaining though. You need to read the links in it really. Start with the Itzik one but that contains a few errors corrected in the follow up article. Specifically the original article makes it appear as though brackets are required when the placement of the `on` clause is all that is needed. – Martin Smith Aug 22 '14 at 23:53
@MartinSmith Thanks for your *quick* reply. We can agree to disagree...but lord help the poor SQL guy who has to deal with Access-generated code! However I can't find an "Itzik" link in the right-join question -- I find only 2 links, one to a Susan Harkins post and another to a MySQL doc page. Are you sure you posted the right reference? [This](http://stackoverflow.com/questions/7311956/weird-sql-server-view-definition) link appeared briefly in the close-duplicate title of this question and it seems fairly relevant. – user906802 Aug 23 '14 at 00:14
That was the question that this was closed as dupe of. Not the right join one. I just mentioned that as it contains a simple three table example. – Martin Smith Aug 23 '14 at 00:25
@MartinSmith Thanks again. The Access query is a lot more complicated than the Itzik example. If the " – user906802 Aug 23 '14 at 00:50
@MartinSmith Thanks again. The Access query is a lot more complicated than the Itzik example. If the "chiastic" relation between JOINs and ONs applies in this case, it is a lot more complicated than 1-2-3/3-2-1. I tried to apply the indenting techniques in the [Wierd SQL Server view definition(http://stackoverflow.com/questions/7311956/weird-sql-server-view-definition) question and failed to make sense of it. I will edit out my rearrangement of the JOINs/ONs in the question and throw my hands up. Ugh. – user906802 Aug 23 '14 at 01:03

ON clauses of View do not appear in order with JOINs

1 Answers1