Left Join in Oracle

Introduction to Left Join in Oracle

Left Join in Oracle is one type among many types of joins available in the oracle database. Left join means it includes all the rows of the left table and only those rows from the other table where joined rows are equal. It is a very commonly used join in extracting details from tables present in the database based on some condition.

Syntax:

So, now we are going to take a look at the syntax for the Left Join in Oracle.  The syntax is pretty simple as you can see below.

SELECT
column_list
FROM
TABLE1
LEFT JOIN TABLE2 ON
join_condition

Parameters of Left Join in Oracle

• column_list: The list of columns that the user wants to retrieve from the database.
• TABLE1: This represents the table on the left side of the LEFT JOIN.
• TABLE2: This represents the table on the right side of the LEFT JOIN.
• Join_condition: It is the condition that both tables have to satisfy for LEFT JOIN.

How does Left Join Work in Oracle?

So, after going through the syntax in the previous section of this article. In this section, we are going to know how the left join works. If we look at the syntax once again we will see that there are two symbols or variables named TABLE1 and TABLE2. The TABLE1 represents the left table which is written just before the “LEFT JOIN” and TABLE2 represent the table on the right side of the “LEFT JOIN” clause written in the syntax.

Each row from both tables is compared based on the join condition. If the join condition is satisfied by any pair of row/rows then the result set will consist of all column values (columns present in the SELECT clause of the query) for these row/rows from both the tables. In case there is no match between rows of the table based on the condition then the result set will consist of all column values(present in SELECT clause) for the rows in TABLE1 along with NULL value for the corresponding rows of each column(present in the SELECT clause) from TABLE2.

In short, LEFT JOIN IN ORACLE returns only equal value rows from the TABLE2 (table on the right side of the LEFT JOIN clause).

Now, for a better understanding of the left join in Oracle. Let us go through some of the examples below.

Examples of Left Join in Oracle

Below are a number of examples of Left Join in Oracle:

1. Left Join Between Two Tables

This example is used to show the basic left join example between two tables. In this case, we are using two tables named employee and vehicle. The employee table is the left table whereas the vehicle table will be the other table. Let us look at the query below for the same.

Code:

Query
select e1.name, e1.employee_id,
v1.vehicle_id,v1.VEHICLE_NAME
FROM
employee e1 LEFT JOIN vehicle v1
ON
e1.vehicle_id= v1.VEHICLE_ID;

Explanation to the above code: If we look at the query we can see that we have given two columns from the employee table and two columns from the vehicle table in the query and the join condition here is vehicle id from employee table as well as vehicle id from vehicle table side. So now let us run the query in SQL developer to see how the output looks like.

Output:

The output above shows the results where we can see the four columns as written in the SELECT statement being displayed.

2. Left Join Between Multiple Tables

This example is used to show that we can do left join between multiple tables we will pick three tables. These tables are employees, vehicles, and orders.

The left table will be the orders table and the other tables in the join are going to be an employee and vehicle table. Let us look at the query below.

Code:

Query
SELECT
od.order_id,
e1.name AS employee_name,
v1.vehicle_name,
od.order_name
FROM
orders od
LEFT JOIN employee e1 ON
e1.EMPLOYEE_ID = od.EMPLOYEE_ID
LEFT JOIN vehicle v1 ON
v1.EMPLOYEE_ID = od.EMPLOYEE_ID;

Explanation to the above code: If we look at this query then we can see that the join condition contains two conditions overall. One condition is with the vehicle table in which we are comparing the order table employee id with vehicle table employee id and the second condition is with employee table in which the order table employee id is in comparison with employee table employee id. In the SELECT clause of the query, we have given four columns where order_id and order_name columns are from the orders table the employee_name is from the employee table and the vehicle_name is from the vehicle table.

Output:

The output above shows the results where we can see the four columns as written in the SELECT statement being displayed.

3. Left Join with USING Clause

In this example, we will go through one more way to do left join by the use of the USING clause. The USING column states which column or columns the query is going to test for equality while doing a left join. Let us look at the query below to get an understanding.

Code:

Query
Select
employee.NAME,
VEHICLE.VEHICLE_ID,
VEHICLE.VEHICLE_NAME
FROM
Employee LEFT JOIN VEHICLE USING (employee_id);

Explanation to the above code: If we look at the query we are using the employee_id as the column inside the USING clause to check equality. One point to remember is that the column inside the USING clause must be present in both the tables.

Output:

Since the employee table was the left table so we can see that all the rows of the name column are displayed and the nonmatching rows of the vehicle table are displayed with null values.

Conclusion

In this article, we discussed Left Join in Oracle with its syntax and working. We also went through some examples to better understand the ways in which we can use the LEFT JOIN.

Recommended Articles

This is a guide to Left Join in Oracle. Here we discuss syntax, parameter, working and different examples of left join in oracle with code implementations. You can also go through our other related articles to learn more –

1. MINUS in Oracle
2. INTERSECT in Oracle
3. DISTINCT in Oracle
4. Joins in Oracle
5. Learn the Examples of Joins in PostgreSQL