子查询联接中的差异可见性以及位置 [英] Difference visibility in subquery join and where

问题描述

我在进行简单的连接时遇到了问题:

I had problems with a simple join:

SELECT * 
FROM worker wo
WHERE EXISTS (
    SELECT wp.id_working_place
    FROM working_place wp 
    JOIN working_place_worker wpw ON ( wp.id_working_place = wpw.id_working_place
        AND wpw.id_worker = wo.id_worker)
)

我的错误是ORA-00904: "WO"."ID_WORKER": not valid identifier.

然后我决定将表的并集从join子句移到where子句:

Then I decided to move the union of tables from join clause to the where clause:

SELECT * 
FROM worker wo
WHERE EXISTS (
    SELECT wp.id_working_place
    FROM working_place wp 
    JOIN working_place_worker wpw ON ( wp.id_working_place = wpw.id_working_place)
    WHERE wpw.id_worker = wo.id_worker
)

最后一个查询works非常完美.

And this last query works perfect.

为什么不能在join中创建它?该表应该像在where子句中一样可见.我想念什么吗?

Why is not possible to make it in the join? The table should be visible like it is in the where clause. Am I missing something?

推荐答案

在

FROM working_place wp 
JOIN working_place_worker wpw ON ... 
WHERE ...

ON子句仅引用 参与连接的两个表，即wp和wpw.来自外部查询的名称对其不可见.

the ON clause refers only to the two tables participating in the join, namely wp and wpw. Names from the outer query are not visible to it.

WHERE子句(及其表亲HAVING是将外部查询与子查询相关联的方法.外部查询的名称对它们来说是可见的.

The WHERE clause (and its cousin HAVING is the means by which the outer query is correlated to the subquery. Names from the outer query are visible to it.

为了便于记忆，

• ON是关于JOIN的，两个表如何关联以形成一行(或多行)
• 关于选择标准的地方，测试行必须通过

尽管SQL解析器将在ON子句中接受文字(不是列名)，但它会在对联接之外的列的引用处绘制直线.您可以将其视为防止错误的支持.

While the SQL parser will admit literals (which aren't column names) in the ON clause, it draws the line at references to columns outside the join. You could regard this as a favor that guards against errors.

在您的情况下，wo表不是JOIN的一部分，因此被拒绝.它是整个查询的一部分，并且被WHERE识别.

In your case, the wo table is not part of the JOIN, and is rejected. It is part of the whole query, and is recognized by WHERE.

这篇关于子查询联接中的差异可见性以及位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！