Databases Reference
In-Depth Information
148 Cambrault 100 King
...
177 Livingston 149 Zlotkey
176 Taylor 149 Zlotkey
175 Hutton 149 Zlotkey
174 Abel 149 Zlotkey
202 Fay 201 Hartstein
206 Gietz 205 Higgins
106 rows selected.
Not unexpectedly, she gets the same results as she did with the pre-Oracle9
i
version of the query.
Cartesian Products: The Black Sheep of the Family
What if you were joining two tables, or even three tables, and you left off the join
conditions? The result would be a
Cartesian product
. Every row of each table in
the FROM clause would be joined with every row of the other tables. If one table had
15 rows, and a second table had 21 rows, a Cartesian product of those two tables
would produce 315 rows in the result set of the query. Needless to say, it can be a
big problem when you have three or more tables with no join conditions specified.
Cartesian product
A join between two tables where no join
condition is specified, and as a result,
every row in the first table is joined with
every row in the second table.
Partial Cartesian products are produced when a query with
n
tables has less than
n-1
join conditions between tables.
Needless to say, Cartesian products are used quite infrequently in SELECT
statements, but they can be useful in very specific situations. For example, a Car-
tesian product of the EMPLOYEES table and the COUNTRIES table could give Janice
a way to produce a checklist in a spreadsheet to note when a particular employee
has visited one of the countries where Scott's widget company has a field office
or distribution center. If employee visits to other offices were tallied in another
table, then the Cartesian product could be joined to the new table as a running
total of visits by employees to other offices.
Pre-Oracle9
i
Cartesian Product Syntax
Janice decides that the employee/country visit idea has some merit, and she
experiments with some queries to generate the combinations of employees and
countries using a Cartesian product query:
select e.employee_id "Emp ID", e.last_name "Emp Name",
c.country_id "Cntry ID", c.country_name "Cntry Name"
from employees e, countries c;
