Weird join on on behavior in tsql

Question

I recently found old code that uses JOIN JOIN ON ON instead of the more familiar JOIN ON JOIN ON syntax.

DECLARE @a TABLE (
    val INT
)
DECLARE @b TABLE (
    val INT
)
DECLARE @c TABLE (
    val INT
)


INSERT INTO @a VALUES (1),(2),(4)
INSERT INTO @b VALUES (1),(2),(4)
INSERT INTO @c VALUES (1),(2),(4)


SELECT *
FROM @a as a
join @b as b 
join @c as c
    on b.val = c.val    on a.val = b.val

What I find weird now is that if you consult the query plan, first a and c is joined but there is not even a join condition a.val = c.val.

Can anybody explain the implicit evaluation of this case?

Answer 1

I would say it is query optimizer thing. First your query:

SELECT *
FROM @a as a
join @b as b 
join @c as c
    on b.val = c.val
    on a.val = b.val;

Is the same as:

SELECT *
FROM @a AS A
JOIN ( @b AS B 
       JOIN @c AS C ON B.Val = C.Val
)  ON   A.Val = B.Val;

Second if you use hint:

FORCE ORDER

When you put this query hint on to your query, it tells SQL Server that when it executes the statement to not change the order of the joins in the query. It will join the tables in the exact order that is specified in the query.

Normally the SQL Server optimizer will rearrange your joins to be in the order that it thinks will be optimal for your query to execute.

SELECT *
FROM @a as a
join @b as b 
join @c as c
    on b.val = c.val
    on a.val = b.val  
    OPTION (FORCE ORDER);  

You will get: