Oracle LEFT OUTER JOIN

What is left outer join in Oracle?

A LEFT OUTER JOIN performs an inner join of two tables (supposed table A which writes before the join keyword and table B which writes after the join keyword in the SQL statement ) based on the condition specified after the ON keyword. It returns all rows from the table A as well as the unmatched rows from the table B. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.

Syntax:

SELECT table1.column, table2.column
FROM table1
LEFT OUTER JOIN table2
ON (table1.column = table2.column);

Pictorial presentation of Oracle Left Outer Join

Example: Oracle Left Outer Join

The following query retrieves all the matching rows in the employees table, and departments table for the criteria same department_id in both tables and department name starts with the letter 'P', and also those rows from departments table even if there is no match in the employees table.

Sample table: employees

Sample table: departments

SELECT e.first_name, e.department_id, d.department_name 
FROM employees e LEFT OUTER JOIN departments d 
ON (e.department_id = d.department_id) 
WHERE d.department_name LIKE 'P%';

Sample Output:

FIRST_NAME           DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- ----------------------
Den                             30 Purchasing
Alexander                       30 Purchasing
Shelli                          30 Purchasing
Sigal                           30 Purchasing
Guy                             30 Purchasing
Karen                           30 Purchasing
Hermann                         70 Public Relations

7 rows selected.

Example: LEFT OUTER JOIN using three tables

The following query retrieves all the matching rows in the employees table, and departments table for the criteria same department_id in both tables. Therefore this result will join with the table locations for the criteria same location id with location table, and finally the result will be filtered out by the criteria department name starts with the letter 'P', and also those rows from departments table and locations table even if there is no match in the employees table.

Sample table: employees

Sample table: departments

Sample table: locations

SELECT e.first_name, e.department_id, 
d.department_name, l.location_id,l.city  
FROM employees e 
LEFT OUTER JOIN departments d  
ON (e.department_id = d.department_id) 
LEFT OUTER JOIN locations l 
ON (d.location_id=l.location_id) 
WHERE d.department_name LIKE 'P%';

Sample Output:

FIRST_NAME           DEPARTMENT_ID DEPARTMENT_NAME                LOCATION_ID CITY
-------------------- ------------- ------------------------------ ----------- --------
Den                             30 Purchasing                            1700 Seattle
Alexander                       30 Purchasing                            1700 Seattle
Shelli                          30 Purchasing                            1700 Seattle
Sigal                           30 Purchasing                            1700 Seattle
Guy                             30 Purchasing                            1700 Seattle
Karen                           30 Purchasing                            1700 Seattle
Hermann                         70 Public Relations                      2700 Munich

7 rows selected.

LEFT JOIN: SQL and other Relational Databases

• SQL LEFT JOIN
• MySQL LEFT JOIN
• PostgreSQL LEFT JOIN
• SQLite LEFT JOIN

Previous: INNER JOINS
Next: RIGHT OUTER JOIN