Oracle SQL - Table Join Cartesian Products

Introduction

The following join sql uses tuple variables e and d over both tables.

Because there is no constraining the WHERE clause; therefore, the query result we get is the Cartesian product of both tables, resulting in 56 rows.

We have 14 emp and 4 departments, and 14 times 4 results in 56 possible combinations of all rows of emp and all rows of departments.

Demo

SQL>
SQL>-- from ww w.jav a2  s . co m
SQL> drop table emp;

Table dropped.

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> drop table departments;

Table dropped.

SQL>
SQL> create table departments(
  2  deptno    NUMBER(2)     primary key,
  3  dname     VARCHAR2(10)  not null unique check (dname = upper(dname)),
  4  location  VARCHAR2(8)   not null        check (location = upper(location)),
  5  mgr       NUMBER(4)) ;
SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007);
SQL> insert into departments values (20,'TRAINING',  'DALLAS',  7004);
SQL> insert into departments values (30,'SALES',     'CHICAGO', 7006);
SQL> insert into departments values (40,'HR',        'BOSTON',  7009);
SQL>
SQL>
SQL> select d.deptno, d.location, e.ename, e.init
  2  from   emp e, departments d;

   DEPTNO | LOCATION | ENAME    | INIT
--------- | -------- | -------- | -----
    1     | NEW YORK | SMITH    | N
    1     | NEW YORK | ALLEN    | JAM
    1     | NEW YORK | WARD     | TF
    1     | NEW YORK | JACK     | JM
    1     | NEW YORK | BROWN    | P
    1     | NEW YORK | BLAKE    | R
    1     | NEW YORK | CLARK    | AB
    1     | NEW YORK | SCOTT    | DEF
    1     | NEW YORK | KING     | CC
    1     | NEW YORK | BREAD    | JJ
    1     | NEW YORK | ADAMS    | AA

   DEPTNO | LOCATION | ENAME    | INIT
--------- | -------- | -------- | -----
    1     | NEW YORK | JONES    | R
    1     | NEW YORK | FORD     | MG
    1     | NEW YORK | MARY     | ABC
    2     | DALLAS   | SMITH    | N
    2     | DALLAS   | ALLEN    | JAM
    2     | DALLAS   | WARD     | TF
    2     | DALLAS   | JACK     | JM
    2     | DALLAS   | BROWN    | P
    2     | DALLAS   | BLAKE    | R
    2     | DALLAS   | CLARK    | AB
    2     | DALLAS   | SCOTT    | DEF

   DEPTNO | LOCATION | ENAME    | INIT
--------- | -------- | -------- | -----
    2     | DALLAS   | KING     | CC
    2     | DALLAS   | BREAD    | JJ
    2     | DALLAS   | ADAMS    | AA
    2     | DALLAS   | JONES    | R
    2     | DALLAS   | FORD     | MG
    2     | DALLAS   | MARY     | ABC
    30    | CHICAGO  | SMITH    | N
    30    | CHICAGO  | ALLEN    | JAM
    30    | CHICAGO  | WARD     | TF
    30    | CHICAGO  | JACK     | JM
    30    | CHICAGO  | BROWN    | P

   DEPTNO | LOCATION | ENAME    | INIT
--------- | -------- | -------- | -----
    30    | CHICAGO  | BLAKE    | R
    30    | CHICAGO  | CLARK    | AB
    30    | CHICAGO  | SCOTT    | DEF
    30    | CHICAGO  | KING     | CC
    30    | CHICAGO  | BREAD    | JJ
    30    | CHICAGO  | ADAMS    | AA
    30    | CHICAGO  | JONES    | R
    30    | CHICAGO  | FORD     | MG
    30    | CHICAGO  | MARY     | ABC
    4     | BOSTON   | SMITH    | N
    4     | BOSTON   | ALLEN    | JAM

   DEPTNO | LOCATION | ENAME    | INIT
--------- | -------- | -------- | -----
    4     | BOSTON   | WARD     | TF
    4     | BOSTON   | JACK     | JM
    4     | BOSTON   | BROWN    | P
    4     | BOSTON   | BLAKE    | R
    4     | BOSTON   | CLARK    | AB
    4     | BOSTON   | SCOTT    | DEF
    4     | BOSTON   | KING     | CC
    4     | BOSTON   | BREAD    | JJ
    4     | BOSTON   | ADAMS    | AA
    4     | BOSTON   | JONES    | R
    4     | BOSTON   | FORD     | MG

   DEPTNO | LOCATION | ENAME    | INIT
--------- | -------- | -------- | -----
    4     | BOSTON   | MARY     | ABC

56 rows selected.

SQL>

• Previous
• Next