Inline view limitations

Question

SELECT in_ev.type, pl.name, in_ev.year
FROM places pl INNER JOIN (SELECT e.type, e.place_id, e.year 
                           FROM events e)in_ev ON in_ev.place_id=pl.place_id
WHERE EXISTS (SELECT 1 FROM in_ev sub_ev WHERE sub_ev.year=1994)

I'm trying to understand how inline views are processed and why we cannot use them as a "source table" in where clause subqueries. When I try to run this query I get ORA-00942: table or view does not exist

Above snippet is only for illustration reasons.

What is the exact difference in storage and processing between ordinary tables and inline views?

Answer 1

The subquery in the where clause just can't see the inline view, which I guess you've figured out; it's a scope issue, really. There is no storage, and how it's managed in memory and processed is somewhat down to the optimiser - it may substantially rewrite things, for instance. The subquery might logically be evaluated before the inline view is evaluated, I suppose.

As the parser is throwing the ORA-00942 before the query is actually executed, how the inline view is handled is a bit of a moot point.

You can use subquery factoring instead:

WITH in_ev AS (
  SELECT e.type, e.place_id, e.year 
  FROM events e
)
SELECT in_ev.type, pl.name, in_ev.year
FROM places pl INNER JOIN in_ev ON in_ev.place_id=pl.place_id
WHERE EXISTS (SELECT 1 FROM in_ev sub_ev WHERE sub_ev.year=1994)

Which looks odd with such a contrived example, but you acknowleged that it's illustrative in the question. The subquery in the where clause can see the CTE - it is in scope to the parser.

As @mathguy noted in a comment, the optimiser may still treat the CTE as a subquery, but that's not normally something you need to worry about - particularly if you're just trying to avoid the error you were getting.