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

问题描述

我有两个表 A 和 B，它们都有一个列 id.我希望从 A 中获取 B 中不存在的 id.显而易见的方法是:

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)

不幸的是，Hive 不支持 in、exists 或 subqueries.有没有办法使用连接来实现上述目的?

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

我想到了以下

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

但这似乎会返回整个 A，因为 B 中总是存在一个不等于 A 中任何 id 的 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.

推荐答案

您可以使用 Hive 中的 LEFT OUTER JOIN 执行相同的操作:

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屋！