sql - 合并 3 个表的结果

Question

我正在使用具有以下结构和数据的 Postgres 10:

create table table1(
  id serial primary key
);

create table table2(
  id serial primary key
);

create table table3(
  id serial primary key,
  table1_id int,
  table2_id int
);

insert into table1 (id) values (1), (2), (3);
insert into table2 (id) values (1), (2);
insert into table3 (id, table1_id, table2_id) values (1, 1, 1), (2, 1, 2), (3, 2, 1);

我想要在表 3 中没有条目的表 1 和表 2 的所有组合，本质上是这种结果:

+-----------+-----------+-----------+
| table1.id | table2.id | table3.id |
+-----------+-----------+-----------+
|         1 |         1 | 1         |
|         2 |         1 | 3         |
|         3 |         1 | null      |
|         1 |         2 | 2         |
|         2 |         2 | null      |
|         3 |         2 | null      |
+-----------+-----------+-----------+

注意结果如何包含来自 table1 和 table2 的所有可能组合，以及来自 table3 的相应 id。

最终，我想让查询只返回 table3.id 为 NULL 的行:

+-----------+-----------+-----------+
| table1.id | table2.id | table3.id |
+-----------+-----------+-----------+
|         3 |         1 | null      |
|         2 |         2 | null      |
|         3 |         2 | null      |
+-----------+-----------+-----------+

我不确定如何处理这个查询:join 或inner selects 或者甚至excludes？

Answer 1

您可以尝试在 table1 和 table2 上使用 CROSS JOIN(笛卡尔积)，然后在 table3 LEFT JOIN 基于 CROSS JOIN 结果集。

架构(PostgreSQL v9.6)

create table table1(
  id serial primary key
);

create table table2(
  id serial primary key
);

create table table3(
  id serial primary key,
  table1_id int,
  table2_id int
);

insert into table1 (id) values (1), (2), (3);
insert into table2 (id) values (1), (2);
insert into table3 (id, table1_id, table2_id) values (1, 1, 1), (2, 1, 2), (3, 2, 1);

---

查询#1

SELECT t1.id,t2.id,t3.id 
FROM table1 t1 CROSS JOIN table2 t2
LEFT JOIN table3 t3 
on t3.table1_id = t1.id and t3.table2_id = t2.id
where t3.id is null;

---

结果

t1id  t2id  t3id
2      2    null
3      1    null
3      2    null

View on DB Fiddle