select

     *

 from (

     select

          max(t1.source) as source

         ,t1.c1

         ,t1.c2

         ,t1.c3

         ,count(*) as cnt

     from (

         select 1 as source, t11.* from dev.astron_wangluochayi_1 t11

         union all

         select 2 as source, t12.* from dev.astron_wangluochayi_1 t12

     ) t1

     group by t1.source,t1.c1,t1.c2 ,t1.c3

     having count(*) = 1

 ) t2

 where t2.source = 1

 ;

-- 此算法可能是最快的，但只能找出是否一致，如不一致，比较难找出差异

select

     flag

    ,count(1) as cnt

from (

    select

        concat_ws('|',ifnull(id,''),ifnull(name,'')) as flag

    from (

        select 1 as id, 'a' as name union all

        select 2 as id, 'b' as name union all

        select 3 as id, 'c' as name

    ) t1

    union all

    select

        concat_ws('|',ifnull(id,''),ifnull(name,'')) as flag

    from (

        select 2 as id, 'b' as name union all

        select 3 as id, 'c' as name union all

        select 4 as id, 'd' as name

    ) t2

) t3

group by flag

having count(1) <> 2

;

-- full join

-- row_number 支持无主键表