Hive不支持，存在。我如何编写以下查询？ [英] Hive doesn't support in, exists. How do I write the following query?

问题描述

我有两个表A和B，都有一个列ID。我希望从A中获得不在B中的ID。显而易见的方法是：

pre code> SELECT id FROM WHERE id NOT IN（SELECT id FROM B）

不幸的是，Hive不支持in，exists或subqueries。有没有办法实现上述使用连接？

我想到以下内容：

  SELECT A.id FROM A，B WHERE A.id<> bid 
  

<但看起来这样会返回A的全部，因为B中总是存在一个id，它不等于A中的任何id。

解决方案

你可以在Hive中用 LEFT OUTER JOIN 做同样的事情：

  SELECT A.id 
 FROM A 
 LEFT OUTER JOIN B 
 ON（B.id = A.id）
 WHERE B.id IS $ null b $ b  

I have two tables A and B that both have a column id. I wish to obtain ids from A that are not present in B. The obvious way is:

SELECT id FROM A WHERE id NOT IN (SELECT id FROM B)

Unfortunately, Hive doesn't support in, exists or subqueries. Is there a way to achieve the above using joins?

I thought of the following

SELECT A.id FROM A,B WHERE A.id<>B.id

But it seems like this will return the entirety of A, since there always exists an id in B that is not equal to any id in A.

解决方案

You can do the same with a LEFT OUTER JOIN in Hive:

SELECT A.id
FROM A
LEFT OUTER JOIN B
ON (B.id = A.id)
WHERE B.id IS null

这篇关于Hive不支持，存在。我如何编写以下查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！