Performing Cross Joins Using SQL/92 : ANSI SQL 92 Joins « Table Joins « Oracle PL / SQL






Performing Cross Joins Using SQL/92



SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /

Table created.

SQL>
SQL> create table job(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
  4  )
  5  /

Table created.

SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000,       48000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, 'James',to_date('19781212','YYYYMMDD'), 23000,       32000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,'Celia',to_date('19821024','YYYYMMDD'), 53000,       58000,        'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000,      36000,        'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,'Linda', to_date('19870730','YYYYMMDD'), 43000,       53000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,'David', to_date('19901231','YYYYMMDD'), 78000,       85000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,'Jode',  to_date('19960917','YYYYMMDD'), 21000,       29000,       'E')
  3  /

1 row created.

SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(101,   'Painter');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(122,   'Tester');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(123,   'Dediator');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(104,   'Chemist');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(105,   'Accountant');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(116,   'Manager');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(117,   'Programmer');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(108,   'Developer');

1 row created.

SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E

7 rows selected.

SQL>
SQL> select * from job;

     EMPNO JOBTITLE
---------- ----------
       101 Painter
       122 Tester
       123 Dediator
       104 Chemist
       105 Accountant
       116 Manager
       117 Programmer
       108 Developer

8 rows selected.

SQL>
SQL>
SQL>
SQL> --Performing Cross Joins Using SQL/92
SQL>
SQL> SELECT *
  2  FROM employee CROSS JOIN job;

     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R      EMPNO JOBTITLE
---------- ---------- --------- ----------- ----------- - ---------- ----------
       122 Alison     21-MAR-96       45000       48000 E        101 Painter
       122 Alison     21-MAR-96       45000       48000 E        122 Tester
       122 Alison     21-MAR-96       45000       48000 E        123 Dediator
       122 Alison     21-MAR-96       45000       48000 E        104 Chemist
       122 Alison     21-MAR-96       45000       48000 E        105 Accountant
       122 Alison     21-MAR-96       45000       48000 E        116 Manager
       122 Alison     21-MAR-96       45000       48000 E        117 Programmer
       122 Alison     21-MAR-96       45000       48000 E        108 Developer
       123 James      12-DEC-78       23000       32000 W        101 Painter
       123 James      12-DEC-78       23000       32000 W        122 Tester
       123 James      12-DEC-78       23000       32000 W        123 Dediator
       123 James      12-DEC-78       23000       32000 W        104 Chemist
       123 James      12-DEC-78       23000       32000 W        105 Accountant
       123 James      12-DEC-78       23000       32000 W        116 Manager
       123 James      12-DEC-78       23000       32000 W        117 Programmer
       123 James      12-DEC-78       23000       32000 W        108 Developer
       104 Celia      24-OCT-82       53000       58000 E        101 Painter
       104 Celia      24-OCT-82       53000       58000 E        122 Tester
       104 Celia      24-OCT-82       53000       58000 E        123 Dediator
       104 Celia      24-OCT-82       53000       58000 E        104 Chemist
       104 Celia      24-OCT-82       53000       58000 E        105 Accountant
       104 Celia      24-OCT-82       53000       58000 E        116 Manager
       104 Celia      24-OCT-82       53000       58000 E        117 Programmer
       104 Celia      24-OCT-82       53000       58000 E        108 Developer
       105 Robert     15-JAN-84       31000       36000 W        101 Painter
       105 Robert     15-JAN-84       31000       36000 W        122 Tester
       105 Robert     15-JAN-84       31000       36000 W        123 Dediator
       105 Robert     15-JAN-84       31000       36000 W        104 Chemist
       105 Robert     15-JAN-84       31000       36000 W        105 Accountant
       105 Robert     15-JAN-84       31000       36000 W        116 Manager
       105 Robert     15-JAN-84       31000       36000 W        117 Programmer
       105 Robert     15-JAN-84       31000       36000 W        108 Developer
       116 Linda      30-JUL-87       43000       53000 E        101 Painter
       116 Linda      30-JUL-87       43000       53000 E        122 Tester
       116 Linda      30-JUL-87       43000       53000 E        123 Dediator
       116 Linda      30-JUL-87       43000       53000 E        104 Chemist
       116 Linda      30-JUL-87       43000       53000 E        105 Accountant
       116 Linda      30-JUL-87       43000       53000 E        116 Manager
       116 Linda      30-JUL-87       43000       53000 E        117 Programmer
       116 Linda      30-JUL-87       43000       53000 E        108 Developer
       117 David      31-DEC-90       78000       85000 W        101 Painter
       117 David      31-DEC-90       78000       85000 W        122 Tester
       117 David      31-DEC-90       78000       85000 W        123 Dediator
       117 David      31-DEC-90       78000       85000 W        104 Chemist
       117 David      31-DEC-90       78000       85000 W        105 Accountant
       117 David      31-DEC-90       78000       85000 W        116 Manager
       117 David      31-DEC-90       78000       85000 W        117 Programmer
       117 David      31-DEC-90       78000       85000 W        108 Developer
       108 Jode       17-SEP-96       21000       29000 E        101 Painter
       108 Jode       17-SEP-96       21000       29000 E        122 Tester
       108 Jode       17-SEP-96       21000       29000 E        123 Dediator
       108 Jode       17-SEP-96       21000       29000 E        104 Chemist
       108 Jode       17-SEP-96       21000       29000 E        105 Accountant
       108 Jode       17-SEP-96       21000       29000 E        116 Manager
       108 Jode       17-SEP-96       21000       29000 E        117 Programmer
       108 Jode       17-SEP-96       21000       29000 E        108 Developer

56 rows selected.

SQL>
SQL>
SQL> drop table job;

Table dropped.

SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
SQL>
           
       








Related examples in the same category

1.Performing Left Outer Joins Using SQL/92
2.Performing Right Outer Joins Using SQL/92
3.Performing Full Outer Joins Using SQL/92
4.Performing Self Joins Using SQL/92