Cartesian join

From Oracle FAQ
Jump to: navigation, search

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.

[edit] Syntax examples

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;

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #