Why can I not do a full outer join

Question

I have a db2 query and I realized today I need to expand that query.

My table's already pretty complicated with joins, so I don't really want to add a union query. I want to do a full outer join.

At the moment, it's showing

    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

Can someone tell me when I try to do a full outer join, it says 'Full Outer Join is not supported for this query'? And how would I overcome that?

I think it's because of the other left joins.

You have 5 tables, 4 JOIN clauses but only 3 ON clauses. Is this what you meant? — Conrad Frix
I believe so. I left out some c, d, and e Case When and aggregate functions in the select statement. — Emwat
@Emwat . . . Please edit your question and include the query that is generating the error. — Gordon Linoff
Platform, Version and SQL code or SQL State being returned is always good to have. — Charles

D Stanley D Stanley · Accepted Answer · 2015-07-21T15:21:27

It may not be able to combine the outer join with the left joins. You may have to make the outer join a subquery (also added some missing aliases and an ON clause):

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

Why can I not do a full outer join

1 Answers