create table a (AID number(8),A1 varchar2(10),A2 varchar2(10));create table b (BID number(8),B1 varchar2(10),B2 varchar2(10));insert into a select 1,'A1','A2' from dual union select 2,'B1','B2' from dual union select 3,'C1','C2' from dual;insert into b select 1,'A3','A4' from dual; union select 2,'B3','B4' from dual;select a.aid,a.a1,a.a2,b.b1,b.b2 from a left join b on a.aid=b.bid;
---------- ----------
a1 a2
b1 b2
c1 c2SQL> select * from b;ID1 ID2
---------- ----------
a3 a4
b3 b4
select t2.*,t1.id1,t1.id2
from (select substr(id1,1,1) id,id1,id2 from b) t1,a t2
where substr(t2.id1,1,1) = t1.id (+)SQL>
4 /ID1 ID2 ID1 ID2
---------- ---------- ---------- ----------
a1 a2 a3 a4
b1 b2 b3 b4
c1 c2
union select 2,'B1','B2' from dual
union select 3,'C1','C2' from dual;insert into b select 1,'A3','A4' from dual;
union select 2,'B3','B4' from dual;select a.aid,a.a1,a.a2,b.b1,b.b2
from a left join b on a.aid=b.bid;