文章目录 简介1.LEFT JOIN2.INNER JOIN3.RIGHT JOIN4.FULL JOIN5.CROSS JOIN6.JOIN ON和WHERE条件区别7.其他常用SQL操作7.1 不同数据类型判断空7.2 排序或JOIN或Group by字段空值导致结果问题7.3 JOIN操作导致数据膨胀 8.Semi Join和Anti Join

简介

总体上JOIN的操作如下图所示 数据准备

create table user ( user_id bigint COMMENT '用户id' ,name STRING COMMENT '姓名' ,gender STRING COMMENT '性别' ) COMMENT '用户表'; create table class ( class_id bigint COMMENT '班级id' ,class_name STRING COMMENT '班级名' ,user_id BIGINT COMMENT '用户id' ) COMMENT '班级表'; INSERT INTO user values(1,'李四','男'); INSERT INTO user values(2,'张三','男'); INSERT INTO user values(3,'翠花','女'); INSERT INTO user values(4,'武则天','女'); INSERT INTO class values(1001,'物理',1); INSERT INTO class values(1001,'物理',3); INSERT INTO class values(1002,'物理',2); INSERT INTO class values(1003,'化学',null); INSERT INTO class values(1003,'化学',5); select * from user ; user_id name gender 4 武则天 女 2 张三 男 3 翠花 女 1 李四 男 select * from class ; class_id class_name user_id 1001 物理 1 1001 物理 3 1002 英语 2 1003 化学 \N 1003 化学 5 1.LEFT JOIN

返回左表的全部数据，和右表中满足on条件的行，如果左表的行在右表中没有匹配的数据，那么这一行中右表对应的数据为null

