Oracle存储过程迁移ODPS-03（专有云）：ODPS1.0支持exists语法 ...-优快云博客

专有云目前还有不少ODPS1.0版本，主要是应对V3之前的平台版本。这个版本的ODPS不支持exists语法，如何换种写法实现。

-- ODPS1.0 如何实现 exists 语法
-- oracle
select col1,col2,col3,col4
  from (select t1.col1,t1.col2,t2.col3,t2.col4
          from      table1 t1
         INNER JOIN table2 t2
            ON t1.col1 = t2.col1
         WHERE (t2.col3 between '14400000000' and '14499000000')
           and (NOT EXISTS
                (SELECT 1
                    FROM table3 t3
                   WHERE t1.col2 = t3.col2
                     AND GREATEST(t3.col5,t3.col6,t3.col7) <= sysdate()
                     AND GREATEST(t3.col5,t3.col6,t3.col7) >= sysdate() - interval '1095' day))) foo
 group by col1
 order by col1;
-- odps
select col1,col2,col3,col4
  from (select t1.col1,t1.col2,t2.col3,t2.col4
          from            table1 t1
         inner       join table2 t2
            ON t1.col1 = t2.col1
          left outer join(
               SELECT col2
                 FROM table3 t3
                WHERE GREATEST(t3.col5,t3.col6,t3.col7) <= getdate()
                  AND GREATEST(t3.col5,t3.col6,t3.col7) >= getdate() - 1095) t3
            ON t1.col2 = t3.col2
         WHERE (t2.col3 >= '14400000000' and t2.col3 <='14499000000')
           and t3.col2 is null
) foo
 group by col1
 order by col1;
-- 暮角 15901445705 update at 20181224

exists写法在oracle上其实就是可用，可不用的写法。主要是使用exists语法的查询效率高于JOIN，所以，很多人在使用。