CODEWITHSUNDEEP
sqloracle-database
Neelima Sharma
Neelima Sharma

Reputation: 45

INTERSECT in ORACLE (SQL not PL/SQL)

I have to do INTERSECTION in between two select statements. The condition is intersection should take place only when both the select statements return some resultset. If any of the ones returns O rows Intersection should not work and the final result must be of the statement which has returned some rows. Is this possible in SQL(I do not need the answer in pl/sql)?

Upvotes: 2

Views: 453

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

You can achieve this using this construction.

with t1 as (select level id from dual where 1 = 0 connect by level <= 3),
     t2 as (select level id from dual where 1 = 1 connect by level <= 5)
select distinct * 
  from t1 
  where exists (select null from t2 where id = t1.id)
    or (select count(1) from t2) = 0
union all
select distinct * from t2 where (select count(1) from t1) = 0

t1 and t2 simulates some data. First select makes intersection or gets all distinct rows from first table if there is no data in t2. Second select (after union all) is to get all data from t2 if there are no rows in t1.

Edit: You can do it simplier:

select * from t1 
intersect 
select * from t2 
union 
select * from t1 where (select count(1) from t2) = 0
union 
select * from t2 where (select count(1) from t1) = 0

Upvotes: 1

Related Questions