如何获取全连接的空结果表？

Question

我将向您展示一些表的内容。

Bolek=> SELECT id, description from "TOMBInput";
 id |    description    
----+-------------------
  1 | Virtual Input 111
  2 | Virtual Input 112
  3 | Virtual Input 113
  4 | Virtual Input 114
(4 rows)

Bolek=> SELECT id, setup_id FROM "TRBTOMBConnection";
 id | setup_id 
----+----------
  1 |        1
  2 |        1
  3 |        1
  4 |        1
(4 rows)

Bolek=> SELECT id, setname FROM "Setup";
 id |   setname   
----+-------------
  1 | SETUP_00001
(1 row)

Bolek=> SELECT id, setup_id FROM "Run";
 id | setup_id 
----+----------
  1 |        1
(1 row)

我的问题1是

SELECT
  "TOMBInput".id AS tombinput_id,
  "TRBTOMBConnection".id AS trbtombconnection_id,
  "Setup".id AS setup_id,
  "Run".id AS run_id
FROM "TOMBInput" 
  INNER JOIN "TRBTOMBConnection" ON "TOMBInput".id = "TRBTOMBConnection".tombinput_id
  FULL JOIN "Setup" ON "TRBTOMBConnection".id = "Setup".id
  FULL JOIN "Run" ON "Setup".id = "Run".id AND "Run".id = 1;

结果表

 tombinput_id | trbtombconnection_id | setup_id | run_id 
--------------+----------------------+----------+--------
            1 |                    1 |        1 |      1
            2 |                    2 |          |       
            3 |                    3 |          |       
            4 |                    4 |          |       
(4 rows)

问题是我想要像这样的桌子

 tombinput_id | trbtombconnection_id | setup_id | run_id 
--------------+----------------------+----------+--------
            1 |                    1 |        1 |      1
            2 |                    2 |        1 |      1 
            3 |                    3 |        1 |      1 
            4 |                    4 |        1 |      1 
(4 rows)

因为"TRBTOMBConnection“有4行setup_id==1，而"Run”有setup_id==1。

更重要的是，现在当我更改最后一行(在我的查询1中)

FULL JOIN "Run" ON "Setup".id = "Run".id AND "Run".id = 2;

(在"Run“表中，我们没有id==2)查询的结果是

 tombinput_id | trbtombconnection_id | setup_id | run_id 
--------------+----------------------+----------+--------
            1 |                    1 |        1 |       
            2 |                    2 |          |       
            3 |                    3 |          |       
            4 |                    4 |          |       
              |                      |          |      1
(5 rows)

这没问题，因为我用的是全连接。

但在本例中，当我运行查询1

我希望有一个空的结果表，因为" Run“没有id==2，并且它没有任何意义来显示表，因为一切都是从Run开始的。

如何更改我的查询1

Answer 1

您混淆了ID：

SELECT
  "TOMBInput".id AS tombinput_id,
  "TRBTOMBConnection".id AS trbtombconnection_id,
  "Setup".id AS setup_id,
  "Run".id AS run_id
FROM "TOMBInput" 
  INNER JOIN "TRBTOMBConnection" ON "TOMBInput".id = "TRBTOMBConnection".tombinput_id
  INNER JOIN "Setup" ON "TRBTOMBConnection".setup_id = "Setup".id
  INNER JOIN "Run" ON "Setup".id = "Run".setup_id AND "Run".id = 1;

我在这里看不到完全外部连接的理由。