Home / Questions / 229701
Accepted
Aetos2501
Asked: 2020-01-15 16:10:51 +0800 CST 2020-01-15 16:10:51 +0800 CST

What is the correct execution order of sql statements?

  • 772

I have an existential doubt about the order of execution of SQL statements , specifically in the union of related tables. FROMFirst the after the clause is executed ONand after INNER JOINeach table?. If someone could enlighten me as I frequently get wrong results in my queries and I want to understand this part of the order of execution. Thank you all in advance.

    SELECT h.SalesOrderID,
           h.OrderDate,
           h.SalesOrderNumber,
           h.TotalDue,
           d.ProductID,
           d.LineTotal,
           p.Name
      FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
        ON h.SalesOrderID = d.SalesOrderID
INNER JOIN AdventureWorks.Production.Product AS p
        ON d.ProductID = p.ProductID
sql

1 Answers

  1. Best Answer

    The best way to understand both the order and the cost of a query is through the Execution Plan tool provided by the Sql Management Studio. Here you have the official documentation, but I put a simple example of a query similar to yours for you to see:

    SELECT  j.id AS idJornada, j.nombre, j.descripcion, j.color,
        s.id AS idSesion, s.horaInicio, s.horaFinal
FROM Jornadas j 
LEFT JOIN JornadaSesiones js ON j.id = js.idJornada
LEFT JOIN Sesiones s ON s.id = js.idSesion
WHERE s.id = 1

    Activating the Execution Plan and after executing the query I see the following (read from right to left):

    Execution plan

    As you can see, it first joins "Jornadas" and "JornadaSesiones", to the result it applies the join together with "Sessiones" after filtering by the WHERE search (if you look closely, it says Search in index instead of Exam in index) and on the result applies the SELECT.

    This tool gives much more of itself, but it can be used to get an idea of ​​what you are asking for.

    • 2