The logical order of execution plan of the SQL query with more than one join

I know the logical order of execution of the SQL query which is:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

What will happen if there are more than one join in the query for instance if we have a query like this:

SELECT *
FROM user_branch T1
INNER JOIN dimcustomer2 T2
   ON T1.BRANCH_CODE = T2.BRANCH_CODE
INNER JOIN customer_guarantee T3
   ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM

some example data:

customer_guarantee:    CUSTOMER_NUM      BRANCH_CODE
                      -------------------------------
                           A                X
                           B                X
                           C                Y
                           D                Z

 user_branch:           USER_ID          BRANCH_CODE    
                      --------------------------------
                           U1               Y

 dimcustomer2:         CUSTOMER_NUM      BRANCH_CODE      
                      --------------------------------
                           A                Y
                           B                Y
                           C                Y
                           D                Z

How will this execute? Which join will be executed first? And what if there are
different kind of joins in the query? What would be the order of executing joins in that case? Thanks in advance.

One way to determine the logical order of joins is to replace the first inner join in your example with a left outer join:

SELECT *
FROM user_branch T1
LEFT  JOIN dimcustomer2 T2
   ON T1.BRANCH_CODE = T2.BRANCH_CODE
INNER JOIN customer_guarantee T3
   ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM

Let us assume that some rows in T1 have no matches in T2. More specifically, let us assume these are the three tables:
T1 T2 T3

BRANCH_CODE BRANCH_CODE CUSTOMER_NUM CUSTOMER_NUM
----------- ----------- ------------ ------------
11 11 230 120
12 12 235 170
13 15 260 230
14 235
15 245
250
260
270


There are two joins here and two possibilities in which order they are executed.

1. LEFT JOIN, then INNER JOIN

If the left join evaluates first, then its result will have nulls in the T2 columns where T1 rows had no match:

T1.BRANCH_CODE T2.BARNCH_CODE T2.CUSTOMER_NUM
-------------- -------------- ---------------
11 11 230
12 12 235
13 (null) (null)
14 (null) (null)
15 15 260

Joining that result further with T3 using an inner join on a condition that uses a T2 column will eliminate the non-matches – and, therefore, corresponding T1 rows, – because a null cannot satisfy the join's equals condition:

T1.BRANCH_CODE T2.BARNCH_CODE T2.CUSTOMER_NUM T3.CUSTOMER_NUM
-------------- -------------- --------------- ---------------
11 11 230 230
12 12 235 235
15 15 260 260

This way some of T1 rows will be excluded from the final result set.

1. INNER JOIN, then LEFT JOIN

Now if the inner join is executed first, then it will produce a result set containing the rows from T2 and T3 that match the inner join's condition:

T2.BARNCH_CODE T2.CUSTOMER_NUM T3.CUSTOMER_NUM
-------------- --------------- ---------------
11 230 230
12 235 235
15 260 260

When this result set is then outer-joined to T1, T1 being on the outer side, you will get a final result containing all the rows from T1 and those from the T2-T3 inner join that match the outer join condition:

T1.BRANCH_CODE T2.BARNCH_CODE T2.CUSTOMER_NUM T3.CUSTOMER_NUM
-------------- -------------- --------------- ---------------
11 11 230 230
12 12 235 235
13 (null) (null) (null)
14 (null) (null) (null)
15 15 260 260

Therefore, this second interpretation would mean that all T1 rows should be present in the result.

Since these two interpretations give such different results, it is clear that only one can be true. Executing the query, you will see that actually it is the first one. That means that logically, the joins are executed in the order they are specified in the FROM clause.
Syntax variations

Note that the conclusion above applies to the most conventional join syntax, namely this:

FROM
  T1
  ... JOIN T2 ON ...
  ... JOIN T3 ON ...
  ...

Your example matches that pattern, so the conclusion applies to it as well. However, there are variations worth mentioning where our conclusion does not apply, or at least not as straightforwardly.

1. Nested JOIN syntax

Syntactically, a join may be specified inside another join, like this:

FROM
  T1
  JOIN
    T2
    JOIN T3 ON ..
  ON ...

In the above case, JOIN T2 is encountered before JOIN T3. However the former join's declaration is not complete at that point: its ON subclause is the one at the end and is logically evaluated only after the JOIN T3 ON ... part. So in this case, T2 is joined to T3 first, then the result of the join is joined to T1.

You could still argue that our conclusion stands here, although it is not as clear-cut in this situation. We concluded that the joins are evaluated in the order they are specified in the FROM clause. In this example, the first join we encounter as we parse the FROM clause, is not yet completely specified by the time the second one is.

1. Mixing comma joins and conventional joins

Prior to introduction of the explicit JOIN syntax, joins were specified like this:

```
FR

StackExchange Database Administrators Q#251988, answer score: 11