如何使用旧式Oracle语法执行完全外连接

Question

我有两个表，每个表都有相同的列，项目代码和数量：

TABLE A                    TABLE B
--------------             -------------
X    2                      X   1
Y    1                      S   2
Z    5                      Z   5

我希望得到的结果是这样的：

Table C
---------------
X  2  1
Y  1  0
S  0  2

我只需要两个表中qty不同的项(包括应该显示为零的空值。

注意:我使用的是Oracle8，所以我不能使用ANSI完全外连接。

Answer 1

编辑，由于该问题特定于不使用ANSI语法的Oracle 8，因此应执行以下操作：

select col1, 
  nvl(a_col2, 0) as a_col2, 
  nvl(b_col2, 0) as b_col2
from 
( 
  select a.col1, a.col2 as a_col2, b.col2 as b_col2 
  from TableA a, TableB b
  where a.col1 = b.col1(+)
  union
  select b.col1, a.col2 as a_col2, b.col2 as b_col2 
  from TableA a, TableB b
  where a.col1(+) = b.col1 
)      
where a_col2 <> b_col2
  or (a_col2 is null or b_col2 is null)

参见SQL Fiddle with Demo。这将返回：

| COL1 | A_COL2 | B_COL2 |
--------------------------
|    S |      0 |      2 |
|    X |      2 |      1 |
|    Y |      1 |      0 |

如果您使用的Oracle版本支持ANSI语法，则可以使用以下FULL OUTER JOIN

select 
  coalesce(a.col1, b.col1) col1, 
  coalesce(a.col2, 0) a_col2, 
  coalesce(b.col2, 0) b_col2
from tablea a
full outer join tableb b
  on a.col1 = b.col1
where a.col2 <> b.col2
  or (a.col2 is null or b.col2 is null);

请参阅SQL Fiddle with Demo

Answer 2

查询的另一种编写，应该可以在8和(可能是更早的版本)中工作。

它既不使用FULL JOIN，也不使用可怕的(+)连接语法，所以即使升级不推荐使用它，它也应该可以工作。

假设表上没有空值，那么您也不需要COALESCE()或NVL()：

SELECT  a.col1, 
        a.col2 AS a_col2, 
        b.col2 AS b_col2
FROM    TableA a, TableB b
WHERE   a.col1 = b.col1
  AND ( a.col2 <> b.col2
     OR a.col2 IS NULL
     OR b.col2 IS NULL
      )

UNION ALL

SELECT  col1, col2, 0
FROM    TableA a
WHERE   NOT EXISTS
        ( SELECT  *
          FROM    TableB b
          WHERE   a.col1 = b.col1
        )

UNION ALL

SELECT  col1, 0, col2
FROM    TableB b
WHERE   NOT EXISTS
        ( SELECT  *
          FROM    TableA a 
          WHERE   a.col1 = b.col1
        ) ; 

上的测试

Answer 3

select code, nvl(a.qty,0) a, nvl(b.qty,0) b
from tableA a full join tableB b using(code)
where decode(a.qty, b.qty, 0) is null

fiddle