sql掉坑记录

SQL环境： 阿里MAXCompute

大致的语句

 

@existgroup :=
  select group_name,
         device_name
  from a.table1 
;

@existinterface := SELECT
                   group_name,
                   device_name
                   from b.table2
                   ;


    select
    t1.group_name,
    t1.device_name,
    t2.group_name as t2_group_name,
    t2.device_name as t2_device_name
    from
    (select distinct group_name,
                     device_name
                      from @existgroup )t1
     left join   (
        SELECT distinct
       group_name,
       device_name
        from @existinterface 
      )t2
    where t1.group_name = t2.group_name
    and t1.device_name = t2.device_name
 )t12
;

 

这个语句始终没法发现t2比t1少的那些行(left join 是outer join应该能找出差异项)。调试了很久后来发现在left join上，SQL语法编译器提示如下：

outer join will be treated as inner join because of filtration: t1.group_name = t2.group_name

这才反应过来：left join 作为outer join的一种，后面得使用on 

问题解决