Back to: Oracle Tutorials for Beginners and Professionals
Left Outer Join in Oracle with Examples
In this article, I am going to discuss the Left Outer Join in Oracle with Examples. Please read our previous article where we discussed Inner Join in Oracle with Examples. At the end of this article, you will understand the following pointers in detail.
- What is Left Outer Join?
- How to implement Left Outer Join in Oracle?
- When do we need to use Left Outer JOIN?
- Examples to Understand Left Outer Join
- How to use Left Outer Join along with Where Condition?
- How to retrieve only the non-matching rows from the left table?
- Joining three Tables using Left Outer Join
What is Left Outer Join in Oracle?
The Left Outer Join in Oracle is used to retrieve all the matching records from both the tables as well as non-matching records from the left side table. In that case, the non-matching data will take a null value. The LEFT OUTER JOIN or LEFT JOIN keyword is used to perform the left join in Oracle.
The question that should come to your mind is which is the left table and which is the right table? The answer is, the table which is mentioned to the left of the LEFT OUTER JOIN keyword is the left table, and the table which is mentioned to the right of the LEFT OUTER JOIN keyword is the right table. The following diagram shows the pictorial representation of Left Outer Join.
How to implement Left Outer Join in Oracle?
The following images show how to use Left Outer Join in Oracle. As you can see in the below image, we can use either the LEFT OUTER JOIN or the LEFT JOIN keyword to perform the Left Join operation in Oracle. That means if you use the LEFT JOIN keyword, then also it is going to perform Left Outer JOIN Operation.
When do we need to use Left JOIN in Oracle?
If you want to retrieve all the matching rows from both the tables involved in the join as well as all the non-matching rows from the left side table in the result set then you need to use Left Join in Oracle. In that case, the non-matching rows will take a null value.
Understand LEFT OUTER JOIN:
Let us understand how to implement Left Outer Join in Oracle with Examples. To understand Left Outer Join, we are going to use the following Employee, Address, and Projects tables. Here, EmployeeId is the common column that is available in all the tables and based on this column we are going to perform the Left Outer Join Operations.
Please use the following SQL Script to create the Employee, Address, and Projects tables with the required data.
set linesize 500; DROP TABLE Employee; CREATE TABLE Employee ( EmployeeId INT PRIMARY KEY, FullName VARCHAR(20), Department VARCHAR(10), Salary INT, Gender VARCHAR(10), Age INT ); INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25); INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27); INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28); INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28); INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26); INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25); INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1007, 'Priyanka Dewangan', 'HR', 45000, 'Female', 27); INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28); INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28); INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26); DROP TABLE Projects; CREATE TABLE Projects ( ProjectId INT PRIMARY KEY, ProjectName VARCHAR(50), EmployeeId INT ); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (1, 'Develop Ecommerse Website from Scratch', 1003); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (2, 'WordPress Website for our company', 1002); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (3, 'Manage our Company Servers', 1007); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (4, 'Hosting account is not working', 1009); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (5, 'MySQL database from my desktop application', 1010); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (6, 'Develop WordPress plugin for my business website', NULL); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (7, 'Migrate web application and database to new server', NULL); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (8, 'Android Application development', 1004); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (9, 'Hosting account is not working', 1001); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (10, 'MySQL database from my desktop application', 1008); INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (11, 'Develop WordPress plugin for my business website', NULL); DROP TABLE Address; CREATE TABLE Address ( AddressId INT PRIMARY KEY, EmployeeId INT, Country VARCHAR(10), State VARCHAR(20), City VARCHAR(10) ); INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (1, 1001, 'India', 'Odisha', 'BBSR'); INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (2, 1002, 'India', 'Maharashtra', 'Mumbai'); INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (3, 1003, 'India', 'Maharashtra', 'Pune'); INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (4, 1004, 'India', 'Odisha', 'Cuttack'); INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (5, 1005, 'India', 'Maharashtra', 'Nagpur'); INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (6, 1006, 'India', 'Odisha', 'Cuttack');
Left Outer Join Example in Oracle:
Our requirement is to retrieve the EmployeeId, FullName, Department, Gender, ProjectName from the Employee and Projects tables. The query should return the following result set. As you can see in the below image, we want to retrieve all the employees from the Employee table irrespective of whether there is an entry that exists or not in the Projects table.
Following is the SQL Query that is Left Outer Joining the Employee and Projects tables based on the EmployeeID column and will give you the required results as shown in the above image.
SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName FROM Employee LEFT OUTER JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId;
If you look at the output, here, we got 10 rows i.e. all the records from the Employee table. Those employees who do not have any entry in the Projects table will have null values. So, this proves, the left outer join in Oracle will retrieve all the matching rows from both the table as well as non-matching rows from the left-hand side table. In our example, Employee is the left-hand side table.
In Oracle, instead of using the LEFT OUTER JOIN keyword, you can also use the LEFT JOIN keyword as shown in the below SQL Query and it will also give you the same result as the previous SQL Query.
SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName FROM Employee LEFT JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId;
Left Outer Join with Where Condition:
Let us understand how to use Left Outer join along with the WHERE filtering condition in Oracle. Our requirement is to retrieve all the EmployeeId, FullName, Department, Gender, ProjectName from the Employee and Projects tables by using the Left Outer Join where the gender is Male. The following is the SQL Script.
SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName FROM Employee LEFT OUTER JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId WHERE Employee.Gender = 'Male';
Once you execute the above SQL query, you will get the following result set which includes only the Male employees.
How to retrieve only the non-matching rows from the left table?
There are two employees for which there is no entry exists in the Projects table i.e. two employees are not currently assigned with any projects. So, what our requirement is, we want to retrieve only those two employees for which there are non-matching records exist in the Projects table. So, basically, we want to retrieve only the non-matching records from the left side table. The query should return the following result set.
The following Left Outer Join Query with the where clause does the same.
SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName FROM Employee LEFT JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId WHERE Projects.EmployeeId IS NULL;
Joining three Tables using Left Outer Join:
It is also possible in Oracle to join more than two tables. Let us understand how to JOIN three tables in Oracle using Left Outer Join. The following is the syntax in Oracle to join three tables using Left Outer Join.
Left Outer Joining Employee, Projects, and Address tables
Now, our requirement is to fetch the details of all employees along with the project name and address if they have. The following SQL Query Left Joining Employee, Projects, and Address tables to fetch the Employee details along with their projects and address.
SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName, Country, State, City FROM Employee LEFT OUTER JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId LEFT JOIN Address ON Employee.EmployeeID = Address.EmployeeId;
Once you execute the above SQL query, you will get the following result set. You can see, here, we get all the employee’s records irrespective of whether they are having Addresses or assigning with any projects or not.
In the next article, I am going to discuss Right Outer Join in Oracle with examples. Here, in this article, I try to explain the Left Outer Join in Oracle with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.