SQL可选JOIN查询问题：关联多表获取设备对应管分区信息

表结构与示例数据 #

我有父表device，以及stop、ctrltab、pipe_division三张表。其中stop和ctrltab均通过device_id关联device表，通过pipe_division_id关联pipe_division表。

Device表 #

device_id | name     | board_number 
--------------------------------
23         Stop1      10
24         Stop2      11
25         Ctrltab1   11
26         Rand_dev   8 

Stop表 #

device_id | label          | pipe_division_id | length
23          Stop1: Piano     305                16
24          Stop2: Buffet    306                16

Ctrltab表 #

device_id | label      | pipe_division_id | ctrl_function
25          Ctrltab1       305              open_window 

Pipe Division表 #

pipe_division_id | label     | position
305                Lower Box    underneath the stairs
306                Upper Box    above the stairs
307                Side Box     To the left of the console in the closet

需求 #

查询device表中board_number大于10的所有设备，同时获取其通过stop或ctrltab表关联到的pipe_division表的对应label，且希望避免使用UNION实现。

期望查询结果 #

name     | board_number | label
Stop1      10             Lower Box
Stop2      11             Upper Box
Ctrltab1   11             Lower Box

尝试的SQL（无结果返回） #

Select name, board_number, pd.label 
from device d 
JOIN stops s ON s.device_id = d.device_id 
JOIN ctrltab ct ON ct.device_id = d.device_id 
JOIN pipe_division_id pd ON (s.pipe_division_id = pd.pipe_division_id 
                         OR ct.pipe_division_id = pd.pipe_division_id)

问题分析与解决方案 #

问题原因 #

1. 内连接导致无匹配数据：使用JOIN（内连接）同时关联stop和ctrltab，但一个设备只会存在于其中一张表（比如Stop1只在stop表，Ctrltab1只在ctrltab表），没有设备同时存在于两张表，因此连接后无结果返回。
2. 表名错误：pipe_division_id是字段名，不是表名，正确表名应为pipe_division。

正确SQL语句 #

使用LEFT JOIN分别关联stop和ctrltab，通过COALESCE获取有效的pipe_division_id，再关联pipe_division表：

SELECT 
    d.name, 
    d.board_number, 
    pd.label
FROM device d
LEFT JOIN stop s ON s.device_id = d.device_id
LEFT JOIN ctrltab ct ON ct.device_id = d.device_id
JOIN pipe_division pd ON pd.pipe_division_id = COALESCE(s.pipe_division_id, ct.pipe_division_id)
WHERE d.board_number > 10;

或者，也可以在关联pipe_division时使用OR匹配两个表的关联字段，但需要确保至少有一个表存在匹配：

SELECT 
    d.name, 
    d.board_number, 
    pd.label
FROM device d
LEFT JOIN stop s ON s.device_id = d.device_id
LEFT JOIN ctrltab ct ON ct.device_id = d.device_id
JOIN pipe_division pd ON pd.pipe_division_id = s.pipe_division_id 
                     OR pd.pipe_division_id = ct.pipe_division_id
WHERE d.board_number > 10
  AND (s.device_id IS NOT NULL OR ct.device_id IS NOT NULL);

这两种写法都不需要使用UNION，且能正确返回你需要的结果。

内容的提问来源于stack exchange，提问作者Cheetaiean