hemalp108 hemalp108 - 2 months ago 6
SQL Question

Invalid Subquery yet outer query returns the records

I've two table EMP & DEPT. Below are the descriptions.

**EMP table:**

EMP_ID NUMBER
EMP_NAME VARCHAR2(4)

**DEPT table:**

DEPT_ID NUMBER
DEPT_NAME VARCHAR2(4)


when i run the below query it executed successfully even though the subquery is invalid.

SELECT * FROM EMP WHERE EMP_ID IN (SELECT SAL FROM DEPT);


I am expecting ORA-00904 : invalid identifier, but it returns the records from EMP table.

Answer

This is because Oracle supports correlated subqueries one level deep. So the scope of the columns in the outer query includes the subquery in your example.

The query you wrote is equivalent to:

SELECT *
FROM   EMP
WHERE  EMP_ID IN (SELECT EMP.SAL
                  FROM   DEPT);

It also highlights the need to adequately alias your queries - if you had written:

SELECT *
FROM   EMP
WHERE  EMP_ID IN (SELECT DEPT.SAL
                  FROM   DEPT);

then you would have gotten the error you were expecting.

Comments