 |
|
Oracle Tips by Burleson |
Cartesian Product
The Cartesian product,
also referred to as a cross-join, returns all the rows in all the
tables listed in the query. Each row in the first table is paired
with all the rows in the second table. This happens when there is no
relationship defined between the two tables. Both the AUTHOR and
STORE tables have ten rows. If we use a Cartesian join in these two
tables, we will get back 100 rows.
SQL> select
2 author_key,
3 store_key
4 from
5 author, store;
AUTHOR_KEY
STOR
----------- ----
A101 S101
A101 S102
A101 S103
A101 S104
A101 S105
A101 S106
A101 S107
A101 S108
A101 S109
A101 S110
A102 S101
A102 S102
…
A110 S105
A110 S106
A110 S107
A110 S108
A110 S109
A110 S110
100 rows
selected.
Most of the time, we do not want a Cartesian join,
and we end up with one because we failed to provide a filter on the
join. If we actually want a Cartesian join, then we should use the
ANSI cross join to tell others reading the script that we
actually wanted a Cartesian join.
select
author_key,
store_key
from
author cross join store;
One reason to use a Cartesian join is to generate
a large amount of rows to use for testing. I can take a large table
and cross join it to another large table and produce a very large
results set. If I cross join
dba_objects and
dba_views, I can produce the results set below.
SQL> select
count(*)
2 from
3 dba_objects cross join dba_views;
COUNT(*)
----------
164623840
That’s a lot of rows!
Remember, if you
forget to specify your join criteria, you may get a Cartesian
result!
Most of the time, we will want to join our tables
on some meaningful key. SQL provides a number of join methods, like
the natural join, relate multiple tables. While the natural join can
be confusing and possibly error prone, the equal join clearly defines
how we want to join our table and on what columns.
The above text is
an excerpt from:
Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
by
John Garmany
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
