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)
)
The error I had was ORA-00904: "WO"."ID_WORKER": not valid identifier
.
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
)
And this last query works
perfect.
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?
In
FROM working_place wp
JOIN working_place_worker wpw ON ...
WHERE ...
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.
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.
To make it easy to remember,
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.
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
.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments