如何在Hive中使用NOT IN [英] How to use NOT IN in Hive

问题描述

假设我有2个表格，如下所示.现在，如果我想获得sql将给出的结果，请插入ID不在其中的B(从A中选择ID)它将在表B中插入 3 George .

Suppose I have 2 tables as shown below. Now, if I want to achieve result which sql will give using, insert into B where id not in(select id from A) which will insert 3 George in Table B.

如何在配置单元中实现这一点?

How to implement this in hive?

表A

id  name      
1   Rahul     
2   Keshav    
3   George

表B

id  name      
1   Rahul     
2   Keshav    
4   Yogesh   

具有不相关子查询的WHERE子句中的

推荐答案

禁止进入是

---

+----+--------+
| id |  name  |
+----+--------+
|  3 | George |
+----+--------+

在早期版本中，外部表的列应使用表名/别名进行限定.

On earlier versions the column of the outer table should be qualified with the table name/alias.

hive> select * from A where id not in (select id from B where id is not null);
FAILED: SemanticException [Error 10249]: Line 1:22 Unsupported SubQuery Expression 'id': Correlating expression cannot contain unqualified column references.

---

hive> select * from A where A.id not in (select id from B where id is not null);
OK
3   George

P.s.
使用 NOT IN 时，应向内部查询添加 not null ，除非您100％确保相关列不包含空值.
一个空值足以使您的查询不返回任何结果.

P.s.
When using NOT IN you should add is not null to the inner query, unless you are 100% sure that the relevant column does not contain null values.
One null value is enough to cause your query to return no results.

这篇关于如何在Hive中使用NOT IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！