From Oracle FAQ
A Cartesian join or Cartesian product is a join of every row of one table to every row of another table. This normally happens when no matching join columns are specified. For example, if table A with 100 rows is joined with table B with 1000 rows, a Cartesian join will return 100,000 rows.
Note: A Cartesian product may indicate a missing join condition. A query must have at least (N-1) join conditions to prevent a Cartesian product, where N is the number of tables in the query. However a Cartesian product may be something valid; for instance, in a star schema, a Cartesian join between dimension tables is not unusual.
Using Oracle join syntax:
SELECT * FROM emp, dept;
SELECT * FROM emp, dept WHERE dept.deptno = 10 AND emp.sal > 10000;
Using ANSI join syntax:
SELECT * FROM emp CROSS JOIN dept;
|Glossary of Terms|