hadoop-hive-关于IN与NOT IN

LeBronJames

于 2014-10-13 17:04:58 发布

阅读量771

点赞数

CC 4.0 BY-SA版权

分类专栏： Hadoop 文章标签： hadoop hive

本文链接：https://blog.youkuaiyun.com/LeBronJames/article/details/40048015

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