CODEWITHSUNDEEP
sqloracle-database
David
David

Reputation: 117

SQL Oracle IN operator behavior

I use Oracle and I can't explain this behavior :

SELECT DUMMY 
FROM   DUAL 
WHERE  DUMMY IN (SELECT DUMMY FROM z_sites);

Returns

DUMMY
-----
X

And this :

SELECT DUMMY FROM z_sites;

Returns

ORA-00904: "DUMMY" : identificateur non valide 00904. 00000 - "%s: invalid identifier"

So on my first query, the subquery is false, but the query still excutes itself ?!

Upvotes: 3

Views: 116

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

It is all about scope:

SELECT DUMMY 
FROM   DUAL 
WHERE  DUMMY IN (SELECT DUMMY FROM z_sites);

is same as:

SELECT DUMMY 
FROM   DUAL 
WHERE  DUMMY IN (SELECT DUAL.DUMMY FROM z_sites);

Whereas:

SELECT DUMMY FROM z_sites;
-- there is no dummy column in z_sites table

To make thing more interesting you could check what will happen if z_sites contains dummy column:

CREATE TABLE z_sites(dummy VARCHAR2(100));
INSERT INTO z_sites(dummy) VALUES('Y');

SELECT DUMMY 
FROM   DUAL 
WHERE  DUMMY IN (SELECT DUMMY FROM z_sites);
-- 0 rows selected
<=>
SELECT DUMMY 
FROM   DUAL 
WHERE  DUMMY IN (SELECT z_sites.DUMMY FROM z_sites);
-- 0 rows selected

SELECT DUMMY 
FROM   DUAL 
WHERE  DUMMY IN (SELECT DUAL.DUMMY FROM z_sites);

DUMMY
-----
X

DBFiddle Demo

Summing up: always prefix your columns in subquery to avoid confusion.

Upvotes: 6

Related Questions