Mega Code Archive
Performing Left Outer Joins Using SQL92
<![CDATA[
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION VARCHAR2(1 BYTE),
8 MANAGER_ID NUMBER(3)
9 )
10 /
Table created.
SQL>
SQL> create table job (
2 EMPNO NUMBER(3),
3 jobtitle VARCHAR2(20 BYTE)
4 )
5 /
Table created.
SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');
1 row created.
SQL>
SQL> insert into job (EMPNO, Jobtitle) values (8,'Programmer');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');
1 row created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (6, 'James', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W', 5)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (7, 'Jodd', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E', 6)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (8, 'Joke', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W')
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (9, 'Jack', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E')
3 /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R MANAGER_ID
---------- --------------- --------- ----------- ----------- - ----------
6 James 18-JUL-04 5679 6546 W 5
7 Jodd 20-JUL-03 5438 7658 E 6
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E
SQL> select * from job
2 /
EMPNO JOBTITLE
---------- --------------------
1 Tester
2 Accountant
3 Developer
8 Programmer
9 Developer
SQL>
SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno = j.empno (+);
ENAME JOBTITLE
--------------- --------------------
Joke Programmer
Jack Developer
James
Jodd
SQL>
SQL> --rewrites this query using the SQL/92 LEFT OUTER JOIN keywords:
SQL> SELECT e.ename, j.jobtitle FROM employee e LEFT OUTER JOIN job j USING (empno);
ENAME JOBTITLE
--------------- --------------------
Joke Programmer
Jack Developer
James
Jodd
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL> drop table job
2 /
Table dropped.
SQL>
]]>