为什么我不能做一个完整的外部连接

Question

我有一个db2查询，今天我意识到需要扩展该查询。

我的表使用联接已经相当复杂了，所以我并不想添加联合查询。我想做一个完整的外部连接。

    SELECT 
        a.id
        ,a.city
        ,a.state
        ,case when a.thing = b.thing then a.thing else b.thing end
        ,sum( case when c.thing = 'thing' then 1 else 0 end)
        ,b.id
        ,b.name

    FROM
        a
    INNER JOIN b -- I want to change this to FULL OUTER JOIN
        ON a.id = b.id
    LEFT JOIN c
        ON a.id = c.id
    LEFT JOIN (d
        INNER JOIN e 
            ON d.id = e.id
        )
    WHERE 
        --logic
    GROUP BY
        --for the aggregate functions
    ORDER BY
        --logic

我想是因为另一个左联接。

Answer 1

它可能无法将外部联接与左侧联接组合在一起。您可能必须将外部联接设置为子查询（还添加了一些丢失的别名和ON子句）：

SELECT 
    ab.a_id
    ,ab.city
    ,ab.state
    ,ab.thing
    ,sum( case when c.thing = 'thing' then 1 else 0 end)
    ,ab.b_id
    ,ab.name

FROM
(
    SELECT 
        a.id a_id
        ,a.city
        ,a.state
        ,case when a.thing = b.thing then a.thing else b.thing end thing
        ,b.id b_id
        ,b.name

    FROM
        a
    FULL OUTER JOIN b 
        ON a.id = b.id
)  ab
LEFT JOIN c
    ON ab.id = c.id
LEFT JOIN (d
    INNER JOIN e 
        ON d.id = e.id
    ) f
    ON ...
WHERE 
    --logic
GROUP BY
    --for the aggregate functions
ORDER BY
    --logic