hadoop-hive-关于IN与NOT IN

最新推荐文章于 2024-09-26 12:13:34 发布

原创最新推荐文章于 2024-09-26 12:13:34 发布 · 771 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hadoop #hive

Hadoop 专栏收录该内容

12 篇文章

订阅专栏

本文详细介绍了SQL中自关联和左外关联的概念，并通过具体实例展示了如何使用NOTIN进行左外关联操作，以及如何利用SQL查询语句解决实际问题。

此为自关联， NOT IN 则需要左外关联，u_sina.user is null;

echo a,sinax,1 >> USER_URL_VAL.txt

echo a,163x,2 >> USER_URL_VAL.txt
echo a,sohu,3 >> USER_URL_VAL.txt
echo b,sinax,4 >> USER_URL_VAL.txt
echo b,haha,5 >> USER_URL_VAL.txt
echo a,sinoix,6 >> USER_URL_VAL.txt

create table user_url_val (user string, url string, val string) row format delimited fields temporary by ',';

load data local inpath '/tmp/USER_URL_VAL.txt' into table user_url_val;

select * from user_url_val u join
(select user from user_url_val where url='163x') u_sina
on u.user=u_sina.user
where u.url='sinax';

a sinax 1 a

select * from user_url_val u left outer join
(select user from user_url_val where url='163x') u_sina
on u.user=u_sina.user
where u.url='sinax' and u_sina.user is null;

b sinax 4 NULL