In an sql query, is there an order to declare the join?

Question

In an sql query, is there an order to declare the join?

Navigation

#1 by (1 votes)

2

I have always had the doubt if it is the same or if it has consequences to make a query of the following forms, or if there is some standard to follow when establishing the order of a join depending on whether or not the table is has the foreign key

__________               ___________
Alumno                   Escuela
——————                   ——————
Id_alumno                Id_escuela
Nombre                   Nombre
Id_escuela               ——————
——————

form 1:

Select e.Nombre, a.id_alumno, a.nombre
From escuela e, alumno a
Where a.Id_escuela = e.Id_escuela

Form 2:

Select e.Nombre, a.id_alumno, a.nombre
From alumno a, escuela e
Where e.Id_escuela, a.Id_escuela

And then the same if you were using the reserved word JOIN

sql

asked by dante198712 19.12.2017 в 13:49

source

1 answer

.Net Dll with Web Service in VB6 Select all the clients whose name is repeated

score 1 · Accepted Answer

The order is not important because the vast majority of modern databases reorder the joins automatically according to what is most optimal for the query. You can check this yourself by examining the execution plan of your different versions of the query. You will see that in all cases, the execution plan is the same.

For this reason, unless the order affects the result of the query itself (which is not the case in your example, but it would be if it were a LEFT JOIN for example), simply write the query so that it is readable and easy to understand. The database will be responsible for reorganizing the clauses of your query automatically to maximize performance.

With this in mind, I recommend that you avoid using the implicit joins notation. Rather, it favors explicit joins, which are easier to understand, especially when the query involves several tables with several joins:

select e.nombre, a.id_alumno, a.nombre
  from escuela e
  join alumno a on a.id_escuela = e.id_escuela