Cross Joins Using SQL/92

To get a Cartesian product, use the CROSS JOIN keywords.


CREATE TABLE departments
(department_id             number(10)            not null,
 department_name           varchar2(50)      not null,
 CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

insert into departments ( department_id, department_name )values(1,'Data Group' );
insert into departments ( department_id, department_name )values(2,'Purchasing' );
insert into departments ( department_id, department_name )values(3,'Call Center' );
insert into departments ( department_id, department_name )values(4,'Communication' );

CREATE TABLE employees
( employee_id          number(10)      not null,
  last_name            varchar2(50)      not null,
  job_id               varchar2(30),
  department_id        number(10),
  salary               number(6),
  manager_id           number(6),
  CONSTRAINT           employees_pk PRIMARY KEY (employee_id),
  CONSTRAINT           fk_departments FOREIGN KEY (department_id) 
                       REFERENCES departments(department_id)
);
insert into employees( employee_id, last_name, job_id, salary,department_id ,manager_id)
values(                1001,        'Lawson',  'MGR',  30000, 1,             1004);
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values(                1002,        'Wells',   'DBA',  20000, 2,             1005 );
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values(                1003,        'Bliss',   'PROG', 24000, 3,             1004);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1004,        'Kyte',    'MGR',  25000 ,4,             1005);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1005,        'Viper',   'PROG', 20000, 1,             1006);
insert into employees( employee_id, last_name, job_id, salary, department_id,manager_id)
values(                1006,        'Beck',    'PROG', 20000, 2,             null);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1007,        'Java',    'PROG', 20000, 3,             1006);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1008,        'Oracle',  'DBA',  20000, 4,             1006);

SQL> SELECT *
  2  FROM employees CROSS JOIN departments;

EMPLOYEE_ID LAST_NAME  JOB_ID                         DEPARTMENT_ID     SALARY MANAGER_ID DEPARTMENT_ID   DEPARTMENT_NAME
----------- -------------------------------------------------------------- ---------- ---------- ------------------------
       1001 Lawson     MGR                                        1      30000       1004             1   Data Group
       1002 Wells      DBA                                        2      20000       1005             1   Data Group
       1003 Bliss      PROG                                       3      24000       1004             1   Data Group
       1004 Kyte       MGR                                        4      25000       1005             1   Data Group
       1005 Viper      PROG                                       1      20000       1006             1   Data Group
       1006 Beck       PROG                                       2      20000                        1   Data Group
       1007 Java       PROG                                       3      20000       1006             1   Data Group
       1008 Oracle     DBA                                        4      20000       1006             1   Data Group
       1001 Lawson     MGR                                        1      30000       1004             2   Purchasing
       1002 Wells      DBA                                        2      20000       1005             2   Purchasing
       1003 Bliss      PROG                                       3      24000       1004             2   Purchasing
       1004 Kyte       MGR                                        4      25000       1005             2   Purchasing
       1005 Viper      PROG                                       1      20000       1006             2   Purchasing
       1006 Beck       PROG                                       2      20000                        2   Purchasing
       1007 Java       PROG                                       3      20000       1006             2   Purchasing
       1008 Oracle     DBA                                        4      20000       1006             2   Purchasing
       1001 Lawson     MGR                                        1      30000       1004             3   Call Center
       1002 Wells      DBA                                        2      20000       1005             3   Call Center
       1003 Bliss      PROG                                       3      24000       1004             3   Call Center
       1004 Kyte       MGR                                        4      25000       1005             3   Call Center
       1005 Viper      PROG                                       1      20000       1006             3   Call Center
       1006 Beck       PROG                                       2      20000                        3   Call Center
       1007 Java       PROG                                       3      20000       1006             3   Call Center
       1008 Oracle     DBA                                        4      20000       1006             3   Call Center
       1001 Lawson     MGR                                        1      30000       1004             4       
       1002 Wells      DBA                                        2      20000       1005             4   Communication
       1003 Bliss      PROG                                       3      24000       1004             4   Communication
       1004 Kyte       MGR                                        4      25000       1005             4   Communication
       1005 Viper      PROG                                       1      20000       1006             4   Communication
       1006 Beck       PROG                                       2      20000                        4   Communication
       1007 Java       PROG                                       3      20000       1006             4   Communication
       1008 Oracle     DBA                                        4      20000       1006             4


32 rows selected.

SQL>
Home »
Oracle »
Select » 

Join:
  1. Table Join
  2. Using Table name to reference duplicate names
  3. Table Alias
  4. Cartesian Products
  5. Join more than two tables
  6. Join Conditions and Join Types
  7. Outer Joins
  8. Left and Right Outer Joins
  9. Outer join Error
  10. Self Join
  11. Outer Self Join
  12. Inner Joins Using SQL/92
  13. Joins with USING Keyword
  14. Inner Joins with More than Two Tables Using SQL/92
  15. Inner Joins on Multiple Columns Using SQL/92
  16. Outer Joins in SQL/92 Syntax
  17. Self Joins Using SQL/92
  18. Cross Joins Using SQL/92
Related: