Oracle PL/SQL Tutorial/Table Joins/Outer Joins Left Right
Содержание
- 1 An Example of a Left Outer Join 1
- 2 An Example of a Left Outer Join 2
- 3 An Example of a Right Outer Join 1
- 4 An Example of a Right Outer Join 2
- 5 Example outer join with (+)
- 6 Left and Right Outer Joins
- 7 Left Outer Join
- 8 LEFT OUTER JOIN tableName ON joined columns
- 9 LEFT OUTER JOIN vs RIGHT OUTER JOIN
- 10 Perform outer joins in combination with self joins, employee and job tables
- 11 Right join with where in clause
- 12 Right Outer Join
- 13 Right Outer Join(room vs class)
- 14 Right outer join with group by
- 15 Right outer join with using statement
- 16 Understanding Outer Joins
An Example of a Left Outer Join 1
<source lang="sql">
SQL> SQL> 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 ) 9 /
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> insert into job (EMPNO, Jobtitle) values (9,"Developer"); 1 row created. SQL> SQL> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, "Jason", to_date("19960725","YYYYMMDD"), 1234, 8767, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, "John", to_date("19970715","YYYYMMDD"), 2341, 3456, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, "Joe", to_date("19860125","YYYYMMDD"), 4321, 5654, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, "Tom", to_date("20060913","YYYYMMDD"), 2413, 6787, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, "Jane", to_date("20050417","YYYYMMDD"), 7654, 4345, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, "James", to_date("20040718","YYYYMMDD"), 5679, 6546, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, "Jodd", to_date("20030720","YYYYMMDD"), 5438, 7658, "E")
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> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
--------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E
9 rows selected. SQL> select * from job
2 /
EMPNO JOBTITLE
--------------------
1 Tester
2 Accountant
3 Developer
9 Developer
SQL> SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno = j.empno (+); ENAME JOBTITLE
--------------------
Jason Tester John Accountant Joe Developer Jack Developer Jane Joke James Jodd Tom 9 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> drop table job
2 /
Table dropped.</source>
An Example of a Left Outer Join 2
<source lang="sql">
SQL> SQL> SQL> 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 ) 9 /
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> insert into job (EMPNO, Jobtitle) values (4,"COder"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (5,"Director"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor"); 1 row created. 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> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, "Jason", to_date("19960725","YYYYMMDD"), 1234, 8767, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, "John", to_date("19970715","YYYYMMDD"), 2341, 3456, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, "Joe", to_date("19860125","YYYYMMDD"), 4321, 5654, "E")
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> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
--------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
9 Jack 29-AUG-01 7896 1232 E
SQL> select * from job
2 /
EMPNO JOBTITLE
--------------------
1 Tester
2 Accountant
3 Developer
4 COder
5 Director
6 Mediator
7 Proffessor
8 Programmer
9 Developer
9 rows selected. SQL> SQL> SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno = j.empno (+); ENAME JOBTITLE
--------------------
Jason Tester John Accountant Joe Developer Jack Developer SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> drop table job
2 /
Table dropped. SQL></source>
An Example of a Right Outer Join 1
<source lang="sql">
SQL> SQL> 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 ) 9 /
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> insert into job (EMPNO, Jobtitle) values (4,"COder"); 1 row created. SQL> SQL> insert into job (EMPNO, Jobtitle) values (9,"Developer"); 1 row created. SQL> SQL> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, "Jason", to_date("19960725","YYYYMMDD"), 1234, 8767, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, "John", to_date("19970715","YYYYMMDD"), 2341, 3456, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, "Joe", to_date("19860125","YYYYMMDD"), 4321, 5654, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, "Tom", to_date("20060913","YYYYMMDD"), 2413, 6787, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, "Jane", to_date("20050417","YYYYMMDD"), 7654, 4345, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, "James", to_date("20040718","YYYYMMDD"), 5679, 6546, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, "Jodd", to_date("20030720","YYYYMMDD"), 5438, 7658, "E")
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> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
--------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E
9 rows selected. SQL> select * from job
2 /
EMPNO JOBTITLE
--------------------
1 Tester
2 Accountant
3 Developer
4 COder
9 Developer
SQL> SQL> SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno (+) = j.empno ; ENAME JOBTITLE
--------------------
Jason Tester John Accountant Joe Developer Tom COder Jack Developer SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> drop table job
2 /
Table dropped.</source>
An Example of a Right Outer Join 2
<source lang="sql">
SQL> SQL> SQL> 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 ) 9 /
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> insert into job (EMPNO, Jobtitle) values (4,"COder"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (5,"Director"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor"); 1 row created. 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> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, "Jason", to_date("19960725","YYYYMMDD"), 1234, 8767, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, "John", to_date("19970715","YYYYMMDD"), 2341, 3456, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, "Joe", to_date("19860125","YYYYMMDD"), 4321, 5654, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, "Tom", to_date("20060913","YYYYMMDD"), 2413, 6787, "W")
3 /
1 row created. SQL> 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> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
--------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
9 Jack 29-AUG-01 7896 1232 E
SQL> select * from job
2 /
EMPNO JOBTITLE
--------------------
1 Tester
2 Accountant
3 Developer
4 COder
5 Director
6 Mediator
7 Proffessor
8 Programmer
9 Developer
9 rows selected. SQL> SQL> SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno (+) = j.empno ; ENAME JOBTITLE
--------------------
Jason Tester John Accountant Joe Developer Tom COder Jack Developer
Director
Programmer
Mediator
Proffessor
9 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> drop table job
2 /
Table dropped.</source>
Example outer join with (+)
<source lang="sql">
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> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected. SQL> select * from dept;
DEPTNO DNAME LOC
-------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SQL> SELECT a.dname, b.empno, b.ename
2 FROM dept a, emp b
3 WHERE a.deptno=b.deptno(+)
4 ORDER BY deptno;
ORDER BY deptno
*
ERROR at line 4: ORA-00918: column ambiguously defined
SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.</source>
Left and Right Outer Joins
Outer joins can be split into two types:
- Left outer joins
- Right outer joins
To understand the difference between left and right outer joins, consider the following syntax:
<source lang="sql">
SELECT ... FROM table1, table2 ...</source>
Assume the tables are to be joined on table1.column1 and table2.column2.
Assume table1 contains a row with a null value in column1.
To perform a left outer join, the WHERE clause is
WHERE table1.column1 = table2.column2 (+);
In a left outer join, the outer join operator is actually on the right of the equality operator.
Next, assume table2 contains a row with a null value in column2.
To perform a right outer join, you switch the position of the outer join operator to the left of the equality operator and the WHERE clause becomes WHERE table1.column1 (+) = table2.column2;
Depending on whether table1 and table2 both contain rows with null values, you get different results depending on whether you use a left or right outer join.
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
Left Outer Join
<source lang="sql">
SQL> SQL> CREATE TABLE Room (
2 RoomID INT NOT NULL PRIMARY KEY, 3 Comments VARCHAR(50), 4 Capacity INT);
Table created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,"Main hall",500); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,"Science Department",200); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,"Science Room 1",100); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,"Languages Block",300); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (5,"Languages Room 1",75); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE Class (
2 ClassID INT NOT NULL PRIMARY KEY, 3 CourseID INT NOT NULL, 4 InstructorID INT NOT NULL, 5 RoomID INT NOT NULL, 6 Time VARCHAR(50));
Table created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,"Mon 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (2,2,1,5,"Mon 11:00-12:00, Thu 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (3,3,2,3,"Mon 14:00-16:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (4,4,3,2,"Tue 10:00-12:00, Thu 14:00-15:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (5,5,2,9,"Tue 14:00-16:00"); 1 row created. SQL> SQL> SQL> SQL> SELECT Room.RoomID, Class.Time
2 FROM Room 3 LEFT OUTER JOIN Class 4 ON Room.RoomID = Class.RoomID 5 ORDER BY Room.RoomID; ROOMID TIME
--------------------------------------------------
1
2 Tue 10:00-12:00, Thu 14:00-15:00
3 Mon 14:00-16:00
4
5 Mon 11:00-12:00, Thu 09:00-11:00
5 rows selected. SQL> SQL> SQL> SQL> drop table Room; Table dropped. SQL> drop table Class; Table dropped.</source>
LEFT OUTER JOIN tableName ON joined columns
<source lang="sql">
SQL> SQL> CREATE TABLE emps (
2 emp varchar(30) 3 ,title varchar(30) 4 );
Table created. SQL> SQL> INSERT INTO emps VALUES ("Tom","Programmer"); 1 row created. SQL> INSERT INTO emps VALUES ("Jack","Tester"); 1 row created. SQL> INSERT INTO emps VALUES ("Mary","Technician"); 1 row created. SQL> SQL> CREATE TABLE JobLevel (
2 title varchar(30) 3 ,rank varchar(30) 4 );
Table created. SQL> SQL> INSERT INTO JobLevel VALUES ("Programmer","Level1"); 1 row created. SQL> INSERT INTO JobLevel VALUES ("Tester","Level2"); 1 row created. SQL> INSERT INTO JobLevel VALUES ("Technician","Level3"); 1 row created. SQL> SQL> CREATE TABLE salary (
2 rank varchar(30) 3 ,payment DECIMAL(10,2) 4 );
Table created. SQL> SQL> INSERT INTO salary VALUES ("Level1",2000.00); 1 row created. SQL> INSERT INTO salary VALUES ("Level2",3000.00); 1 row created. SQL> INSERT INTO salary VALUES ("Level3",5000.00); 1 row created. SQL> INSERT INTO salary VALUES ("Level4",6000.00); 1 row created. SQL> SQL> select * from emps;
EMP
TITLE
Tom Programmer Jack Tester Mary Technician
3 rows selected. SQL> select * from JobLevel;
TITLE
RANK
Programmer Level1 Tester Level2 Technician Level3 3 rows selected. SQL> select * from salary;
RANK PAYMENT
----------
Level1 2000 Level2 3000 Level3 5000 Level4 6000 4 rows selected. SQL> SQL> SQL> SELECT salary.rank
2 FROM salary LEFT OUTER JOIN JobLevel ON (salary.rank = JobLevel.rank) 3 WHERE JobLevel.rank IS NULL;
RANK
Level4
1 row selected. SQL> SQL> SQL> DROP TABLE emps; Table dropped. SQL> DROP TABLE JobLevel; Table dropped. SQL> DROP TABLE salary; Table dropped. SQL> SQL></source>
LEFT OUTER JOIN vs RIGHT OUTER JOIN
<source lang="sql">
SQL> SQL> CREATE TABLE project (
2 pro_id NUMBER(4), 3 pro_name VARCHAR2(40), 4 budget NUMBER(9,2), 5 CONSTRAINT project_pk PRIMARY KEY (pro_id) 6 );
Table created. SQL> SQL> SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345); 1 row created. SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456); 1 row created. SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567); 1 row created. SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678); 1 row created. SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE server_usage (
2 pro_id NUMBER(4), 3 emp_id NUMBER, 4 time_log_date DATE, 5 hours_logged NUMBER(8,2), 6 dollars_charged NUMBER(8,2), 7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date) 8 );
Table created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SELECT p.pro_name, ph.time_log_date, ph.hours_logged
2 FROM project p LEFT OUTER JOIN server_usage ph 3 ON p.pro_id = ph.pro_id;
A 04-APR-04 1123 ERP 04-APR-05 1124 SQL 04-APR-06 1125 CRM 04-APR-07 1126 VPN 04-APR-08 1127 A 04-APR-09 1128 ERP 04-APR-10 1129
7 rows selected. SQL> SQL> SELECT p.pro_name, ph.time_log_date, ph.hours_logged
2 FROM server_usage ph RIGHT OUTER JOIN project p 3 ON p.pro_id = ph.pro_id;
A 04-APR-04 1123 ERP 04-APR-05 1124 SQL 04-APR-06 1125 CRM 04-APR-07 1126 VPN 04-APR-08 1127 A 04-APR-09 1128 ERP 04-APR-10 1129
7 rows selected. SQL> SQL> drop table server_usage; Table dropped. SQL> drop table project; Table dropped.</source>
Perform outer joins in combination with self joins, employee and job tables
<source lang="sql">
SQL> SQL> 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> insert into job (EMPNO, Jobtitle) values (4,"COder"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (5,"Director"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor"); 1 row created. 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> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (1, "Jason", to_date("19960725","YYYYMMDD"), 1234, 8767, "E", 2)
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (2, "John", to_date("19970715","YYYYMMDD"), 2341, 3456, "W", 3)
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (3, "Joe", to_date("19860125","YYYYMMDD"), 4321, 5654, "E", 3)
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (4, "Tom", to_date("20060913","YYYYMMDD"), 2413, 6787, "W", 4)
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (5, "Jane", to_date("20050417","YYYYMMDD"), 7654, 4345, "E", 4)
3 /
1 row created. 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> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R MANAGER_ID
--------------- --------- ----------- ----------- - ----------
1 Jason 25-JUL-96 1234 8767 E 2
2 John 15-JUL-97 2341 3456 W 3
3 Joe 25-JAN-86 4321 5654 E 3
4 Tom 13-SEP-06 2413 6787 W 4
5 Jane 17-APR-05 7654 4345 E 4
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
9 rows selected. SQL> select * from job
2 /
EMPNO JOBTITLE
--------------------
1 Tester
2 Accountant
3 Developer
4 COder
5 Director
6 Mediator
7 Proffessor
8 Programmer
9 Developer
9 rows selected. SQL> SQL> SQL> SELECT w.ename || " works for " ||
2 NVL(m.ename, "the shareholders") 3 FROM employee w, employee m 4 WHERE w.manager_id = m.empno;
W.ENAME||"WORKSFOR"||NVL(M.ENAME,"THESHARE
Jason works for John Joe works for Joe John works for Joe Jane works for Tom Tom works for Tom James works for Jane Jodd works for James 7 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> drop table job
2 /
Table dropped. SQL></source>
Right join with where in clause
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE emp
2 ( 3 cid NUMBER, 4 lname VARCHAR2(40) , 5 city VARCHAR2(30) , 6 customer_state VARCHAR2(40), 7 country_id CHAR(2) , 8 customer_credit NUMBER 9 );
Table created. SQL> SQL> CREATE TABLE sales(
2 product_id NUMBER(6), 3 cid NUMBER, 4 time_id DATE, 5 channel_id CHAR(1), 6 promo_id NUMBER(6), 7 sold NUMBER(3), 8 amount NUMBER(10,2), 9 cost NUMBER(10,2) 10 );
Table created. SQL> SQL> SQL> SQL> select c.cid, c.lname, s.product_id, s.sold
2 from sales s right join emp c 3 on c.cid = s.cid 4 where c.cid in (1,80);
no rows selected SQL> SQL> drop table sales; Table dropped. SQL> drop table emp; Table dropped. SQL> SQL></source>
Right Outer Join
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE Room (
2 RoomID INT NOT NULL PRIMARY KEY, 3 Comments VARCHAR(50), 4 Capacity INT);
Table created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,"Main hall",500); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,"Science Department",200); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,"Science Room 1",100); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,"Languages Block",300); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE Class (
2 ClassID INT NOT NULL PRIMARY KEY, 3 CourseID INT NOT NULL, 4 InstructorID INT NOT NULL, 5 RoomID INT NOT NULL, 6 Time VARCHAR(50));
Table created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,"Mon 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (2,2,1,5,"Mon 11:00-12:00, Thu 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (3,3,2,3,"Mon 14:00-16:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (4,4,3,2,"Tue 10:00-12:00, Thu 14:00-15:00"); 1 row created. SQL> SQL> SQL> SQL> SELECT Room.RoomID, Class.Time
2 FROM Room 3 RIGHT OUTER JOIN Class 4 ON Room.RoomID = Class.RoomID 5 ORDER BY Room.RoomID; ROOMID TIME
--------------------------------------------------
2 Tue 10:00-12:00, Thu 14:00-15:00
3 Mon 14:00-16:00
Mon 09:00-11:00
Mon 11:00-12:00, Thu 09:00-11:00
4 rows selected. SQL> SQL> drop table Room; Table dropped. SQL> drop table Class; Table dropped.</source>
Right Outer Join(room vs class)
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE Room (
2 RoomID INT NOT NULL PRIMARY KEY, 3 Comments VARCHAR(50), 4 Capacity INT);
Table created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,"Main hall",500); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,"Science Department",200); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,"Science Room 1",100); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,"Languages Block",300); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (5,"Languages Room 1",75); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE Class (
2 ClassID INT NOT NULL PRIMARY KEY, 3 CourseID INT NOT NULL, 4 InstructorID INT NOT NULL, 5 RoomID INT NOT NULL, 6 Time VARCHAR(50));
Table created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,"Mon 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (2,2,1,5,"Mon 11:00-12:00, Thu 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (3,3,2,3,"Mon 14:00-16:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (4,4,3,2,"Tue 10:00-12:00, Thu 14:00-15:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (5,5,2,9,"Tue 14:00-16:00"); 1 row created. SQL> SQL> SQL> SQL> SELECT Room.RoomID, Class.Time
2 FROM Class 3 RIGHT OUTER JOIN Room 4 ON Class.RoomID = Room.RoomID 5 ORDER BY Room.RoomID; ROOMID TIME
--------------------------------------------------
1
2 Tue 10:00-12:00, Thu 14:00-15:00
3 Mon 14:00-16:00
4
5 Mon 11:00-12:00, Thu 09:00-11:00
5 rows selected. SQL> SQL> drop table Room; Table dropped. SQL> drop table Class; Table dropped.</source>
Right outer join with group by
<source lang="sql">
SQL> SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> create table departments
2 ( deptno NUMBER(2) 3 , dname VARCHAR2(10) 4 , location VARCHAR2(20) 5 , mgr NUMBER(4) 6 ) ;
Table created. SQL> SQL> insert into departments values (10,"ACCOUNTING","NEW YORK" , 2); 1 row created. SQL> insert into departments values (20,"TRAINING", "VANCOUVER", 3); 1 row created. SQL> insert into departments values (30,"SALES", "CHICAGO", 4); 1 row created. SQL> insert into departments values (40,"HR", "BOSTON", 5); 1 row created. SQL> SQL> SQL> select deptno, count(*)
2 from employees e 3 right outer join 4 departments d 5 using (deptno) 6 group by deptno; DEPTNO COUNT(*)
----------
30 3
20 3
40 2
10 2
SQL> SQL> drop table employees; Table dropped. SQL> SQL> drop table departments; Table dropped. SQL> SQL></source>
Right outer join with using statement
<source lang="sql">
SQL> SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> create table departments
2 ( deptno NUMBER(2) 3 , dname VARCHAR2(10) 4 , location VARCHAR2(20) 5 , mgr NUMBER(4) 6 ) ;
Table created. SQL> SQL> insert into departments values (10,"ACCOUNTING","NEW YORK" , 2); 1 row created. SQL> insert into departments values (20,"TRAINING", "VANCOUVER", 3); 1 row created. SQL> insert into departments values (30,"SALES", "CHICAGO", 4); 1 row created. SQL> insert into departments values (40,"HR", "BOSTON", 5); 1 row created. SQL> SQL> SQL> select deptno, d.location
2 , e.ename, e.init 3 from employees e 4 right outer join 5 departments d 6 using (deptno) 7 order by deptno, e.ename; DEPTNO LOCATION ENAME INIT
-------------------- -------- -----
10 NEW YORK Jason N
10 NEW YORK Jerry J
20 VANCOUVER Jord T
20 VANCOUVER Mary J
20 VANCOUVER Yellow C
30 CHICAGO Black R
30 CHICAGO Joe P
30 CHICAGO Pink J
40 BOSTON Red A
40 BOSTON White S
10 rows selected. SQL> SQL> drop table employees; Table dropped. SQL> SQL> drop table departments; Table dropped. SQL></source>
Understanding Outer Joins
- An outer join retrieves a row even when one of the columns in the join contains a null value.
- You perform an outer join by supplying the outer join operator in the join condition.
- The outer join operator is a plus character in parentheses (+).
- The outer join operator (+) is on the column that contains the null value.
<source lang="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 ) 9 /
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> insert into job (EMPNO, Jobtitle) values (4,"COder"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (5,"Director"); 1 row created. SQL> SQL> insert into job (EMPNO, Jobtitle) values (9,"Developer"); 1 row created. SQL> SQL> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (1, "Jason", to_date("19960725","YYYYMMDD"), 1234, 8767, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (2, "John", to_date("19970715","YYYYMMDD"), 2341, 3456, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (3, "Joe", to_date("19860125","YYYYMMDD"), 4321, 5654, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (4, "Tom", to_date("20060913","YYYYMMDD"), 2413, 6787, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (5, "Jane", to_date("20050417","YYYYMMDD"), 7654, 4345, "E")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (6, "James", to_date("20040718","YYYYMMDD"), 5679, 6546, "W")
3 /
1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (7, "Jodd", to_date("20030720","YYYYMMDD"), 5438, 7658, "E")
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> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
--------------- --------- ----------- ----------- -
1 Jason 25-JUL-96 1234 8767 E
2 John 15-JUL-97 2341 3456 W
3 Joe 25-JAN-86 4321 5654 E
4 Tom 13-SEP-06 2413 6787 W
5 Jane 17-APR-05 7654 4345 E
6 James 18-JUL-04 5679 6546 W
7 Jodd 20-JUL-03 5438 7658 E
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E
9 rows selected. SQL> select * from job
2 /
EMPNO JOBTITLE
--------------------
1 Tester
2 Accountant
3 Developer
4 COder
5 Director
9 Developer
6 rows selected. SQL> SQL> SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno = j.empno (+); ENAME JOBTITLE
--------------------
Jason Tester John Accountant Joe Developer Tom COder Jane Director Jack Developer Joke James Jodd 9 rows selected. SQL> SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno (+) = j.empno; ENAME JOBTITLE
--------------------
Jason Tester John Accountant Joe Developer Tom COder Jane Director Jack Developer 6 rows selected. SQL> SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE j.empno (+) = e.empno; ENAME JOBTITLE
--------------------
Jason Tester John Accountant Joe Developer Tom COder Jane Director Jack Developer Joke James Jodd 9 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> drop table job
2 /
Table dropped. SQL></source>
