Left Outer Join

Left Outer Join Tutorial

This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).

This link explains outer join better.


We have 2 tables Employee and Address.

Employee
1. EmployeeID
2. EmployeeName
3. Division
4. Salary

Address
1. EmployeeID
2. Phone
3. Address

create table Employee values(EmployeeID number(5), EmployeeName varchar2(20), Division varchar2(20), Salary number(5));

create table Address values(EmployeeID number(5), Phone number(5), Address varchar2(30));

insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (1, 'abc', 'computer', 10000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (2, 'def', 'sales', 20000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (3, 'ghi', 'front desk', 30000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (4, 'jkl', 'production', 40000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (5, 'mno', 'marketing', 50000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (6, 'pqr', 'research', 60000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (7, 'stu', 'marketing', 70000);
insert into Employee(EmployeeID, EmployeeName, Division, Salary) values (8, 'vwx', 'hr', 80000);

insert into Address(EmployeeID, Address) values (1, 4444, '1 street, Bangalore');
insert into Address(EmployeeID, Address) values (3, 6666, '3 street, Bangalore');
insert into Address(EmployeeID, Address) values (5, 3333, '5 street, Bangalore');
insert into Address(EmployeeID, Address) values (7, 7777, '7 street, Bangalore');

select emp.EmployeeID, emp.Division, add.Phone from Employee emp, Address add where emp.EmployeeID = add.EmployeeID(+)

select emp.EmployeeID, emp.Division, add.Phone from Employee emp left outer join Address add on emp.EmployeeID = add.EmployeeID

SQL> select emp.EmployeeID, emp.Division, aa.Phone from Employee emp, Address aa where emp.EmployeeI D = aa.EmployeeID(+);

EMPLOYEEID DIVISION PHONE
---------- -------------------- ----------
1 computer 4444
3 front desk 6666
5 marketing 3333
7 marketing 7777
8 hr
2 sales
6 research
4 production

8 rows selected.

SQL> select emp.EmployeeID, emp.Division, aa.Phone from Employee emp left outer join Address aa on e mp.EmployeeID = aa.EmployeeID 2 ;

EMPLOYEEID DIVISION PHONE
---------- -------------------- ----------
1 computer 4444
3 front desk 6666
5 marketing 3333
7 marketing 7777
8 hr
2 sales
6 research
4 production

8 rows selected.