2

I have come across several stored procedures in our "legacy" code that have joins that look like this:

SELECT  *
FROM    TableA
    INNER JOIN TableB
        INNER JOIN TableC ON TableC.TableBId = TableB.TableBId
    ON TableA.TableAId = TableB.TableAId

I would write this query differently, like this:

SELECT  *
FROM    TableA
INNER JOIN TableB ON TableA.TableAId = TableB.TableAId
INNER JOIN TableC ON TableB.TableBId = TableC.TableBId

The results are the same, but I find the second example to be much easier to follow, especially in situations where there are several joins. Is there any advantage to writing JOIN statements with the ON clause "deferred" until after all of the joins have been specified, as in the first example?

AJ.
  • 16,368
  • 20
  • 95
  • 150
  • Was the legacy code ported from Microsoft Access as some point in the past? If I remember correctly, Access had some screwy restriction that would push all the `ON` clauses to the end after all the `JOIN` clauses. There were also parentheses. It's been awhile, so I don't remember the exact syntax. – Shannon Severance May 11 '16 at 18:50
  • @ShannonSeverance Thankfully, I'm not sure if it was ever in Access, but it wouldn't surprise me if it were. I think I remember Access being weird like that. Access.....*shudder* – AJ. May 11 '16 at 18:51

1 Answers1

2

No, predicate order doesn't matter at all to how it's executed. You could put the ON predicates in a WHERE instead, or mix them at random, and end up with the same execution plan. While I'd tread lightly when rewriting code I don't fully understand, in your own code, you should definitely write the way that's readable.

Kevin
  • 5,874
  • 3
  • 28
  • 35
  • http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause. Per this question, your information is incorrect. Can you explain why you think your information is correct? – gh9 May 11 '16 at 18:51
  • OK, I should clarify: in an INNER JOIN, where all predicates are ANDed together, predicate order doesn't matter. In this specific case, I'm pretty sure the parser would even put out a functionally identical abstract syntax tree. The AST might be different if the predicates were in the WHERE, but the execution plan wouldn't be, at least in T-SQL. I can't point to a source because it's not like I just read this somewhere; haven't read a book on SQL in over a decade. – Kevin May 11 '16 at 19:02