OraFAQ Forum: SQL & PL/SQL » subquey execution

Home » SQL & PL/SQL » SQL & PL/SQL » subquey execution (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

subquey execution [message #562852]

Mon, 06 August 2012 05:55

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All ,

Could any body please explain how the following query is executing fine, while the same sub query is giving error message .

SQL> 
SQL> select *  from scott.emp se where    se.ename  in
  2  (select ename   from  scott.dept  )
  3  ;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 12/17/1980     800.00               20
 7499 ALLEN      SALESMAN   7698 2/20/1981     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 2/22/1981     1250.00    500.00     30
 7566 JONES      MANAGER    7839 4/2/1981      2975.00               20
 7654 MARTIN     SALESMAN   7698 9/28/1981     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 5/1/1981      2850.00               30
 7782 CLARK      MANAGER    7839 6/9/1981      2450.00               10
 7788 SCOTT      ANALYST    7566 4/19/1987     3000.00               20
 7839 KING       PRESIDENT       11/17/1981    5000.00               10
 7844 TURNER     SALESMAN   7698 9/8/1981      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 5/23/1987     1100.00               20
 7900 JAMES      CLERK      7698 12/3/1981      950.00               30
 7902 FORD       ANALYST    7566 12/3/1981     3000.00               20
 7934 MILLER     CLERK      7782 1/23/1982     1300.00               10
 
14 rows selected
 
SQL> 

 SQL> select ename   from  scott.dept;
 
ORA-00904: "ENAME": invalid identifier
 
SQL>

Thanks
SaiPradyumn

Report message to a moderator

Re: subquey execution [message #562855 is a reply to message #562852]

Mon, 06 August 2012 06:04

Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

ENAME is "backtraced" to EMP table.

Report message to a moderator

Re: subquey execution [message #562856 is a reply to message #562855]

Mon, 06 August 2012 06:12

John Watson
Messages: 8984
Registered: January 2010
Location: Global Village

Senior Member

Can you explain that, LF? I generated an exec plan, and didn;t understand that, either:

orcl> set autot trace exp
orcl> select *  from scott.emp se where    se.ename  in (select ename   from  scott.dept  );

Execution Plan
----------------------------------------------------------
Plan hash value: 3689681934

--------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |     1 |    38 |    10   (0)| 00:00:01 |
|*  1 |  FILTER                    |         |       |       |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  3 |   FILTER                   |         |       |       |            |          |
|   4 |    INDEX FULL SCAN         | PK_DEPT |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "SCOTT"."DEPT" "DEPT" WHERE :B1=:B2))
   3 - filter(:B1=:B2)

orcl>

Report message to a moderator

Re: subquey execution [message #562857 is a reply to message #562856]

Mon, 06 August 2012 06:21

Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Slightly rewritten (I added table aliases), query is equal to this:

SQL> select *
  2  from emp e
  3  where e.ename in (select e.ename from dept d);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.1980 00:00:00       1600                    20
      7499 ALLEN      SALESMAN        7698 20.02.1981 00:00:00       2400        300         30
      7521 WARD       SALESMAN        7698 22.02.1981 00:00:00       2050        500         30
      7566 JONES      MANAGER         7839 02.04.1981 00:00:00       3775                    20
      7654 MARTIN     SALESMAN        7698 28.09.1981 00:00:00       2050       1400         30
      7698 BLAKE      MANAGER         7839 01.05.1981 00:00:00       3650                    30
      7782 CLARK      MANAGER         7839 09.06.1981 00:00:00       3250                    10
      7788 SCOTT      ANALYST         7566 09.12.1982 00:00:00       3800                    20
      7839 KING       PRESIDENT            17.11.1981 00:00:00       5800                    10
      7844 TURNER     SALESMAN        7698 08.09.1981 00:00:00       2300                    30
      7876 ADAMS      CLERK           7788 12.01.1983 00:00:00       1900                    20
      7900 JAMES      CLERK           7698 03.12.1981 00:00:00       1750                    30
      7902 FORD       ANALYST         7566 03.12.1981 00:00:00       3800                    20
      7934 MILLER     CLERK           7782 23.12.1982 00:00:00       2100                    10

14 rows selected.

Obviously, as there's no ENAME column in DEPT table:

SQL> select *
  2  from emp e
  3  where e.ename in (select d.ename from dept d);
where e.ename in (select d.ename from dept d)
                         *
ERROR at line 3:
ORA-00904: "D"."ENAME": invalid identifier


SQL>

So, if you remove "d." from "d.ename", I suppose that Oracle checks what it can - the EMP table. As it finds the ENAME column in there, query "works":

SQL> select *
  2  from emp e
  3  where e.ename in (select ename from dept d);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.1980 00:00:00       1600                    20
      7499 ALLEN      SALESMAN        7698 20.02.1981 00:00:00       2400        300         30
      7521 WARD       SALESMAN        7698 22.02.1981 00:00:00       2050        500         30
      7566 JONES      MANAGER         7839 02.04.1981 00:00:00       3775                    20
      7654 MARTIN     SALESMAN        7698 28.09.1981 00:00:00       2050       1400         30
      7698 BLAKE      MANAGER         7839 01.05.1981 00:00:00       3650                    30
      7782 CLARK      MANAGER         7839 09.06.1981 00:00:00       3250                    10
      7788 SCOTT      ANALYST         7566 09.12.1982 00:00:00       3800                    20
      7839 KING       PRESIDENT            17.11.1981 00:00:00       5800                    10
      7844 TURNER     SALESMAN        7698 08.09.1981 00:00:00       2300                    30
      7876 ADAMS      CLERK           7788 12.01.1983 00:00:00       1900                    20
      7900 JAMES      CLERK           7698 03.12.1981 00:00:00       1750                    30
      7902 FORD       ANALYST         7566 03.12.1981 00:00:00       3800                    20
      7934 MILLER     CLERK           7782 23.12.1982 00:00:00       2100                    10

14 rows selected.

SQL>

It would "work" for any other table, not just DEPT; for example

SQL> select *
  2  from emp e
  3  where e.ename in (select ename from v$version);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.1980 00:00:00       1600                    20
      7499 ALLEN      SALESMAN        7698 20.02.1981 00:00:00       2400        300         30
      7521 WARD       SALESMAN        7698 22.02.1981 00:00:00       2050        500         30
      7566 JONES      MANAGER         7839 02.04.1981 00:00:00       3775                    20
      7654 MARTIN     SALESMAN        7698 28.09.1981 00:00:00       2050       1400         30
      7698 BLAKE      MANAGER         7839 01.05.1981 00:00:00       3650                    30
      7782 CLARK      MANAGER         7839 09.06.1981 00:00:00       3250                    10
      7788 SCOTT      ANALYST         7566 09.12.1982 00:00:00       3800                    20
      7839 KING       PRESIDENT            17.11.1981 00:00:00       5800                    10
      7844 TURNER     SALESMAN        7698 08.09.1981 00:00:00       2300                    30
      7876 ADAMS      CLERK           7788 12.01.1983 00:00:00       1900                    20
      7900 JAMES      CLERK           7698 03.12.1981 00:00:00       1750                    30
      7902 FORD       ANALYST         7566 03.12.1981 00:00:00       3800                    20
      7934 MILLER     CLERK           7782 23.12.1982 00:00:00       2100                    10

14 rows selected.

SQL>

Report message to a moderator

Re: subquey execution [message #562858 is a reply to message #562856]

Mon, 06 August 2012 06:24

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As ename does not exist in dept, Oracle searches if it exists in the outer one, and indeed it exists in emp.
So the query is (internally) written as:

select * from scott.emp se where se.ename in (select se.ename from scott.dept);

which is equivalent to (what Oracle shows in the plan):

select * from scott.emp se where exists (select 0 from dept where se.ename = se.ename);

which is also equivalent to;

select * from scott.emp se where se.ename is not null and exists (select 1 from dept);

Regards
Michel

Report message to a moderator

Re: subquey execution [message #562859 is a reply to message #562858]

Mon, 06 August 2012 06:29

John Watson
Messages: 8984
Registered: January 2010
Location: Global Village

Senior Member

Thank you MC and LF. And thank you Sai for asking the question.

I learn sonething on Orafaq every day (though it is more often something about human nature than something technical).

Report message to a moderator

Previous Topic:	How to Retrieve the CLOB data when using DISTINCT Keyword for other columns in SQL
Next Topic:	How to maintain Serial Number.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 16 01:06:04 CDT 2025