gmyers,
This would not, by definition, be a cartesian join.
Cartesian Join : If two tables in a join query have no join condition. If a query
joins three or more tables and you do not specify a join condition for a specific pair,
the optimizer may choose a join order that avoids producing an intermediate
Cartesian product.
BobMc
The only time I have used a cartesian join is if I needed counts of tables.
Example:
Select Count_Tab1, Count_Tab2, Count_Tab3
from (select count(1) Count_Tab1 from table1 where ... ),
(select count(1) Count_Tab2 from table2 where ... ),
(select count(1) Count_Tab3 from table3 where ... )
;
Main Topics
Browse All Topics
by: gmyersPosted on 2002-12-30 at 15:57:38ID: 7648328
One candidate is a matrix report where the detail table can't be used to 'drive' it.
create table customer (name varchar2(100));
create table product (name varchar2(100));
create table orders (cust_name varchar2(100), prod_name varchar2(100), qty number);
insert into customer values ('fred');
insert into customer values ('bill');
insert into customer values ('john');
insert into customer values ('tom');
insert into product values ('bread');
insert into product values ('milk');
insert into product values ('butter');
insert into product values ('eggs');
insert into product values ('flour');
insert into orders values ('fred','bread',75);
insert into orders values ('fred','bread',25);
insert into orders values ('fred','flour',90);
insert into orders values ('bill','milk',20);
insert into orders values ('bill','eggs',100);
insert into orders values ('bill','flour',130);
insert into orders values ('john','bread',50);
insert into orders values ('john','milk',10);
insert into orders values ('john','eggs',117);
insert into orders values ('john','flour',500);
select cp.cust_name, cp.prod_name, nvl(sum(orders.qty),0)
from orders,
(select customer.name cust_name, product.name prod_name
from customer, product) cp
where orders.cust_name(+) = cp.cust_name
and orders.prod_name(+) = cp.prod_name
group by cp.cust_name, cp.prod_name