Mega Code Archive

Performing Self Joins Using SQL92

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>