sql - SQL join - 如何选择出现在一个表中而不是另一个表中的所有不同值

Question

我有 2 个 SQL 表。2 个表共享 2 列：external_id类型int和permission类型text：

CREATE TABLE A (
    id              serial PRIMARY KEY,
    external_id     int,
    permission      text,          
    something       int,           
    rather          text
);

CREATE TABLE B (
    id              serial PRIMARY KEY,
    external_id     int,
    permission      text,          
    random          int,           
    life_story      text
);

我想要表中不存在于表中的所有不同组合，反之亦然，表中不存在于select表external_id中的所有不同组合permissionAB ANDexternal_idpermissionBA

为了澄清我正在尝试编写的查询，我转向示例：如果表如下所示：

mwe=# select * from A;
 id | external_id | permission | something | rather
----+-------------+------------+-----------+---------
  1 |           1 | X          |      1111 | bla bla
  2 |           1 | P          |      1111 | bla bla
  3 |           2 | X          |      1111 | bla bla
(3 rows)

mwe=# select * from B;
 id | external_id | permission | random | life_story
----+-------------+------------+--------+------------
  1 |           1 | X          |     41 | bla bla
  2 |           1 | P          |     15 | bla bla
  3 |           2 | X          |     46 | bla bla
(3 rows)

我的查询结果将为空。因为两个表的 distinct 元组external_id是：permission

1| X
1| P
2| X

如果表的状态是：

mwe=# select * from A;
 id | external_id | permission | something | rather
----+-------------+------------+-----------+---------
  1 |           1 | X          |      1111 | bla bla
  2 |           1 | P          |      1111 | bla bla
  3 |           2 | P          |      1111 | bla bla
  4 |           2 | X          |      1111 | bla bla
  5 |           2 | X          |      2222 | bla bla
(5 rows)

mwe=# select * from B;
 id | external_id | permission | random | life_story
----+-------------+------------+--------+------------
  1 |           1 | X          |     41 | bla bla
  2 |           1 | X          |     99 | one day along time ago
  3 |           1 | P          |     15 | bla bla
  4 |        NULL | X          |     46 | bla bla
  5 |           2 | X          |     46 | bla bla
(5 rows)

我的查询结果应该是：

2   | P
NULL| X

因为table 的 distinct 元external_id组是：permissionA

 1| X              # id=1
 1| P              # id=2
 2| P              # id=3
 2| X              # id=4,5

和 distinct external_id，permission表的元组B是：

 1   | X           # id=1,2
 1   | P           # id=3
 NULL| X           # id=4
 2   | X           # id=5

由于2| P出现在我们为 table 列出的元组中，A而不出现在我们为 table 列出的元组中B，我希望它作为我想要的选择查询的结果返回。

而且由于NULL| X出现在我们为 table 列出的元组中，B而不出现在我们为 table 列出的元组中A，我希望它作为我想要的选择查询的结果返回

对于两个表中的任何一个中的每个其他 distinct元组external_id，permission另一个表上都有一个匹配的元组：

 1   | X           # table A id=1 table B id=1,2
 1   | P           # table A id=2 table B id=3
 2   | X           # table A id=4,5 table B id=5

我正在使用 Postgres 9.6

标签： sqlpostgresqlselect

Answer 1

您可以NOT IN在每一侧使用，然后UNION在两侧使用：

select distinct external_id, permission
  from a
  where (external_id, permission) not in (
    select external_id, permission from b
  )
  or external_id is null and permission not in (
    select permission from b where permission is null    
  )
union  
select distinct external_id, permission
  from b
  where (external_id, permission) not in (
    select external_id, permission from a
  )
  or external_id is null and permission not in (
    select permission from a where permission is null    
  );

结果：

external_id  permission
-----------  ----------
2            P         
<null>       X