oracle - sql full outer join on three select statements

Question

I want to select values from three tables. Each table has an buyer_entity_id column.

I've come up with the syntax to do an outer join on two of the tables, but how to add the third table is eluding me.

Here's the statement for the two table join which works exactly how I want it to work:

select * from (select  b.buyer_entity_id, count(distinct(a.row_id)) as imps 
from imps a, anl_line b
where b.line_item_id=a.buyer_line_id 
and a.entity_id=3
group by b.buyer_entity_id
order by b.buyer_entity_id) tab1
full outer join (select b.buyer_entity_id, count(distinct(a.row_id)) as clicks 
from clicks a, anl_line b 
where a.buyer_line_id=b.line_item_id 
and a.entity_id=3
group by b.buyer_entity_id 
order by b.buyer_entity_id) tab2
on tab1.buyer_entity_id = tab2.buyer_entity_id;

The third table would have an identical select statement and would also be joined on the buyer_entity_id value as well. However, when I add the third select statement I'm getting a "missing keyword" error. Below is my three way full outer join statement:

select * from ((select  b.buyer_entity_id, count(distinct(a.row_id)) as imps 
from imps_table a, line_table b
where b.line_item_id=a.buyer_line_id 
and a.entity_id=3
group by b.buyer_entity_id
order by b.buyer_entity_id) tab1
full outer join (select b.buyer_entity_id, count(distinct(a.row_id)) as clicks 
from clicks_table a, line_table b 
where a.buyer_line_id=b.line_item_id 
and a.entity_id=3
group by b.buyer_entity_id 
order by b.buyer_entity_id) tab2)
outer join (select  b.buyer_entity_id, count(distinct(a.row_id)) as vers 
from vers_table a, line_table b
where b.line_item_id=a.buyer_line_id 
and a.entity_id=3
group by b.buyer_entity_id
order by  b.buyer_entity_id) tab3
on tab1.buyer_entity_id = tab2.buyer_entity_id and tab2.buyer_entity_id=tab3.buyer_entity_id;

Answer 1

The error is here: order by b.buyer_entity_id) tab2). You need an on clause to specify the join condition between tab1 & tab2. order by b.buyer_entity) tab1 on <join condition)

Once that is fixed. You will need to add full to the next line so that outer join (select b.buyer_entity_id, count(distinct(a.row_id)) as vers becomes full outer join (select b.buyer_entity_id, count(distinct(a.row_id)) as vers.

That covers syntax issues, but not semantic issues.

Joins are pair wise table 1 is joined to table 2 on some condition, then the results of that becomes the left side to the next join. What do you want to have happen if tab1 and tab3 match, but tab2 does not? I'd guess produce a row with tab1 and tab3 data and tab2 nulls. Something like:

select * 
from (select  b.buyer_entity_id, count(distinct(a.row_id)) as imps 
        from imps_table a, line_table b
        where b.line_item_id=a.buyer_line_id 
        and a.entity_id=3
        group by b.buyer_entity_id) tab1
full outer join (select b.buyer_entity_id, count(distinct(a.row_id)) as clicks 
        from clicks_table a, line_table b 
        where a.buyer_line_id=b.line_item_id 
        and a.entity_id=3
        group by b.buyer_entity_id) tab2
    on tab1.buyer_entity_id = tab2.buyer_entity_id
full outer join (select  b.buyer_entity_id, count(distinct(a.row_id)) as vers 
        from vers_table a, line_table b
        where b.line_item_id=a.buyer_line_id 
        and a.entity_id=3
        group by b.buyer_entity_id) tab3
    on tab3.buyer_entity_id in (tab1.buyer_entity_id, tab2.buyer_entity_id)
order by coalesce(tab1.buyer_entity_id
    , tab2.buyer_entity_id
    , tab3.buyer_entity_id);

Also, please lose the order by in the subqueries. They are not doing anything for you, since the order is not guaranteed to survive the joins.

Answer 2

You can simplify a bit using the WITH clause:

With first_part as ( ( select .... ) outer join ( select .... )); select * from first_part outer join ( select .... );

You may want to revisit the overall logic though and see if there's a better way to accomplish what you're trying to do. Multiple outer joins aren't often the most efficient solution.

Answer 3

A faster method would be as stated here https://forums.oracle.com/thread/2388229

SELECT       COALESCE (a.id, b.id, c.id)     AS common_id
,       NVL2 (a.id, 1, NULL)           AS table_a_flag
,       NVL2 (b.id, 1, NULL)           AS table_b_flag
,       NVL2 (c.id, 1, NULL)           AS table_c_flag
FROM              table_a  a
FULL OUTER JOIN  table_b  b  ON  b.id  =           a.id
FULL OUTER JOIN      table_c  c  ON  c.id  = COALESCE (a.id, b.id)
ORDER BY  common_id;