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
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