mysql, hive, sqlserver,oracle对比

FULL (OUTER) JOIN

-- sqlserver，oracle，hive写法
select * from t1
full join t2 on t1.id = t2.id;

-- mysql 没有 full outer join: 用left join + right join + union 代替
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id;

Cartesian Product JOIN

-- sqlserver，oracle，mysql 支持Cartesian Product JOIN
select * from t1
full join t2 on 1=1;

-- hive在strict mode下不支持Cartesian Product JOIN
-- 解决方式1：用子查询新增一个joinkey列，然后用join
SELECT * FROM (select *, 1 as joinkey from t1) t1
LEFT JOIN (select *, 1 as joinkey from t2) t2 ON t1.joinkey= t2.joinkey;
-- 解决方式2：set hive.mapred.mode=nonstrict

对Null值的判断

select coalesce(null, 3);-- 支持：hive，mysql,sqlserver
select nvl(null, 3); -- 支持：hive,oracle
select case when col1 is null then 1 else 0 end -- 全部支持

GROUP BY ... HAVING...

• Mysql和Hive支持将SELECT中的运算结果字段带入HAVING中判断

select t.CountryCode, sum(Population) sum_p
from city t
group by t.CountryCode having sum_p>=270000;

• Sqlserver不支持此操作，要把计算在HAVING中再写一遍

select t.CountryCode, sum(Population) sum_p
from city t
group by t.CountryCode having sum(Population)>=270000;

• Oracle待验证