create table TAB1
( col1 VARCHAR2(5),col2 VARCHAR2(5), col3 VARCHAR2(5), col4 VARCHAR2(5),
col5 VARCHAR2(5)) ;
create table TAB2
( col1 VARCHAR2(5), col2 VARCHAR2(5), col3 VARCHAR2(5),
col4 VARCHAR2(5), col5 VARCHAR2(5)) ;
insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('a', 'a', 'a', 'a', 'a');
insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('b', 'b', 'b', 'b', 'b');
insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', null, 'c');
insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('d', 'd', 'd', 'd', 'd');
insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('e', 'e', 'e', 'e', 'e');
insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('a', 'a', 'a', 'a', 'a');
insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('b', 'b', 'b', 'b', 'b');
insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', 'c', 'c');
insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', 'c', 'c');
commit;
---------------------------
select tab1.*,tab2.* from tab1 left join tab2
on tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2
and tab1.col3 <> 'c';
select tab1.*,tab2.* from tab1 left join tab2
on tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2
and tab1.col3 <> 'c';
select tab1.*,tab2.* from tab1 , tab2
where tab1.col1 = tab2.col1 (+)
and tab1.col2 = tab2.col2 (+)
and tab2.col3 (+)<> 'c';
select tab1.*,tab2.* from tab1 , tab2
where tab1.col1 = tab2.col1 (+)
and tab1.col2 = tab2.col2 (+)
and tab1.col3 (+)<> 'c';
( col1 VARCHAR2(5),col2 VARCHAR2(5), col3 VARCHAR2(5), col4 VARCHAR2(5),
col5 VARCHAR2(5)) ;
create table TAB2
( col1 VARCHAR2(5), col2 VARCHAR2(5), col3 VARCHAR2(5),
col4 VARCHAR2(5), col5 VARCHAR2(5)) ;
insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('a', 'a', 'a', 'a', 'a');
insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('b', 'b', 'b', 'b', 'b');
insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', null, 'c');
insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('d', 'd', 'd', 'd', 'd');
insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('e', 'e', 'e', 'e', 'e');
insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('a', 'a', 'a', 'a', 'a');
insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('b', 'b', 'b', 'b', 'b');
insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', 'c', 'c');
insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', 'c', 'c');
commit;
---------------------------
select tab1.*,tab2.* from tab1 left join tab2
on tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2
and tab1.col3 <> 'c';
select tab1.*,tab2.* from tab1 left join tab2
on tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2
and tab1.col3 <> 'c';
select tab1.*,tab2.* from tab1 , tab2
where tab1.col1 = tab2.col1 (+)
and tab1.col2 = tab2.col2 (+)
and tab2.col3 (+)<> 'c';
select tab1.*,tab2.* from tab1 , tab2
where tab1.col1 = tab2.col1 (+)
and tab1.col2 = tab2.col2 (+)
and tab1.col3 (+)<> 'c';
No comments:
Post a Comment