内部连接内部的行为存在sql [英] behavior of inner join inside exists sql

问题描述

让我们说有2个表(在oracle SQL中)是这样的:

let's say that there're 2 tables (in oracle SQL) like this:

用户(用户ID，公司ID)

user(user_id, company_id):

123    | company_id_1 |
123    | company_id_2 |

company(id，version_id):

company(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)

I would expect there's only 1 result, which is company_id_2, but it returns 2 results (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个结果，并且存在条件仅应仅针对具有不为空的version_id
的company_id_2评估为true.
2. 您能否详细说明exists内部的内部联接与exists内部的常规where子句之间的区别是什么?
1. So why does query with inner join inside exists returns 2 results? even though by running the inner join individually it only returns 1 result, and exists condition should only be evaluated to true for only company_id_2, which has the not-null version_id
2. Can you elaborate more on what's the difference between the inner join inside the exists vs the regular where clause inside exists, they both looks the same to me?

推荐答案

区别在于，当您使用 exists 时-内部查询通常取决于主查询(均值使用主查询中的列，因此不能单独执行)，因此，对于主查询的每一行，它都会检查子查询返回的某些数据是否存在.

The difference is that when you use exists - the query inside usually depends on the main query (mean uses columns from it and so can't be executed separately) and, so, for each row of the main query it checks if some data retreived by the subquery exists or not.

您的第一个查询的问题是，existing块中的子查询无论如何都不依赖于主查询列，它是一个单独的查询，始终返回带有 1 的行，因此，对于任何主查询的行中 exists 的结果将始终为 true .因此，您的第一个查询等同于

The problem of your first query is that the subquery inside exists block doesn't anyhow depend on the main query columns, it's a separate query which always return a row with 1, so, for any row of the main query the result of exists will be always true. So, your first query is just equivalent to

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

另请参见小提琴

这篇关于内部连接内部的行为存在sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！