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;
