1. Case when
select table_name,
(CASEWHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END) ownerDesc
from all_tables;
2. join
create table test1(
reason varchar(10);
volume number(5,0);
amount number (5,0)
)
create table test2(
reason varchar(10);
volume number(5,0);
amount number (5,0)
)
--left join
select * from test1 aa ,test2 bb
where aa.reason =bb.reason(+)
select * from test1 aa left join test2 bb on aa.reason=bb.reason
--further left join
select aa.reason,nvl(aa.volume,0)+ nvl(bb.volume,0) cnt,nvl(aa.amount,0)+ nvl(bb.amount,0) amt
from test1 aa left join test2 bb on aa.reason = bb.reason
--right join
select * from test1 aa ,test2 bb
where aa.reason(+) =bb.reason
select * from test1 aa right join test2 bb on aa.reason = bb.reason
--full join
select * from test1 aa ,test2 bb where aa.reason=bb.reason(+)
union
select * from test1 aa ,test2 bb where aa.reason(+)=bb.reason;