Rando Jones Rando Jones - 3 months ago 11
SQL Question

SQL - Consecutive "ON" Statements

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) 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.

Comments