sql - postgresql - 加入匹配的表以及不匹配的表？

Question

所以我有一个连接，它可以工作，但它没有显示我想要的所有数据。目前我想查看 device_interface.c1 中的每一行，如果 device_interface.c1 = device_inventory.c1 那么我也想在列中查看它。现在我只看到了 device_interface.c1 = device_inventory.c1。

下面是我的查询:

SELECT 
  device_info.pk,
  device_interface.fk, 
  device_interface.c1, 
  device_interface.c2, 
  device_inventory.c1
FROM 
  "test".device_info, 
  "test".device_interface, 
  "test".device_inventory
WHERE 
  device_info.pk = device_interface.fk AND
  device_info.pk = device_inventory.fk AND
  device_interface.c1 = device_inventory.c1 AND
  device_interface.c2 = 'Physical'

示例输出:

device_info.pk  device_interface.c1   device_inventory.c1
1                        1                     1
1                        3                     3
1                        95                    95
2                        55                    55
2                        634                   634

所需输出示例:

device_info.pk  device_interface.c1   device_inventory.c1
1                        1                     1
1                        2          
1                        3                     3
1                        4
1                        5
1                        6
1                        95                    95
2                        55                    55
2                        56 
2                        57
2                        634                   634

Answer 1

left join 应该可以解决问题:

SELECT 
  device_info.pk,
  device_interface.fk, 
  device_interface.c1, 
  device_interface.c2, 
  device_inventory.c1
FROM 
  "test".device_info
LEFT JOIN
  "test".device_interface 
ON 
  device_info.pk = device_interface.fk
LEFT JOIN
  "test".device_inventory 
ON 
  device_info.pk = device_inventory.fk AND
  device_interface.c1 = device_inventory.c1
WHERE 
  device_interface.c2 = 'Physical'