Oracle -- left join 什么情况可以直接改成join

最新推荐文章于 2023-06-08 19:20:57 发布

cixiaobi8104

最新推荐文章于 2023-06-08 19:20:57 发布

阅读量218

点赞数

链接： http://blog.itpub.net/28602568/viewspace-1280508/

标题： Oracle -- left join 什么情况可以直接改成join

前言：

一般开发人员都比较喜欢写left join，那是否有些情况不用根据业务情况就能将left join 改成join 呢？

情景 ① A left join B on a.a=b.a where b.任意字段 =(只要不是is null ) 可将left 去掉;

理由：根据left join特性不匹配显示空，而where是针对关联后的结果进行筛选

例如（where b.a=1）那也就说明'is null'不匹配的过滤掉..既然不匹配的过滤掉那完全可以改成join了；

情景 ② A left join B on 1=1 where .. 可以将left 去掉;

理由：left join 和 join 都能实现 on 1=1 笛卡尔乘积

情景 ③ A left join B on (a.a=X or/and b.a=X ) where b. 任意字段 =(只要不是is null ), 可将left 去掉;

理由： (a.a=X or/and b.a=X ) 筛选后不匹配的显示为空.. 所以where 传值 (只要不是is null )就可以将left去掉；

注释：
很多时候即使写left join A where a.字段 is not null 情况。CBO生成执行计划时会自己判断成join的执行计划;
此博客仅是对left join的where 和on 2部分做一些了解；

以上三种情况可以统称： A left join B on ... where b.任意字段 =(只要不是is null ) 都 可将left 去掉;

情景 ①

【on a.a = b.a 】部分

SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 left join (SELECT 1 a, 2 b FROM dual) b 7 on a.a = b.a 8 where b.a =1; A B A B ---------- ---------- ---------- ---------- 1 2 1 2 SQL>	等同于	SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 /left/ join (SELECT 1 a, 2 b FROM dual) b 7 on a.a = b.a 8 where b.a =1; A B A B ---------- ---------- ---------- ---------- 1 2 1 2 SQL>
SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 left join (SELECT 1 a, 2 b FROM dual) b 7 on a.a = b.a 8 where b.a is null; A B A B ---------- ---------- ---------- ---------- 2 1 SQL>	不等于	SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 /left/ join (SELECT 1 a, 2 b FROM dual) b 7 on a.a = b.a 8 where b.a is null; A B A B ---------- ---------- ---------- ---------- SQL>

情景②

【on 1 = 1 】部分

SQL> SELECT *
  2    FROM (SELECT '1' a, 2 b
  3            FROM dual
  4          union all
  5          SELECT '2' a, 1 b FROM dual) a
  6    left join (SELECT '1' a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT '' a, 3 b FROM dual) b
10      on (1 = 1)
11   where b.a =1;

A          B A          B
- ---------- - ----------
1          2 1          2
2          1 1          2
SQL>

等同于

SQL>  SELECT *
  2    FROM (SELECT '1' a, 2 b
  3            FROM dual
  4          union all
  5          SELECT '2' a, 1 b FROM dual) a
  6    /*left*/ join (SELECT '1' a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT '' a, 3 b FROM dual) b
10      on (1 = 1)
11   where b.a =1;

A          B A          B
- ---------- - ----------
1          2 1          2
2          1 1          2

SQL>

情景③

【on (a.a = 1 or a.b =2) 】部分

SQL> SELECT *
  2    FROM (SELECT 1 a, 2 b
  3            FROM dual
  4          union all
  5          SELECT 2 a, 1 b FROM dual) a
  6    left join (SELECT 1 a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT 1 a, 3 b FROM dual) b
10      on (b.a = 2 or b.b = 5)
11   where b.b is null;

         A          B          A          B
---------- ---------- ---------- ----------
         1          2
         2          1

SQL>

不等于

SQL> SELECT *
  2    FROM (SELECT 1 a, 2 b
  3            FROM dual
  4          union all
  5          SELECT 2 a, 1 b FROM dual) a
  6    /*left*/ join (SELECT 1 a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT 1 a, 3 b FROM dual) b
10      on (b.a = 2 or b.b = 5)
11   where b.b is null;

         A          B          A          B
---------- ---------- ---------- ----------

SQL>

SQL> SELECT *
  2    FROM (SELECT 1 a, 2 b
  3            FROM dual
  4          union all
  5          SELECT 2 a, 1 b FROM dual) a
  6    left join (SELECT 1 a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT 1 a, 3 b FROM dual) b
10      on (a.a = 2 or b.b = 5)
11   where b.b is not null;

         A          B          A          B
---------- ---------- ---------- ----------
         2          1          1          2
         2          1          1          3

SQL>

等同于

SQL> SELECT *
  2    FROM (SELECT 1 a, 2 b
  3            FROM dual
  4          union all
  5          SELECT 2 a, 1 b FROM dual) a
  6    /*left*/ join (SELECT 1 a, 2 b
  7                 FROM dual
  8               union all
  9               SELECT 1 a, 3 b FROM dual) b
10      on (a.a = 2 or b.b = 5)
11   where b.b is not null;

         A          B          A          B
---------- ---------- ---------- ----------
         2          1          1          2
         2          1          1          3

SQL>

【on (a.a = 1 and a.b =2) 】部分

SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 left join (SELECT 1 a, 2 b 7 FROM dual 8 union all 9 SELECT 1 a, 3 b FROM dual) b 10 on (a.a = 1 and b.b = 3) 11 where b.b is null; A B A B ---------- ---------- ---------- ---------- 2 1 SQL>	不等于		SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 /left/ join (SELECT 1 a, 2 b 7 FROM dual 8 union all 9 SELECT 1 a, 3 b FROM dual) b 10 on (a.a = 1 and b.b = 3) 11 where b.b is null; A B A B ---------- ---------- ---------- ---------- SQL>
SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 left join (SELECT 1 a, 2 b 7 FROM dual 8 union all 9 SELECT 1 a, 3 b FROM dual) b 10 on (a.a = 1 and b.b = 3) 11 where b.b is not null; A B A B ---------- ---------- ---------- ---------- 1 2 1 3 SQL>	等同于		SQL> SELECT * 2 FROM (SELECT 1 a, 2 b 3 FROM dual 4 union all 5 SELECT 2 a, 1 b FROM dual) a 6 /left/ join (SELECT 1 a, 2 b 7 FROM dual 8 union all 9 SELECT 1 a, 3 b FROM dual) b 10 on (a.a = 1 and b.b = 3) 11 where b.b is not null; A B A B ---------- ---------- ---------- ---------- 1 2 1 3 SQL>