sql - SQL中交集的补充

Question

我正在使用 Oracle SQL，我有一个关于 join 命令的基本问题。

我有5张 table 。它们中的每一个都具有与主键相同的列: ID (int) 。让我们看看以下查询:

select count(*) from table_a - 100 records
select count(*) from table_c - 200 records
select count(*) from table_c - 150 records
select count(*) from table_d - 100 records
select count(*) from table_e - 120 records

select * -- 88 records
 from table_a a
  inner join table b
    on a.id = b.id
  inner join table c
    on a.id = c.id
  inner join table d
    on a.id = d.id
  inner join table e
    on a.id = e.id

在这种情况下，如果其中一个表不包含特定 ID(即使包含其余记录)，则许多记录将无法包含在输出中。我怎么知道这些“坏”记录是什么？它实际上是我认为的交叉点的补充。

我想知道每个案例的有问题的记录和表格是什么。例如:ID 123 是“坏”记录，因为它不包含在 table_c 中，但包含在其余表中。 ID 321 是有问题的记录，因为它包含在除 table_d 之外的所有表中。

Answer 1

您可能正在所有表之间寻找 symmetric difference 。

要解决这类问题而又不太聪明，您需要一个 FULL OUTER JOIN ... USING :

SELECT id
    FROM table_a
    FULL OUTER JOIN table_b USING(id) 
    FULL OUTER JOIN table_c USING(id) 
    FULL OUTER JOIN table_d USING(id) 
    FULL OUTER JOIN table_e USING(id) 
WHERE table_a.ROWID IS NULL
   OR table_b.ROWID IS NULL
   OR table_c.ROWID IS NULL
   OR table_d.ROWID IS NULL
   OR table_e.ROWID IS NULL;

FULL OUTER JOIN 将返回所有满足连接条件的行(如普通的 JOIN )以及所有没有对应行的行。 USING 子句在 equijoin 列上嵌入了一个隐式的 COALESCE。

另一种选择是使用 anti-join :

SELECT id
    FROM table_a
    FULL OUTER JOIN table_b USING(id) 
    FULL OUTER JOIN table_c USING(id) 
    FULL OUTER JOIN table_d USING(id) 
    FULL OUTER JOIN table_e USING(id) 
WHERE id NOT IN (
    SELECT id
        FROM table_a
        INNER JOIN table_b USING(id) 
        INNER JOIN table_c USING(id) 
        INNER JOIN table_d USING(id) 
        INNER JOIN table_e USING(id) 
)

基本上，这将构建所有集合减去所有集合的交集的联合。

在图形上，您可以比较 INNER JOIN 和 OUTER JOIN(在 3 个表上仅为便于表示):



鉴于该测试用例:

ID    TABLE_A TABLE_B TABLE_C TABLE_D TABLE_E
1     *       -       -       -       -
2     -       *       *       *       *
3     *       -       -       *       -
4     *       *       *       *       *

_{* value in the table - missing entry}

两个查询都会产生:

ID
1
3
2

如果您想要表格结果，您可以通过添加一堆 CASE 表达式来调整其中一个查询。类似的东西:

SELECT ID,
    CASE when table_a.rowid is not null then 1 else 0 END table_a,
    CASE when table_b.rowid is not null then 1 else 0 END table_b,
    CASE when table_c.rowid is not null then 1 else 0 END table_c,
    CASE when table_d.rowid is not null then 1 else 0 END table_d,
    CASE when table_e.rowid is not null then 1 else 0 END table_e
FROM table_a
    FULL OUTER JOIN table_b USING(id) 
    FULL OUTER JOIN table_c USING(id) 
    FULL OUTER JOIN table_d USING(id) 
    FULL OUTER JOIN table_e USING(id) 
WHERE table_a.ROWID IS NULL
   OR table_b.ROWID IS NULL
   OR table_c.ROWID IS NULL
   OR table_d.ROWID IS NULL
   OR table_e.ROWID IS NULL;

生产:

ID    TABLE_A TABLE_B TABLE_C TABLE_D TABLE_E
1     1       0       0       0       0
3     1       0       0       1       0
2     0       1       1       1       1

_{1 value in the table 0 missing entry}