限定数据的关联条件放错位置导致Left Join关联连接被破坏成 Inner Join 连接结果

1.前言:

当最终影响结果的过滤条件 [ t2.status=1 ] 紧跟where 而不是Left Join 后的ON后的限定条件时,会导致左外连接的查询条件变内连接结果,从而导致Left Join关联连接被破坏成 Inner Join 连接结果

2.创建临时查询数据

WITH t1 AS 
(
 SELECT '001' AS code,
        'aa' AS NAME FROM dual 
UNION ALL
 SELECT '002' AS code,
        'bb' AS NAME FROM dual 
UNION ALL
 SELECT '003' AS code,
        'cc' AS NAME FROM dual
),
t2 AS 
(
 SELECT '001' AS code,
        '1' AS status FROM dual 
UNION ALL
 SELECT '002' AS code,
        '2' AS status FROM dual 
) 

3. left join 正常连接无影响的查询数据结果

left join 正常情况无影响的查询数据结果,既

当最终影响结果的过滤条件紧跟左外连接的ON时

3.1 执行查询sql

WITH t1 AS 
(
 SELECT '001' AS code,
        'aa' AS NAME FROM dual 
UNION ALL
 SELECT '002' AS code,
        'bb' AS NAME FROM dual 
UNION ALL
 SELECT '003' AS code,
        'cc' AS NAME FROM dual
),
t2 AS 
(
 SELECT '001' AS code,
        '1' AS status FROM dual 
UNION ALL
 SELECT '002' AS code,
        '2' AS status FROM dual 
) 

SELECT t1.*,t2.* FROM t1
LEFT JOIN t2
ON t1.code = t2.code
and t2.status=1;

3.2 结论:

可以看到,t1作为主表,当 left join t2时,只有code=‘001’,t2表匹配上一条数据,当code=‘003’
和code='002’的时候,t2表示没有数据的,但是因为是 left join t2,所以以null值填充

4. left join 连接被影响的查询数据结果

left join 连接被影响的查询数据结果,既

当最终影响结果的过滤条件紧跟where时

4.1 执行查询sql

WITH t1 AS 
(
 SELECT '001' AS code,
        'aa' AS NAME FROM dual 
UNION ALL
 SELECT '002' AS code,
        'bb' AS NAME FROM dual 
UNION ALL
 SELECT '003' AS code,
        'cc' AS NAME FROM dual
),
t2 AS 
(
 SELECT '001' AS code,
        '1' AS status FROM dual 
UNION ALL
 SELECT '002' AS code,
        '2' AS status FROM dual 
) 

SELECT t1.*,t2.* FROM t1
LEFT JOIN t2
ON t1.code = t2.code
where t2.status=1;    --and t2.status=1; 

4.2 结论:

可以看到,查询sql只是把最后一句的 and t2.status=1 改成了where t2.status=1 就只查询出了一条数据,为什么会有这个结果出现,因为这个sql会先执行一下sql生成一个关联中间表(如下图)后在执行 where t2.status=1 进行数据过滤筛选,而筛选的过程就导致查询的中间表本来是左外连接的结果变成了内连接的结果,这就是

当最终影响结果的过滤条件紧跟where时,会导致左外连接的查询条件变内连接结果的最终原因

 WITH t1 AS 
(
 SELECT '001' AS code,
        'aa' AS NAME FROM dual 
UNION ALL
 SELECT '002' AS code,
        'bb' AS NAME FROM dual 
UNION ALL
 SELECT '003' AS code,
        'cc' AS NAME FROM dual
),
t2 AS 
(
 SELECT '001' AS code,
        '1' AS status FROM dual 
UNION ALL
 SELECT '002' AS code,
        '2' AS status FROM dual 
) 

SELECT t1.*,t2.* FROM t1
LEFT JOIN t2
ON t1.code = t2.code