Thanks for the question, Bhagat .
Asked: February 05, 2005 - 6:25 am UTC
Last updated: February 05, 2005 - 6:31 am UTC
Version: 8.1.7
Viewed 1000+ times
You Asked
Hello Tom,
The query is this that I have a table say t
create table t ( col1 number, col2 number);
insert into t values (1,1);
insert into t values (2,2);
say another table t2
create table t1 ( col3 number);
insert into t1 values (1);
SQL> create table t ( col1 number, col2 number);
Table created.
SQL> insert into t values (1,1);
1 row created.
SQL> insert into t values (2,2);
1 row created.
SQL> drop table t1;
Table dropped.
SQL> create table t1 ( col3 number);
Table created.
SQL> insert into t1 values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select col1,col2 from t where col1 in ( select col1 from t1);
COL1 COL2
---------- ----------
1 1
2 2
it gives this result where as there is not column in t1 which says col1.
Can you tell about this behaviour of the sql it is a bug or I am looking from wrong side of the coin.
Regards
Bhagat Singh
and Tom said...
that is called a correlated subquery, and when used properly, is a very powerful feature.
your query:
select col1,col2 from t where col1 in ( select col1 from t1);
is really:
select T.col1, T.col2 from t where T.col1 in ( select T.col1 from t1 );
so, as long as t1 has at least one row - that query will behave just like:
select T.col1, T.col2 from t where T.col1 is not null;
it'll return all non-null rows from T.
Correlated subqueries are very powerful for expressing certain types of questions, for example:
select * from dept where exists ( select null from emp where emp.deptno=dept.deptno);
shows all depts that have at least one employee. or:
select *
from t
where t.y in ( select t2.y
from t2
where t2.x = t.x );
show me all rows in T where T.Y is in the set of Y's from T2 where T2.X = T.X
(suggestion: use correlation names on all colums and you'll get a couple of very real benefits:
a) when you ask someone to look at the query and help you tune/debug it, it'll be very very very clear what columns come from which tables
b) you won't 'accidently' create a bug like you did with this example....
Is this answer out of date? If it is, please let us know via a Comment