关于Hive 在子查询中是否支持 IN 和 NOT IN 子句的测试

╰+哭是因爲堅強的太久メ 2022-04-04 06:14 588阅读 0赞

## 亲测有效，在hive-2.1.1版本,存储为hadoop-2.7.3下，hive支持 in或not in 中包含查询子句的语法，此外，在有些版本或hive thrift包不支持的话，也可能通过left join实现 ##

### 1、创建表 ###

假设有一个用户浏览商品表scan\_info,和一个用户购买商品表buy\_info。如下

> hive> create table scan\_info (userId string,itemId string,time string) row format delimited fields terminated by '\\t';
> 
> OK
> 
> Time taken: 0.784 seconds
> 
> hive> create table buy\_info (userId string,itemId string,time string) row format delimited fields terminated by '\\t';
> 
> OK
> 
> Time taken: 0.133 seconds

### 2、准备数据并导入数据 ###

> hive> load data local inpath '/home/wo/yy\_work/scan\_info.txt' overwrite into table scan\_info;
> 
> Loading data to table test\_hive.scan\_info
> 
> OK
> 
> Time taken: 2.568 seconds
> 
> hive> load data local inpath '/home/wo/yy\_work/buy\_info.txt' overwrite into table buy\_info;
> 
> Loading data to table test\_hive.buy\_info
> 
> OK
> 
> Time taken: 0.605 second

### 3、验证查看数据 ###

> hive> select \* from scan\_info;
> 
> OK
> 
> 001 342 2018-12-14
> 
> 002 382 2018-12-15
> 
> 003 458 2018-12-15
> 
> 004 468 2018-12-15
> 
> hive> select \* from buy\_info;
> 
> OK
> 
> 001 342 2018-12-13
> 
> 002 382 2018-12-14
> 
> 003 458 2018-12-15
> 
> 005 325 2018-12-15

### 4、左外连接查询 ###

>   
> select \* from scan\_info left outer join buy\_info on scan\_info.userId = buy\_info.userId and scan\_info.itemId = buy\_info.itemId 
> 
> Total MapReduce CPU Time Spent: 2 seconds 690 msec
> 
> OK
> 
> 001 342 2018-12-14 001 342 2018-12-13
> 
> 002 382 2018-12-15 002 382 2018-12-14
> 
> 003 458 2018-12-15 003 458 2018-12-15
> 
> 004 468 2018-12-15 NULL NULL NULL
> 
> Time taken: 23.802 seconds, Fetched: 4 row(s)

### 5、如果要查询在scan\_info表中并且也在buy\_info表中的信息，需要用in查询，hive sql如下: ###

> select scan\_info.userId , scan\_info.itemId from scan\_info left outer join buy\_info
> 
> on scan\_info.userId = buy\_info.userId and scan\_info.itemId = buy\_info.itemId
> 
> where buy\_info.userId is not null;
> 
> 001 342
> 
> 002 382
> 
> 003 458
> 
> Time taken: 22.313 seconds, Fetched: 3 row(s)

### 6、如果要查询在scan\_info表中并且不也在buy\_info表中的信息，需要用not in查询，hive sql如下: ###

> select scan\_info.userId, scan\_info.itemId from scan\_info left outer join buy\_info
> 
> on scan\_info.userId=buy\_info.userId and scan\_info.itemId=buy\_info.itemId
> 
> where buy\_info.userId is null;
> 
> OK
> 
> 004 468
> 
> Time taken: 20.133 seconds, Fetched: 1 row(s)

### 7、not in 子查询 ###

> select scan\_info.userId, scan\_info.itemId from scan\_info where scan\_info.userId not in (
> 
> select distinct buy\_info.userId from buy\_info )
> 
> OK
> 
> 004 468
> 
> Time taken: 104.018 seconds, Fetched: 1 row(s)

### 8、in子查询 ###

> select scan\_info.userId, scan\_info.itemId from scan\_info where scan\_info.userId in (
> 
> select distinct buy\_info.userId from buy\_info )
> 
> OK
> 
> 001 342
> 
> 002 382
> 
> 003 458
> 
> Time taken: 44.224 seconds, Fetched: 3 row(s)

参考：[https://blog.csdn.net/yzhhughyzh/article/details/45604291][https_blog.csdn.net_yzhhughyzh_article_details_45604291]

https://blog.csdn.net/chenyuexing0822/article/details/78525383

[https_blog.csdn.net_yzhhughyzh_article_details_45604291]: https://blog.csdn.net/yzhhughyzh/article/details/45604291