SQL - Consecutive "ON" Statements

Question

As I was cleaning up some issues in an old view in our database I came across this "strange" join condition:

from
    tblEmails [e]
    join tblPersonEmails [pe]
        on (e.EmailID = pe.EmailID)
    right outer join tblUserAccounts [ua]
    join People [p]
        on (ua.PersonID = p.Id)
    join tblChainEmployees [ce]
        on (ua.PersonID = ce.PersonID)
        on (pe.PersonID = p.Id)

Table tblUserAccounts is referenced as a right outer join, but the on condition for it is not declared until after tblChainEmployees is referenced; then there are two consecutive on statements in a row.

I couldn't find a relevant answer anywhere on the Internet, because I didn't know what this kind of join is called.

So the questions:

1. Does this kind of "deferred conditional" join have a name?
2. How can this be rewritten to produce the same result set where the on statements are not consecutive?
3. Maybe this is a "clever" solution when there has always been a simpler/clearer way?

Answer 1

(1) This is just syntax and I've never heard of some special name. If you read carefully this MSDN article you'll see that (LEFT|RIGHT) JOIN has to be paired with ON statement. If it's not, expression inside is parsed as <table_source>. You can put parentheses to make it more readable:

from
    tblEmails [e]
    join tblPersonEmails [pe]
        on (e.EmailID = pe.EmailID)
    right outer join
    (
        tblUserAccounts [ua]
        join People [p]
            on (ua.PersonID = p.Id)
        join tblChainEmployees [ce]
            on (ua.PersonID = ce.PersonID)
    ) on (pe.PersonID = p.Id)

(2) I would prefer LEFT syntax, with explicit parentheses (I know, it's a matter of taste). This produces the same execution plan:

FROM tblUserAccounts ua
JOIN People p ON ua.PersonID = p.Id
JOIN tblChainEmployees ce ON ua.PersonID = ce.PersonID
LEFT JOIN
(
    tblEmails e
    JOIN tblPersonEmails pe ON e.EmailID = pe.EmailID
) ON pe.PersonID = p.Id

(3) Yes, it's clever, just like some C++ expressions (i.e. (i++)*(*t)[0]<<p->a) on interviews. Language is flexible. Expressions and queries can be tricky, but some 'arrangements' lead to readability degradation and errors.