Q

Subselect from DUAL not giving an error

I created two tables, one is called "employees":

 create table employees 
 (code number primary key , name varchar2(10)); 
 and another table department 
  create table department 
   (code number references employees (code), dname varchar2(10)); 

After creating these two tables, I inserted some rows in these tables like in the employee table:

  1234 Peter 
  9999 Peter 

...and in the department table

  1234 Computer 
  1234 Accounting 

Then I write a query:

 
 select distinct e.code, d.dname 
  from employee e, department d 
   where e.deptno=d.deptno 
    and 
dname in (select dname from dual where dname='Computer'); 

This query gives me one record. My question is why the subquery returns a row rather then returning the error because in the dual table (or you can place any existing table_name in place of dual) there is no column named dname. If you can place emp table in place of dual, the answer is the same. Please tell me why this happens.


That's a very good question, and I don't know the answer, but I would like to make a comment on your query.

First of all, I tried the subquery on its own. It did produce an error, just as expected --

select dname from dual where dname='Computer' 
                             * 
ERROR at line 1: 
ORA-00904: invalid column name

Then I tried the query without the subquery (after fixing the syntax errors; you were using columns called deptno instead of code as defined in your tables). Oracle returned both rows, again as expected --

select distinct e.code, d.dname 
  from employees e, department d 
  where e.code=d.code ; 
      CODE DNAME 
---------- ---------- 
      1234 Accounting 
      1234 Computer

But then I tried the query with the subquery. And it worked! --

select distinct e.code, d.dname 
  from employees e, department d 
where e.code=d.code 
   and dname in (select dname from dual where dname='Computer'); 
      CODE DNAME 
---------- ---------- 
      1234 Computer

I'm not an Oracle expert, so I don't know why it doesn't give an error in this case.

However, I really have to wonder why you couldn't just have removed the subquery altogether! --

select distinct e.code, d.dname 
  from employees e, department d 
where e.code=d.code 
   and dname ='Computer';

Logically, it is exactly the same. Perhaps the Oracle optimizer was smart enough to figure that out, too.


This was first published in August 2001

Dig deeper on Oracle error messages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close