unexpected results !

It makes you cry! It makes you claim you have found a bug! but it is working as specified!

1) subquery refers to a column of the main query
select * from emp where ename in (select ename from dept where deptno=10);

the query does not complain that column does not exist in dept. It is perfectly legal to specify a non-prefixed column of the main query in the subquery. This could be like

select * from tab where 'foo' in (select 'foo' from dict);

so the “in” clause is always true

2) “not in” does not deliver result

select sysdate from dual where 400 not in (select comm from emp);

this is because 400!=null is UNKOWN. Check the 3 state booleans operations!

it could be rewritten with a not exists, or in 10g with LNNVL

select sysdate from dual where lnnvl(400 in (select comm from emp));

3) rows are not delivered in the correct order
it is a wrong assumption to think that the rows will be delivered in a specific order if you do not specify order by.

4) table not found


SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
GreatTable TABLE

SQL> desc GreatTable
ERROR:
ORA-04043: object GreatTable does not exist


Whoever it is, someone created a case sensitive table name.


SQL> desc "GreatTable"
Name Null? Type
----------------------- -------- ----------------
MagicColumn NUMBER(38,2)