sql内部存在内部联接行为

Question

假设有两个表(在OracleSQL中)如下：

user(user_id，company_id):

123    | company_id_1 |
123    | company_id_2 |

公司(id，version_id):

company_id_1 | (null)      |
company_id_2 | version_id1 |

以下查询返回2行

company_id_1
company_id_2

SELECT distinct(company_id)
FROM user
WHERE user.user_id = 123 
AND user.company_id IS NOT NULL
AND EXISTS
(SELECT 1
FROM company
INNER JOIN user ON company.id = user.company_id AND company.version_id IS NOT NULL);

我希望只有1个结果，即company_id_2，但它返回2个结果(company_id_1，company_id~2)

其他注意事项：

• 以下查询确实为我返回了1个结果

SELECT distinct(company_id)
FROM user
WHERE user.user_id = 123 
AND user.company_id IS NOT NULL
AND EXISTS
(SELECT 1
FROM company
WHERE company.id = user.company_id AND company.version_id IS NOT NULL);

• 奇怪的是，以下语句(单独运行内部联接)确实返回了1个结果：

SELECT *
FROM company
INNER JOIN user ON company.id = user.company_id AND company.version_id IS NOT NULL
WHERE company.id IN (company_id_1, company_id_2)

1. 那么，为什么存在内部联接的查询会返回2个结果呢？即使通过单独运行内部联接，它也只返回1个结果，并且exists条件只应针对版本id为非null的company_id_2求值为true
2. 你能详细说明exists内部的内部联接与内部存在的正则where子句之间的区别吗？在我看来，它们都是一样的

Answer 1

不同之处在于，当您使用exists时，内部的查询通常取决于主查询(mean使用其中的列，因此不能单独执行)，因此，对于主查询的每一行，它都会检查子查询检索到的一些数据是否存在。

第一个查询的问题是，exists块中的子查询无论如何都不依赖于主查询列，它是一个单独的查询，总是返回一个带有1的行，因此，对于主查询的任何行，exists的结果都将始终是true。所以，你的第一个查询就相当于

SELECT distinct(company_id)
FROM user
WHERE user.user_id = 123 
AND user.company_id IS NOT NULL

另请参阅小提琴

Answer 2

如果没有联接，使用user.company_id的过滤器将从子查询外部关联。这意味着，对于外部查询中的每一行，子查询都可能返回不同的结果。

现有子查询中的联接没有什么特别的，但您可以将子查询与联接取消关联。它现在可以完全独立于外部查询运行。exists过滤器的工作方式完全相同，但由于与外部查询没有关联，因此它将始终为true或始终为false。

Answer 3

Query-1会给您两条记录，因为EXISTS中的查询不是相关的子查询，并且对于user.user_id = 123的两条记录都为true。请注意，EXISTS内部的表和外部的表(在主查询中)是单独评估的。

您的EXISTS条件在这里没有意义，因为它对任何记录都是正确的，因为它单独返回一个记录。

SELECT distinct(company_id)
FROM user
WHERE user.user_id = 123 
AND user.company_id IS NOT NULL
-- following will behave as an individual query
-- and has no relation will main query's user table
AND EXISTS
(SELECT 1
FROM company
INNER JOIN user ON company.id = user.company_id AND company.version_id IS NOT NULL);

现在，调试您的第二个查询。它是一个相关的子查询，EXISTS对于user.company_id = 'company_id_2'变为false，因此它只返回一条记录

SELECT distinct(company_id)
FROM user
WHERE user.user_id = 123 
AND user.company_id IS NOT NULL
-- in EXISTS condition user table is used which refers to the main query's user table
-- it is called the correlated sub-query
AND EXISTS
(SELECT 1
FROM company
WHERE company.id = user.company_id AND company.version_id IS NOT NULL); 

Answer 4

WHERE子句一次只看一行：

FROM user
WHERE ... EXISTS ( ... )

查看用户行并检查是否存在某些数据。

在您的第二个查询中，您检查是否存在用户的公司版本：

SELECT 1
FROM company
WHERE company.id = user.company_id -- the company for the user we are looking at in the main query
AND company.version_id IS NOT NULL

以下是如何使用EXISTS；子查询查找与主查询的行相关的数据。

然而，在您的第一个查询中，您现有的子查询是：

SELECT 1
FROM company
INNER JOIN user ON company.id = user.company_id 
AND company.version_id IS NOT NULL

您可以运行此单机版；因此它与主查询无关。你在这里要问的是：是否存在公司版本的用户。答案是：确实存在这样一个用户。因此，这是真的。无论您在主查询中处理什么，都为True。这不是如何使用EXISTS。我们很少使用与主查询无关的独立EXISTS子句。

第三个查询仅连接两个表，并查找与所有条件匹配的所有行。加入会给你这个：

+--------------+-----------------+--------------------+--------------------+|user.user_id | user.company_id | company.company_id | company.version _id|+--------------+-----------------+--------------------+--------------------+|123|company_id_1|company_id _1|(null)||123|company_id_2|company_id _2|版本_id1|+--------------+-----------------+--------------------+--------------------+

其中只有第二行与WHERE子句匹配。因此，只返回第二行。

我想提的另一件事是，当你被迫使用DISTINCT时，问问自己：是什么让这变得必要？为什么有你必须删除的重复项？规范化的数据库通常不包含重复的数据，因此这可能是查询中的一个弱点，它构建了一个太大的中间结果，然后必须减少。

如果正在寻找公司，请从以下公司中选择：

select *
from company
where version_id is not null
and company_id in (select company_id from user where user_id = 123);

或者用EXISTS代替IN:

select *
from company
where version_id is not null
and exists 
( 
select null 
from user
where user.company_id = company.company_id
and user.user_id = 123
);