HiveSQL基础之表连接

1. inner join

• 内连接，返回两个表的交集
• 既在user_list_1又在user_list_2的用户

SELECT * 
FROM user_list_1 as a
JOIN user_list_2 as b
ON a.user_id=b.user_id;

• 表连接时，必须进行重命名
• on后面使用的连接条件必须起到唯一键值的作用
• inner可以省略不写，效果一样
• 一定要先去重，再做表连接，养成良好习惯
• 在2019年购买后又退款的用户

SELECT a.user_name
FROM
	(SELECT distinct user_name
	FROM user_trade
	WHERE year(dt)=2019) as a
  JOIN
	(SELECT distinct user_name
	FROM user_refund
	WHERE year(dt)='2019') as b ON a.user_name=b.user_name;

• 在2017年和2018年都购买的用户

SELECT a.user_name
FROM
	(SELECT distinct user_name 
	FROM user_trade
	WHERE year(dt)='2017') as a
  JOIN
	(SELECT distinct user_name
	FROM user_trade
	WHERE year(dt)='2018') as b ON a.user_name=b.user_name;

• 在2017，2018，2019年都有交易的用户

SELECT a.user_name
FROM
	(SELECT distinct user_name
	FROM trade_2017) as a
  JOIN
	(SELECT distinct user_name
	FROM trade_2018) as b on a.user_name=b.user_name
  JOIN 
	(SELECT distinct user_name
	FROM trade_2019) as c on b.user_name=c.user_name;

2. left join

• 左连接，以左边为全集，返回能够匹配上右表的匹配结果，没有匹配上的显示NULL

SELECT * 
FROM 
	user_list_1 as a
LEFT JOIN 
	user_list_2 as b ON a.user_id=b.user_id;

• 在user_list_1中但是不在user_list_2中

SELECT a.user_id,
	   a.u