How can I simplify and format nested joins?

Tim 0
Viewed 67

I have a SQL script with nested inner joins in the FROM clause:

SELECT
    ...
FROM            
    (t1
    INNER JOIN
    (t2
    INNER JOIN
    ((t3
    INNER JOIN
    t4
    ON
    t3.ContractID = t4.ContractID AND 
    t3.Line = t4.Line)
    INNER JOIN
    t5
    ON
    t3.TaskID = t5.TaskID AND
    t3.ContractID = t5.ContractID)
    ON 
    t2.TaskID = t5.TaskID)
    ON
    t1.PaymentID = t2.PaymentID AND
    t1.ContractID = t2.ContractID)
    INNER JOIN
    t6
    ON
    t1.Email = t6.Email
WHERE
   (t3.ContractID = 'abc123')
   AND  
   (t2.PaymentID = '12')

I was wondering how to simplify and format nested joins like the one above?

If I remember correctly, all kinds of joins are associative and commutative, and can these properties be used to simplify nested joins?

Thanks.

tsql join sql-server sql
1 Answers

First, 2 mistakes in your code, regarding your subqueries:

  1. When, after the FROM, you open a parenthesis to define a derived table (aka subquery), it has to start with select. You can't just toss a table name in there. This can't work:

select * from (t1)

  1. Also, after your are done and close the parenthesis, you always need to alias the derived table. This will still not work: select * from (select * from t1)

Combining the corrections, this will work: select * from (select * from t1) as t1X

Now that this is out of the way: You don't seem to need a subquery at all. It makes sense to use a join order that is easy to the eye; in your case, the joins are like this: 6<-->1<-->2<-->5<-->3<-->4, so let's do them so:

SELECT
    ...
FROM   
    t6
    INNER JOIN t1 ON t1.Email = t6.Email
    INNER JOIN t2 ON t1.PaymentID = t2.PaymentID AND t1.ContractID = t2.ContractID
    INNER JOIN t5 ON t2.TaskID = t5.TaskID
    INNER JOIN t3 ON t3.TaskID = t5.TaskID AND t3.ContractID = t5.ContractID
    INNER JOIN t4 ON t3.ContractID = t4.ContractID AND  t3.Line = t4.Line
WHERE
   (t3.ContractID = 'abc123')
   AND  
   (t2.PaymentID = '12')
George Menoutis0
Related