0

The below code returns outcome with no errors in the process. However, the variable b is not present in the view nob.

WITH basetbl AS (
    SELECT
        1 AS a,
        2 AS b,
        1 AS c
    FROM
        dual
    UNION ALL
    SELECT
        2 AS a,
        3 AS b,
        0 AS c
    FROM
        dual
),nob AS (
    SELECT a, c
    FROM basetbl
) 
SELECT *
FROM basetbl
WHERE b IN (SELECT b FROM nob WHERE c = 1);

Returns:

"A"  "B"  "C"                           
 1    2    1                           
 2    3    0
skywhalecommando
  • 3
  • 2

1 Answers1

1

This is your query:

SELECT *
FROM basetbl
WHERE b IN (SELECT b FROM nob WHERE c = 1);

First, you should always qualify column names, so the query should look more like this:

SELECT bt.*
FROM basetbl bt
WHERE bt.b IN (SELECT n.b FROM nob n WHERE n.c = 1);

Then you would get an error.

Instead, Oracle applies its default scoping rules (which are similar to the scoping rules for all SQL databases). So, when it doesn't find b in nob, it looks in the outer query. The query is interpreted as:

SELECT bt.*
FROM basetbl bt
WHERE bt.b IN (SELECT bt.b FROM nob n WHERE n.c = 1);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This is not specific to Oracle, this is how the SQL standard defines the visibility rules. At least Postgres and SQL Server work exactly the same way. –  Apr 24 '19 at 13:12