Omitting a join condition between two tables leads to a Cartesian product.
By using the SQL/92 join syntax, you avoid inadvertently producing a Cartesian product because you must always provide an ON or USING clause to join the tables.
In the following example, a Cartesian product is generated using the CROSS JOIN keywords.
SQL> -- create demo table
SQL> create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION VARCHAR2(1 BYTE),
8 MANAGER_ID NUMBER(3)
9 )
10 /
Table created.
SQL>
SQL> create table job (
2 EMPNO NUMBER(3),
3 jobtitle VARCHAR2(20 BYTE)
4 )
5 /
Table created.
SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (4,'COder');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (5,'Director');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (6,'Mediator');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (7,'Proffessor');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (8,'Programmer');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');
1 row created.
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (1, 'Jason', to_date('19960725','YYYYMMDD'), 1234, 8767, 'E', 2)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (2, 'John', to_date('19970715','YYYYMMDD'), 2341, 3456, 'W', 3)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (3, 'Joe', to_date('19860125','YYYYMMDD'), 4321, 5654, 'E', 3)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (4, 'Tom', to_date('20060913','YYYYMMDD'), 2413, 6787, 'W', 4)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (5, 'Jane', to_date('20050417','YYYYMMDD'), 7654, 4345, 'E', 4)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (6, 'James', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W', 5)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (7, 'Jodd', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E', 6)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (8, 'Joke', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W')
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (9, 'Jack', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E')
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R MANAGER_ID
---------- --------------- --------- ----------- ----------- - ----------
1 Jason 25-JUL-96 1234 8767 E 2
2 John 15-JUL-97 2341 3456 W 3
3 Joe 25-JAN-86 4321 5654 E 3
4 Tom 13-SEP-06 2413 6787 W 4
5 Jane 17-APR-05 7654 4345 E 4
6 James 18-JUL-04 5679 6546 W 5
7 Jodd 20-JUL-03 5438 7658 E 6
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E
9 rows selected.
SQL> select * from job
2 /
EMPNO JOBTITLE
---------- --------------------
1 Tester
2 Accountant
3 Developer
4 COder
5 Director
6 Mediator
7 Proffessor
8 Programmer
9 Developer
9 rows selected.
SQL>
SQL> SELECT * FROM employee CROSS JOIN job;
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R MANAGER_ID EMPNO JOBTITLE
---------- --------------- --------- ----------- ----------- - ---------- ---------- --------------------
1 Jason 25-JUL-96 1234 8767 E 2 1 Tester
1 Jason 25-JUL-96 1234 8767 E 2 2 Accountant
1 Jason 25-JUL-96 1234 8767 E 2 3 Developer
1 Jason 25-JUL-96 1234 8767 E 2 4 COder
1 Jason 25-JUL-96 1234 8767 E 2 5 Director
1 Jason 25-JUL-96 1234 8767 E 2 6 Mediator
1 Jason 25-JUL-96 1234 8767 E 2 7 Proffessor
1 Jason 25-JUL-96 1234 8767 E 2 8 Programmer
1 Jason 25-JUL-96 1234 8767 E 2 9 Developer
2 John 15-JUL-97 2341 3456 W 3 1 Tester
2 John 15-JUL-97 2341 3456 W 3 2 Accountant
2 John 15-JUL-97 2341 3456 W 3 3 Developer
2 John 15-JUL-97 2341 3456 W 3 4 COder
2 John 15-JUL-97 2341 3456 W 3 5 Director
2 John 15-JUL-97 2341 3456 W 3 6 Mediator
2 John 15-JUL-97 2341 3456 W 3 7 Proffessor
2 John 15-JUL-97 2341 3456 W 3 8 Programmer
2 John 15-JUL-97 2341 3456 W 3 9 Developer
3 Joe 25-JAN-86 4321 5654 E 3 1 Tester
3 Joe 25-JAN-86 4321 5654 E 3 2 Accountant
3 Joe 25-JAN-86 4321 5654 E 3 3 Developer
3 Joe 25-JAN-86 4321 5654 E 3 4 COder
3 Joe 25-JAN-86 4321 5654 E 3 5 Director
3 Joe 25-JAN-86 4321 5654 E 3 6 Mediator
3 Joe 25-JAN-86 4321 5654 E 3 7 Proffessor
3 Joe 25-JAN-86 4321 5654 E 3 8 Programmer
3 Joe 25-JAN-86 4321 5654 E 3 9 Developer
4 Tom 13-SEP-06 2413 6787 W 4 1 Tester
4 Tom 13-SEP-06 2413 6787 W 4 2 Accountant
4 Tom 13-SEP-06 2413 6787 W 4 3 Developer
4 Tom 13-SEP-06 2413 6787 W 4 4 COder
4 Tom 13-SEP-06 2413 6787 W 4 5 Director
4 Tom 13-SEP-06 2413 6787 W 4 6 Mediator
4 Tom 13-SEP-06 2413 6787 W 4 7 Proffessor
4 Tom 13-SEP-06 2413 6787 W 4 8 Programmer
4 Tom 13-SEP-06 2413 6787 W 4 9 Developer
5 Jane 17-APR-05 7654 4345 E 4 1 Tester
5 Jane 17-APR-05 7654 4345 E 4 2 Accountant
5 Jane 17-APR-05 7654 4345 E 4 3 Developer
5 Jane 17-APR-05 7654 4345 E 4 4 COder
5 Jane 17-APR-05 7654 4345 E 4 5 Director
5 Jane 17-APR-05 7654 4345 E 4 6 Mediator
5 Jane 17-APR-05 7654 4345 E 4 7 Proffessor
5 Jane 17-APR-05 7654 4345 E 4 8 Programmer
5 Jane 17-APR-05 7654 4345 E 4 9 Developer
6 James 18-JUL-04 5679 6546 W 5 1 Tester
6 James 18-JUL-04 5679 6546 W 5 2 Accountant
6 James 18-JUL-04 5679 6546 W 5 3 Developer
6 James 18-JUL-04 5679 6546 W 5 4 COder
6 James 18-JUL-04 5679 6546 W 5 5 Director
6 James 18-JUL-04 5679 6546 W 5 6 Mediator
6 James 18-JUL-04 5679 6546 W 5 7 Proffessor
6 James 18-JUL-04 5679 6546 W 5 8 Programmer
6 James 18-JUL-04 5679 6546 W 5 9 Developer
7 Jodd 20-JUL-03 5438 7658 E 6 1 Tester
7 Jodd 20-JUL-03 5438 7658 E 6 2 Accountant
7 Jodd 20-JUL-03 5438 7658 E 6 3 Developer
7 Jodd 20-JUL-03 5438 7658 E 6 4 COder
7 Jodd 20-JUL-03 5438 7658 E 6 5 Director
7 Jodd 20-JUL-03 5438 7658 E 6 6 Mediator
7 Jodd 20-JUL-03 5438 7658 E 6 7 Proffessor
7 Jodd 20-JUL-03 5438 7658 E 6 8 Programmer
7 Jodd 20-JUL-03 5438 7658 E 6 9 Developer
8 Joke 01-JAN-02 8765 4543 W 1 Tester
8 Joke 01-JAN-02 8765 4543 W 2 Accountant
8 Joke 01-JAN-02 8765 4543 W 3 Developer
8 Joke 01-JAN-02 8765 4543 W 4 COder
8 Joke 01-JAN-02 8765 4543 W 5 Director
8 Joke 01-JAN-02 8765 4543 W 6 Mediator
8 Joke 01-JAN-02 8765 4543 W 7 Proffessor
8 Joke 01-JAN-02 8765 4543 W 8 Programmer
8 Joke 01-JAN-02 8765 4543 W 9 Developer
9 Jack 29-AUG-01 7896 1232 E 1 Tester
9 Jack 29-AUG-01 7896 1232 E 2 Accountant
9 Jack 29-AUG-01 7896 1232 E 3 Developer
9 Jack 29-AUG-01 7896 1232 E 4 COder
9 Jack 29-AUG-01 7896 1232 E 5 Director
9 Jack 29-AUG-01 7896 1232 E 6 Mediator
9 Jack 29-AUG-01 7896 1232 E 7 Proffessor
9 Jack 29-AUG-01 7896 1232 E 8 Programmer
9 Jack 29-AUG-01 7896 1232 E 9 Developer
81 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL> drop table job
2 /
Table dropped.
SQL>
| 7.12.Cross Join SQL 92 Syntax |
| 7.12.1. | Performing Cross Joins Using SQL/92 |