Oracle PL/SQL/Table Joins/ANSI SQL 92 Joins
Содержание
Performing Cross Joins Using SQL/92
<source lang="sql">
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>
</source>
Performing Full Outer Joins Using SQL/92
<source lang="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> -- Performing Full Outer Joins Using SQL/92 SQL> SQL> --A full outer join uses all rows in the joined tables including those that have null values in SQL> --either of the columns used in the join. The following example shows a query that uses the SQL/92 SQL> -- FULL OUTER JOIN keywords: SQL> SQL> SELECT e.ename, j.jobtitle
2 FROM employee e FULL OUTER JOIN job j 3 USING (empno);
ENAME JOBTITLE
----------
Alison Tester James Dediator Celia Chemist Robert Accountant Linda Manager David Programmer Jode Developer
Painter
8 rows selected. SQL> SQL> SQL> drop table job; Table dropped. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Performing Left Outer Joins Using SQL/92
<source lang="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 Left Outer Joins Using SQL/92 SQL> SQL> -- Earlier you saw the following query that performed a left outer join: SQL> SQL> SELECT e.ename, j.jobtitle
2 FROM employee e, job j 3 WHERE e.empno = j.empno (+);
ENAME JOBTITLE
----------
Alison Tester James Dediator Celia Chemist Robert Accountant Linda Manager David Programmer Jode Developer 7 rows selected. SQL> SQL> -- The next example rewrites this query using the SQL/92 LEFT OUTER JOIN keywords: SQL> SQL> SELECT e.ename, j.jobtitle
2 FROM employee e LEFT OUTER JOIN job j 3 USING (empno);
ENAME JOBTITLE
----------
Alison Tester James Dediator Celia Chemist Robert Accountant Linda Manager David Programmer Jode Developer 7 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> drop table job; Table dropped. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Performing Right Outer Joins Using SQL/92
<source lang="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 Right Outer Joins Using SQL/92 SQL> SQL> -- Earlier you saw the following query that performed a right outer join: SQL> SQL> SELECT e.ename, j.jobtitle
2 FROM employee e, job j 3 WHERE e.empno (+) = j.empno;
ENAME JOBTITLE
----------
Alison Tester James Dediator Celia Chemist Robert Accountant Linda Manager David Programmer Jode Developer
Painter
8 rows selected. SQL> SQL> -- The next example rewrites this query using the SQL/92 RIGHT OUTER JOIN keywords: SQL> SQL> SELECT e.ename, j.jobtitle
2 FROM employee e RIGHT OUTER JOIN job j 3 USING (empno);
ENAME JOBTITLE
----------
Alison Tester James Dediator Celia Chemist Robert Accountant Linda Manager David Programmer Jode Developer
Painter
8 rows selected. SQL> SQL> SQL> drop table job; Table dropped. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Performing Self Joins Using SQL/92
<source lang="sql">
SQL> -- create demo table SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- ----------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-DEC-17 800 20 7499 ALLEN SALESMAN 7698 1981-FEB-20 1600 300 30 7521 WARD SALESMAN 7698 1981-FEB-22 1250 500 30 7566 JONES MANAGER 7839 1981-APR-02 2975 20 7654 MARTIN SALESMAN 7698 1981-SEP-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-MAY-01 2850 30 7782 CLARK MANAGER 7839 1981-JUN-09 2450 10 7788 SCOTT ANALYST 7566 1982-DEC-09 3000 20 7839 KING PRESIDENT 1981-NOV-17 5000 10 7844 TURNER SALESMAN 7698 1981-SEP-08 1500 0 30 7876 ADAMS CLERK 7788 1983-JAN-12 1100 20 7900 JAMES CLERK 7698 1981-DEC-03 950 30 7902 FORD ANALYST 7566 1981-DEC-03 3000 20 7934 MILLER CLERK 7782 1982-JAN-23 1300 10
14 rows selected. SQL> SQL> --Performing Self Joins Using SQL/92 SQL> SQL> --The following example uses SQL/86 to perform a self join on the employees table: SQL> SQL> SELECT e.ename || " works for " || m.ename
2 FROM emp e, emp m 3 WHERE e.mgr = m.empno;
E.ENAME||"WORKSFOR"||M.ENAME
FORD works for JONES SCOTT works for JONES JAMES works for BLAKE TURNER works for BLAKE MARTIN works for BLAKE WARD works for BLAKE ALLEN works for BLAKE MILLER works for CLARK ADAMS works for SCOTT CLARK works for KING BLAKE works for KING JONES works for KING SMITH works for FORD 13 rows selected. SQL> SQL> -- The next example rewrites this query to use the SQL/92 INNER JOIN and ON keywords: SQL> SQL> SELECT w.ename || " works for " || m.ename
2 FROM emp w INNER JOIN emp m 3 ON w.mgr = m.empno;
W.ENAME||"WORKSFOR"||M.ENAME
FORD works for JONES SCOTT works for JONES JAMES works for BLAKE TURNER works for BLAKE MARTIN works for BLAKE WARD works for BLAKE ALLEN works for BLAKE MILLER works for CLARK ADAMS works for SCOTT CLARK works for KING BLAKE works for KING JONES works for KING SMITH works for FORD 13 rows selected. SQL> SQL> SQL> SQL> drop table Emp
2 /
Table dropped. SQL> SQL>
</source>