I am having below table.
create table tbl1 ( col1 varchar2(20));
create table tbl2 ( col1 varchar2(20));
Insert into tbl1 (COL1) values ('1');
Insert into tbl1 (COL1) values ('1');
Insert into tbl1 (COL1) values ('1');
Insert into tbl1 (COL1) values ('2');
Insert into tbl1 (COL1) values ('3');
Insert into tbl1 (COL1) values (null);
Insert into tbl2 (COL1) values ('1');
Insert into tbl2 (COL1) values ('1');
Insert into tbl2 (COL1) values ('1');
Insert into tbl2 (COL1) values ('2');
Insert into tbl2 (COL1) values ('2');
Insert into tbl2 (COL1) values ('5');
Insert into tbl2 (COL1) values ('5');
i am running below query
select a.col1 /*+ use_band(a b) */
from tbl1 a , tbl b
where b.col1 between 4 and 6;
and i am getting 12 rows. But even without using /*+ use_band(a b) */ i am getting same 12 rows.
and also we can get this same result by cross join .
so i am having these questions:
A) Why using band join?
B) Is there any performance improvement compared to using inner/outer/left/right joins ?
C) Is there any thing going on in underlying mechanism compared to rest of the joins [inner/outer/left/right/cross]
Link for oracle docs: band_join