多表连接SQL查询需求：获取2021年ID与ID4关联结果

需求描述 #

编写多表连接的SQL查询，获取2021年对应的ID与ID4的关联结果。

现有4张表结构 #

1) table1 #

2) table2 #

3) table3 #

4) table4 #

期望输出 #

当前编写的查询语句 #

Select a.ID, d.ID4
from table1 as a
left join table2 as b on a.id = b.id and a.date = b.year
inner join table3 as c on b.id2 = c.id2  and b.fulldate = c.fulldate
inner join table4 as d on c.id3 = d.id3 and c.fulldate = d.fulldate
where b.year = '2021'

查询修正方案 #

当前查询存在逻辑问题：使用left join table2后在where中过滤b.year = '2021'，会将左连接自动转为内连接，且关联条件未精准锁定2021年数据链路，可能出现多余关联。

以下是两种可行的修正写法：

写法一：精准锁定各表2021年数据 #

SELECT a.ID, d.ID4
FROM table1 AS a
INNER JOIN table2 AS b 
    ON a.id = b.id 
    AND a.Date = 2021
    AND b.year = 2021
INNER JOIN table3 AS c 
    ON b.id2 = c.id2 
    AND b.fulldate = c.fulldate
    AND c.year = 2021
INNER JOIN table4 AS d 
    ON c.id3 = d.id3 
    AND c.fulldate = d.fulldate
    AND d.year = 2021;

写法二：通过year字段简化关联逻辑 #

SELECT a.ID, d.ID4
FROM table1 AS a
INNER JOIN table2 AS b 
    ON a.id = b.id 
    AND a.Date = 2021 
    AND b.year = 2021
INNER JOIN table3 AS c 
    ON b.id2 = c.id2 
    AND b.year = c.year
INNER JOIN table4 AS d 
    ON c.id3 = d.id3 
    AND c.year = d.year;

两种写法均可得到期望输出，核心是精准锁定2021年的关联数据链路，避免不必要的关联和过滤逻辑。

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