合并查询的简单问题

Question

SQL>  select cl090_son_sub from cl090 where cl090_son_sub in (select cl120_son_sub from cl120  where cl120_depart_no='cw' and cl120_school='yz' and cl120_yymm='200511');CL090_SON_
----------
0201
0801SQL> select cl080_sub_no from cl080 where cl080_sub_no  in (select cl120_sub_no from cl120 where cl120_depart_no='EM01' and cl120_school='EM' and cl120_yymm='200511');CL080_
------
02
08SQL> select a.cl080_sub_no,b.cl090_son_sub from cl080 a,cl090 b 
where 
a.cl080_sub_no in 
 (select cl120_sub_no from cl120 where cl120_depart_no='cw' and cl120_school='yz' and cl120_yymm='200511')
and 
b.cl090_son_sub in (select cl120_son_sub from cl120  where cl120_depart_no='cw' and cl120_school='yz' and cl120_yymm='200511');CL080_ CL090_SON_
------ ----------
02     0201
08     0201
02     0801
08     0801<问题>
如何才能得到:
CL080_ CL090_SON_
------ ----------
02     0201
08     0801

Answer 1

CL080_ CL090_SON_
------ ----------
02 0201
08 0201
02 0801
08 0801
没有关联条件,得到前两个结果集的笛卡尔集.SQL> create table testa as select * from v$option where rownum<3;表被创建SQL> select * from testa;PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning                                                     TRUE
Objects                                                          TRUESQL> create table testb as select * from v$option where rownum<3;表被创建SQL> select a.parameter,b.parameter as val from testa a,testb b;PARAMETER                                                        VAL
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning                                                     Partitioning
Partitioning                                                     Objects
Objects                                                          Partitioning
Objects                                                          ObjectsSQL> select r1.parameter,r2.parameter as val from (select rownum rn,a.* from testa a) r1,(select rownum rn,b.* from testb b) r2 where r1.rn=r2.rn;PARAMETER                                                        VAL
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning                                                     Partitioning
Objects                                                          Objects