Oracle系列工作总结

1.EXISTS用法错误注意

错误代码：
SELECT A.*
FROM TABLE1 A
LEFT JOIN TABLE2 B
ON EXISTS (SELECT 1
FROM TABLE3 C
WHERE C.YHBH = B.YHBH
AND C.YHZTDM = ‘1’
AND (CASE
WHEN A.XLTQBZ = ‘1’ THEN
C.YHLBDM = ‘110’
WHEN A.XLTQBZ = ‘2’ THEN
C.YHLBDM = ‘120’
ELSE
NULL
END))
WHERE A.NY = 2019;

总结：case when 条件 then 值，then的后面是值，不是表达式，如果放在where条件后面需要写成表达式加值的形式。

正确代码：

SELECT A.*
  FROM TABLE1 A
  LEFT JOIN TABLE2 B
    ON EXISTS (SELECT 1
          FROM TABLE3 C
         WHERE C.YHBH = B.YHBH
           AND C.YHZTDM = '1'
           AND  C.YHLBDM =(CASE
                 WHEN A.XLTQBZ = '1' THEN
                   '110'
                 WHEN A.XLTQBZ = '2' THEN
                   '120'
                 ELSE
                  NULL
               END))
 WHERE A.NY = 2019;

2.LEFT JOIN ON执行速度

慢：

SELECT  a.*
  FROM TABLE1 a
  left join TABLE2 b
    ON  a.bs=b.bs
    and  exists (SELECT 1
          FROM TABLE3 c
         WHERE c.yhbh = b.yhbh
           and c.dq = b.dq
           and c.yhlbdm = '120'
           and c.yhztdm = '1')
 WHERE a.ny = 2019
   and a.xltqbz = '2'  ;

总结： 将exist的条件放在ON后面比放在where后面要慢得多(具体执行速度原因还未研究，后续补充)

快：

 SELECT  a.*
      FROM TABLE1 a
      left join TABLE2 b
        ON  a.bs=b.bs
     WHERE a.ny = 2019
       and a.xltqbz = '2' 
       and  exists (SELECT 1
              FROM TABLE3 c
             WHERE c.yhbh = b.yhbh
               and c.dq = b.dq
               and c.yhlbdm = '120'
               and c.yhztdm = '1') ;