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 »
Oracle »
Select »
Join:
- Table Join
- Using Table name to reference duplicate names
- Table Alias
- Cartesian Products
- Join more than two tables
- Join Conditions and Join Types
- Outer Joins
- Left and Right Outer Joins
- Outer join Error
- Self Join
- Outer Self Join
- Inner Joins Using SQL/92
- Joins with USING Keyword
- Inner Joins with More than Two Tables Using SQL/92
- Inner Joins on Multiple Columns Using SQL/92
- Outer Joins in SQL/92 Syntax
- Self Joins Using SQL/92
- Cross Joins Using SQL/92
Related: