2

This is not a duplicate of this Q&A because the question and answers here concerns the table mentioned in the FROM clause. Which mine doesn't.

Assuming the table in the FROM clause is always the same and I'm never going to change it. Does it matter which order I add my joins?

I am using an in-house built query builder. (Yes I know there are things out there already but that's out of scope for the question).

I want to be able to set some of the joins at the beginning of my script and some later based on conditionals, the query builder adds them to the query from the top down. Will the SQL engine optimize the order of the joins anyway, regardless of their order in the query?

example: 

SELECT a.col1, d.col2, c.col1, b.col3
FROM table1 A 
INNER JOIN table2 B
    ON B.a_id = A.id
LEFT JOIN table3 C 
    ON C.id = A.c_id
LEFT JOIN table4 D 
    ON D.id = C.d_id;

SELECT a.col1, d.col2, c.col1, b.col3
FROM table1 A 
LEFT JOIN table4 D 
    ON D.id = C.d_id
INNER JOIN table2 B
    ON B.a_id = A.id
LEFT JOIN table3 C 
    ON C.id = A.c_id;

Here you can see that I have declared the join for table4 D before the join for it's dependent table is declared in the script (C). Does this matter?

Community
  • 1
  • 1
Adam Copley
  • 1,495
  • 1
  • 13
  • 31
  • 1
    "Will the SQL engine optimize the order of the joins anyway, regardless of their order in the query?" Yes unless you use STRAIGHT_JOIN – Raymond Nijland May 04 '17 at 12:30
  • 1
    If anyone is going to downvote the question please give feedback on how I can improve it. – Adam Copley May 04 '17 at 12:40
  • Have you tried your second query? In the past I get a compiler error when I reference a table before it's been declared... I'll check mySQL in a sec... – xQbert May 04 '17 at 13:00
  • mySQL will throw an error on 2nd query. `Unknown column 'C.d_id' in 'on clause'` So yes... the compiler doesn't look ahead to see if it's been referenced later.. It only knows the order first then it tries to figure out which method of joining is best. [SQLFiddle](http://sqlfiddle.com/#!9/54d932/2/0) to address question of: Will the SQL engine optimize the order of the joins anyway, regardless of their order in the query? yes it will optimize order; but they must be declared in proper order 1st;or it will not compile. – xQbert May 04 '17 at 13:16
  • Thanks for that xQbert. If you put it into an answer I'll gladly accept. – Adam Copley May 04 '17 at 13:18
  • Great question. I like the ones that make me think! – xQbert May 04 '17 at 13:22

1 Answers1

2

Simple answer: No you can't reference a table object/alias before the object has been declared.

mySQL will throw an error on 2nd query. `Unknown column 'C.d_id' in 'on clause'

So yes... the compiler doesn't look ahead to see if it's been referenced later.. It only knows the order first then it tries to figure out which method of joining is best.

SQLFiddle

*To address question of: Will the SQL engine optimize the order of the joins anyway, regardless of their order in the query? *

Yes it would optimize the order; but the "FROM" order can't include a reference to a table before it's been declared or the query will not compile. (See error above and link for example)

xQbert
  • 34,733
  • 2
  • 41
  • 62