Greetings to all doing a query a question comes to me I have seen that the order in which a LEFT JOIN is done varies, now well it is usually done like this:
SELECT * FROM tabla_1 LEFT JOIN tabla_2 ON tabla_1.tabla2_id = tabla_2.id
to put it in some way, it is the most conventional or traditional to find it in that way, but there is also the other variant:
SELECT * FROM tabla_1 LEFT JOIN tabla_2 ON tabla_2.id = tabla_1.tabla2_id
As we can see, after ON , the order of the first statement varies with respect to the second.
The question
Does this order infer anything in a LEFT JOIN ?
The order it infers is that of the tables in the join since taking into account the following data:
Which you can see here: Sql Fiddle, build -> run sql
If we run:
We will see that the output is:
If we execute reversing the order of the join:
We change the output to:
Here it is obvious why since first this table1 then table2 (the columns are shown in that order and in the second execution they change).
The thing is that this is not the important thing about the order, but rather we have to take into account set theory (on which SQL joins are based) because when we indicate that table1 will have a left join with table2 we say that we will select all the records of table1 (table on the left) plus the records of the right table that are related to it. Said in set theory we will take the elements of the set on the left plus those on the right that intersect:
So, again, when executing the query:
The output would be:
Expected result since we select all the records of table_1 (which is only one) plus the records of table_2 where it intersects with table_1 (also only one because table_2.id_table1 has the id of the only record of table_1), therefore a single row is returned.
Now by reversing the order of the join executing the query:
The output of it is:
Here again, the order of the columns is not what is important, but it brings us another row, taking into account what was explained previously: "we will select all the records of table1 (table on the left) plus the records of the right table that are relate to it." , in addition to the fact that in this case table_1 (left) is now table_2, it brings us two records from table_2 (left), plus one record from table_1 (right) that intersects.
You will notice that the second row does not bring results in the columns of table_1 (right), since for the second record there is no element (row) that intersects but since the query must bring all those from the left then it brings the columns of table_2 and the columns of table_1 leave them null.
This is useful in situations where we want to consult, for example, the list of customers and their purchased products, but if a customer does not have products, the query returns it, the query would be:
Or also if we want to search for customers without products:
Greetings.
The order of the statements after ON does NOT matter at all, that is, both statements you wrote are correct. What it does influence is the order of the tables found before and after LEFT, since it will depend on which table will be the "base table" for the LEFT join.