CODEWITHSUNDEEP
sql-servert-sqljoin
rmutalik
rmutalik

Reputation: 2045

How do multiple on statements work in a single inner join?

There are two ON clauses one after another, like so:

select * from foo
left join b on b.ID = foo.pID
left join c
inner join foo as f
ON b.ID = f.pProject
ON c.Name = f.Hostname

Does it mean that all 3 tables are being joined together?

Upvotes: 5

Views: 2274

Answers (2)

paparazzo
paparazzo

Reputation: 45096

select * 
from foo
left  join b 
        on b.ID = foo.pID
left  join c
inner join foo as f
        ON f.pProject = b.ID  
        ON c.Name = f.Hostname

I have seen this used before but not in this same way. I forget the purpose but I know it had a good use.

In this case I think it joins on f.pProject = b.ID first so it can get the value for f.Hostname.

Upvotes: 0

Sentinel
Sentinel

Reputation: 6449

It means that the joins are nested such that the inner join between f and b happens before they are collectively outer joined to c. However I think you may have some lines transposed. This could be rewritten with some indentation and parenthesis to maybe make it clearer.

select *
  from foo 
  left join b
    on b.ID = foo.pID
  left join (c
       join foo as f
         ON c.Name = f.Hostname
       )
    ON b.ID = f.pProject

You can think of it like the JOIN key word acts as an open parenthesis and the ON key word acts as a closing parenthesis in nested matching pairs. The order of precedence or scoping on this is such that the nested joins can't access the the enclosing joins until the nested joins have been completed. As such your final two ON statements as written would likely throw an error until they are transposed as I did above.

Upvotes: 4

Related Questions