--SQL SELECT a.user_id ,a.name ,a.gender ,b.class_id ,b.class_name FROM （ SELECT user_id ,name ,gender FROM USER ） LEFT JOIN ( SELECT class_id ,class_name ,user_id FROM class ） ON a.user_id = b.user_id; --结果 user_id name gender class_id class_name 1 李四 男 1001 物理 2 张三 男 1002 英语 3 翠花 女 1001 物理 4 武则天 女 \N \N

HIVE中没有IN与NOT IN操作，可使用LEFT JOIN方式代替 (1) 获取【a在b中不存在】的数据

--获取【a在b中不存在】的数据 SELECT a.user_id ,a.name ,a.gender ,b.class_id ,b.class_name FROM （ SELECT user_id ,name ,gender FROM USER ） a LEFT JOIN ( SELECT class_id ,class_name ,user_id FROM class ）b ON a.user_id = b.user_id where b.user_id is NULL ; --结果 user_id name gender class_id class_name 4 武则天 女 \N \N

(2) 获取【a在b中存在】的数据

--获取【a在b中存在】的数据 SELECT a.user_id ,a.name ,a.gender ,b.class_id ,b.class_name FROM （ SELECT user_id ,name ,gender FROM USER ） a LEFT JOIN ( SELECT class_id ,class_name ,user_id FROM class ）b ON a.user_id = b.user_id where b.user_id is NOT NULL ; --结果 user_id name gender class_id class_name 1 李四 男 1001 物理 2 张三 男 1002 英语 3 翠花 女 1001 物理 2.INNER JOIN

返回左右表同时存在的数据，其实INNER JOIN 等价于上述获取【a在b中存在】数据的写法。

--SQL SELECT a.user_id ,a.name ,a.gender ,b.class_id ,b.class_name FROM （ SELECT user_id ,name ,gender FROM USER ） a INNER JOIN ( SELECT class_id ,class_name ,user_id FROM class ）b ON a.user_id = b.user_id; --结果 user_id name gender class_id class_name 1 李四 男 1001 物理 2 张三 男 1002 英语 3 翠花 女 1001 物理 3.RIGHT JOIN

返回右表中所有的行，和左表中满足on条件的行，如果右表的行在左表中没有匹配，那么这一行中左表的对应数据为NULL。 可以看作是LEFT JOIN的反面，一般用的也较少

--SQL1 SELECT a.user_id ,a.name ,a.gender ,b.class_id ,b.class_name FROM （ SELECT user_id ,name ,gender FROM USER ） a RIGHT JOIN ( SELECT class_id ,class_name ,user_id FROM class ）b ON a.user_id = b.user_id; --结果 user_id name gender class_id class_name 1 李四 男 1001 物理 3 翠花 女 1001 物理 2 张三 男 1002 英语 \N \N \N 1003 化学 \N \N \N 1003 化学

一般将主表的关联键保留下来

--SQL2 SELECT b.user_id --保留主表的关联键 ,a.name ,a.gender ,b.class_id ,b.class_name FROM （ SELECT user_id ,name ,gender FROM USER ） a RIGHT JOIN ( SELECT class_id ,class_name ,user_id FROM class ）b ON a.user_id = b.user_id; --结果 user_id name gender class_id class_name 1 李四 男 1001 物理 3 翠花 女 1001 物理 2 张三 男 1002 英语 \N \N \N 1003 化学 5 \N \N 1003 化学 4.FULL JOIN

会返回左表，右表所有的行，对应表中没有数据以NULL填充。 (1)SQL1及结果

--SQL1 SELECT a.*,b.* FROM ( SELECT user_id ,name ,gender FROM USER ) a FULL JOIN ( SELECT class_id ,class_name ,user_id FROM class ) b ON a.user_id = b.user_id; --结果 user_id name gender class_id class_name user_id2 2 张三 男 1002 英语 2 4 武则天 女 \N \N \N 3 翠花 女 1001 物理 3 1 李四 男 1001 物理 1 \N \N \N 1003 化学 5 \N \N \N 1003 化学 \N

(2)SQL2及结果 FULL JOIN 可以理解为 LEFT JOIN UNION RIGHT JOIN

--SQL2 SELECT a.user_id ,a.name ,a.gender ,b.class_id ,b.class_name FROM （ SELECT user_id ,name ,gender FROM USER ） a LEFT JOIN ( SELECT class_id ,class_name ,user_id FROM class ）b ON a.user_id = b.user_id UNION SELECT b.user_id ,a.name ,a.gender ,b.class_id ,b.class_name FROM （ SELECT user_id ,name ,gender FROM USER ） a RIGHT JOIN ( SELECT class_id ,class_name ,user_id FROM class ）b ON a.user_id = b.user_id; --结果 user_id name gender class_id class_name \N \N \N 1003 化学 1 李四 男 1001 物理 2 张三 男 1002 英语 3 翠花 女 1001 物理 4 武则天 女 \N \N 5 \N \N 1003 化学

(3)在取两个表中的共同值的时候，可以使用coalesce

--SQL3 SELECT coalesce(a.user_id,b.user_id) ,a.name ,a.gender ,b.class_id ,b.class_name FROM ( SELECT user_id ,name ,gender FROM USER ) a FULL JOIN ( SELECT class_id ,class_name ,user_id FROM class ) b ON a.user_id = b.user_id; --结果 user_id name gender class_id class_name 2 张三 男 1002 英语 4 武则天 女 \N \N 3 翠花 女 1001 物理 1 李四 男 1001 物理 5 \N \N 1003 化学 \N \N \N 1003 化学

注意：以上这种FULL JOIN的使用，在JOIN的字段无NULL值的情况下使用，才能保证数据的准确性。

5.CROSS JOIN

把表A和表B的数据进行一个N*M的组合，即笛卡尔积 即是4x5=20条记录

--SQL SELECT /*+MAPJOIN(b)*/a.*,b.* FROM ( SELECT user_id ,name ,gender FROM USER ) a CROSS JOIN ( SELECT class_id ,class_name ,user_id FROM class ) b; --结果 user_id name gender class_id class_name user_id2 1 李四 男 1001 物理 1 1 李四 男 1001 物理 3 1 李四 男 1002 英语 2 1 李四 男 1003 化学 \N 1 李四 男 1003 化学 5 2 张三 男 1001 物理 1 2 张三 男 1001 物理 3 2 张三 男 1002 英语 2 2 张三 男 1003 化学 \N 2 张三 男 1003 化学 5 3 翠花 女 1001 物理 1 3 翠花 女 1001 物理 3 3 翠花 女 1002 英语 2 3 翠花 女 1003 化学 \N 3 翠花 女 1003 化学 5 4 武则天 女 1001 物理 1 4 武则天 女 1001 物理 3 4 武则天 女 1002 英语 2 4 武则天 女 1003 化学 \N 4 武则天 女 1003 化学 5

JOIN没有写ON条件，等同于CROSS JOIN

--SQL2 SELECT /*+MAPJOIN(b)*/a.*,b.* FROM ( SELECT user_id ,name ,gender FROM USER ) a JOIN ( SELECT class_id ,class_name ,user_id FROM class ) b; --结果 user_id name gender class_id class_name user_id2 1 李四 男 1001 物理 1 1 李四 男 1001 物理 3 1 李四 男 1002 英语 2 1 李四 男 1003 化学 \N 1 李四 男 1003 化学 5 2 张三 男 1001 物理 1 2 张三 男 1001 物理 3 2 张三 男 1002 英语 2 2 张三 男 1003 化学 \N 2 张三 男 1003 化学 5 3 翠花 女 1001 物理 1 3 翠花 女 1001 物理 3 3 翠花 女 1002 英语 2 3 翠花 女 1003 化学 \N 3 翠花 女 1003 化学 5 4 武则天 女 1001 物理 1 4 武则天 女 1001 物理 3 4 武则天 女 1002 英语 2 4 武则天 女 1003 化学 \N 4 武则天 女 1003 化学 5 6.JOIN ON和WHERE条件区别

参考文章

7.其他常用SQL操作 7.1 不同数据类型判断空 数据类型判断空备注STRINGis not null and !=‘’DOUBLE/BIGINTis not null如果使用DOUBLE/BIGINT ！= ‘’,会出现返回\N，结果不正确。

例如：

select 8.0 != ''; --返回\N select true and 8.0 != ''; --返回\N 7.2 排序或JOIN或Group by字段空值导致结果问题

排序字段(以下a,b,c)为null会导致结果不正确

row_number() over(partition by a,b,c by gmtModified desc) as rk

group by字段(以下a,b,c)为null，会导致结果不正确

group by a ,b ,c

join字段为null会导致结果不正确

t1 left join t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c

经验：JOIN的字段不能有null，否则会导致结果不正确。

7.3 JOIN操作导致数据膨胀

例如

select * from a left join b on a.id=b.id

如果在主表a的id唯一情况下，副表b的id不唯一，就会导致数据膨胀(1条数据变多表)。

经验：在JOIN的时候最好保证JOIN的字段唯一，不然会出现数据膨胀，在计算聚合值的时候出现问题。

8.Semi Join和Anti Join

---

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。