SQL multiple outer joins (converting t-sql joins t

问题:

I have 3 tables t1, t2, t3. I want a result set based on these conditions : t1 with outer join on t2 (all rows of t1), t1 with outer join on t3 (all rows of t1) and t2 with outer join on t3 (all rows of t2). How to use these outer 3 joins in a single query? Basically I want to convert a t-sql format query to ANSI format. The original query is something like this

Select * from t1, t2, t3
where t1.col1 *= t2.col1
  and t1.col2 *= t3.col1
  and t2.col2 *= t3.col2

I managed to use first 2 joins as

   Select * 
     from t1
left join t2 on t1.col1 = t2.col1
left join t3 on t1.col2 = t3.col1

This works properly for first 2 conditions. But wasn't able to incorporate the 3rd join. Can anyone please suggest a way to accompolish this? Thanks in advance.

回答1:

From your question I guess you only want rows from t3 that join with the rows from t2 that joined with t1:

SELECT 
    * 
FROM
    t1
    LEFT JOIN t2 ON t1.col1 = t2.col1
    LEFT JOIN t3 ON t1.col2 = t3.col1 AND t2.col2 = t3.col2

This will not include rows from t2 and t3 that join on col2, unless the rows from t2 have already joined with t1 on col1.

回答2:

You can have several variations, all with different result sets. Which of them was the original intention is difficult if not impossible for others to tell:

---

(Variation 1 - Tobsey's query, join first to t2, then to t3, equivalent to):

SELECT 
    * 
FROM
        t1
    INNER JOIN                                --- or LEFT JOIN
        t2 
            ON  t1.col1 = t2.col1
    LEFT JOIN 
        t3 
            ON  t1.col2 = t3.col1 
            AND t2.col2 = t3.col2     --- this line makes the first LEFT join
                                      --- equal to INNER join

---

(Variation 2 - join first to t3, then to t2 ):

SELECT 
    * 
FROM
        t1
    INNER JOIN                                --- or LEFT JOIN
        t3 
            ON  t1.col2 = t3.col1 
    LEFT JOIN 
        t2 
            ON  t1.col1 = t2.col1  
            AND t3.col2 = t2.col2 

---

(Variation 3a - join first t2 to t3, then join t1 to that join ):

SELECT 
    * 
FROM
        t1
    LEFT JOIN
            t2 
        LEFT JOIN 
            t3 
                ON  t2.col2 = t3.col2  
        ON  t1.col1 = t2.col1
        AND t1.col2 = t3.col1 

Variation 3 can have several more sub-variations if you replace the first or the second LEFT join with an INNER join.

My guess is you want variation 3a.