Oracle PL/SQL Tutorial/Table Joins/Cartesian Products
Cartesian Products
If a join condition is missing, you will end up selecting all rows from one table joined to all the rows in the other table.
This kind of situation is known as a Cartesian product.
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 )
9 /
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)
2 values (1, "Jason", to_date("19960725","YYYYMMDD"), 1234, 8767, "E")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, "John", to_date("19970715","YYYYMMDD"), 2341, 3456, "W")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, "Joe", to_date("19860125","YYYYMMDD"), 4321, 5654, "E")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, "Tom", to_date("20060913","YYYYMMDD"), 2413, 6787, "W")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, "Jane", to_date("20050417","YYYYMMDD"), 7654, 4345, "E")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, "James", to_date("20040718","YYYYMMDD"), 5679, 6546, "W")
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, "Jodd", to_date("20030720","YYYYMMDD"), 5438, 7658, "E")
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
---------- --------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
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 j. empno, e.empno FROM
2 job j, employee e;
EMPNO EMPNO
---------- ----------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 1
2 2
2 3
2 4
2 5
2 6
2 7
2 8
2 9
3 1
3 2
3 3
3 4
3 5
3 6
3 7
3 8
3 9
4 1
4 2
4 3
4 4
4 5
4 6
4 7
4 8
4 9
5 1
5 2
5 3
5 4
5 5
5 6
5 7
5 8
5 9
6 1
6 2
6 3
6 4
6 5
6 6
6 7
6 8
6 9
7 1
7 2
7 3
7 4
7 5
7 6
7 7
7 8
7 9
8 1
8 2
8 3
8 4
8 5
8 6
8 7
8 8
8 9
9 1
9 2
9 3
9 4
9 5
9 6
9 7
9 8
9 9
81 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL> drop table job
2 /
Table dropped.
SQL>