文章目录
简介
总体上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条件区别
参考文章
HIVE的谓词下推
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
标签：
sql
本文转载自: https://blog.csdn.net/lihuazaizheli/article/details/124053485
版权归原作者 黄土高坡上的独孤前辈 所有， 如有侵权，请联系我们删除。
版权归原作者 黄土高坡上的独孤前辈 所有， 如有侵权，请联系我们删除。