oracle - 三个选择语句上的 sql 全外连接

oracle - sql full outer join on three select statements

提问by phileas fogg

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

我想从三个表中选择值。每个表都有一个buyer_entity_id 列。

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:

第三个表将具有相同的选择语句，并且也将连接到buyer_entity_id 值上。但是，当我添加第三个 select 语句时，出现“缺少关键字”错误。下面是我的三路全外连接语句：

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;

回答by RogerG

You can simplify a bit using the WITH clause:

您可以使用 WITH 子句稍微简化一下：

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

用 first_part as ( ( select .... ) 外连接 ( select .... )); select * from first_part external 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.

不过，您可能想要重新审视整体逻辑，看看是否有更好的方法来完成您正在尝试做的事情。多个外部联接通常不是最有效的解决方案。

回答by Shannon Severance

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

错误在这里：order by b.buyer_entity_id) tab2)。您需要一个on子句来指定 tab1 和 tab2 之间的连接条件。order by b.buyer_entity) tab1 on <join condition)

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

一旦确定。您将需要添加full到下一行，以便outer join (select b.buyer_entity_id, count(distinct(a.row_id)) as vers成为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:

联接是成对的表 1 在某些条件下联接到表 2，然后该结果成为下一个联接的左侧。如果 tab1 和 tab3 匹配，而 tab2 不匹配，您希望发生什么？我猜想用 tab1 和 tab3 数据和 tab2 空值生成一行。就像是：

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 byin the subqueries. They are not doing anything for you, since the order is not guaranteed to survive the joins.

另外，请order by在子查询中丢失。他们没有为您做任何事情，因为不能保证订单在连接中继续存在。

回答by Atur

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

一种更快的方法将如此处所述 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;