CODEWITHSUNDEEP
sqloracle-databasejoinouter-join
swet
swet

Reputation: 217

Oracle - inner and left join

create table t1 (v varchar2(500), n number);
Insert into T1 (V,N) values ('a',1);
Insert into T1 (V,N) values ('bb',2);
Insert into T1 (V,N) values ('c',3);
Insert into T1 (V,N) values ('d',4);

create table t2 (v varchar2(500), n number);
Insert into T2 (V,N) values ('a',1);
Insert into T2 (V,N) values ('bb',2);

select * from t1 join t2 on t1.v = t2.v 
union all
select * from t1 LEFT join t2 on t1.v = t2.v ;

Output:

    a     1     a         1
    bb    2     bb        2
    a     1     a         1
    bb    2     bb        2
    d     4     (null)  (null)
    c     3     (null)  (null)

Can we get the same above output from single scan of T1 and T2 ie from single query without UNION ALL etc? Want to re-write the above Select query so that it scans the tables T1 and T2 only once and give the same result. See the LEFT join.

The output cant be changed as we are passing it further in the application, duplicate data is required as per the requirement.

Upvotes: 2

Views: 58

Answers (2)

wolfrevokcats
wolfrevokcats

Reputation: 2100

You can avoid excess joins and unions by doubling the rows:

select t1.*,t2.* from t1
  left join t2 on t1.v=t2.v
  cross join (select 1 as dbl from dual 
            union select 2 as dbl from dual) dbl
where dbl=1 or t2.v is not null

Upvotes: 0

APC
APC

Reputation: 146229

" Want to re-write the above Select query so that it scans the tables T1 and T2 only once"

You could use subquery factoring . The WITH clauses read each table once and the UNION-ed queries read from them:

with cte1 as ( select * from t1 )
   , cte2 as ( select * from t2 ) 
select * from cte1 join cte2 on cte1.v = cte2.v 
union all
select * from cte1 LEFT join cte2 on cte1.v = cte2.v ;

Here is a SQL Fiddle demo.

Upvotes: 1

Related Questions