LEFT JOIN in Oracle

A Join statement is used to combine data or rows from two or more tables based on a common field between the tables.

Left join returns all the rows(records) of the first table(left side of join) and matching rows of the second table(right side of join). If there is no matching on the second table, the result will return null. Left Join is also called as Left Outer Join.

Syntax of Left Join

To define a left join, simply we need to add a LEFT JOIN keyword between the two tables. The basic syntax of a LEFT JOIN is as follows.

SELECT column_name(s) 
FROM table1 
LEFT JOIN table2 
ON table1.matching_column = table2.matching_column ;

Here, the given condition could be any expression based on our requirements.

Example 1: LEFT JOIN with two tables in Oracle

In this tutorial let us consider Student and Payment table

1.Student Table (Left table)

2. Payment Table (Right table)

Student table has the student id and their names. Payment table has the transaction details of each student. Both the tables has the column student_id. Let’s join both Student and Payment table to find which student not paid their tuition fees.

SELECT *
FROM Student 
LEFT JOIN Payment 
ON Student.Student_Id = Payment.Student_Id

Left join output

In the query, we have given the Student table Left join Payment table. Also the joining column is Student_Id. As per the Left join, the student table returned all the rows in the left side. The right side table payment returned the rows if the student_id is matched. For the not matching student_id, it returned the null values.

Where condition in Left join

From the Left join output, we can filter only the null values using Where condition. In our use case, we want to find which student not paid their tuition fees. So we are checking where tuition_fees is null.

SELECT *
FROM Student 
LEFT JOIN Payment 
ON Student.Student_Id = Payment.Student_Id
WHERE tuition_fees is null;

Example 2: LEFT JOIN with multiple tables in Oracle

For this example, consider that we have three tables such as Student,Subject and Grade.

• Student Table – It has the student id and name.
• Subject Table – It contains subject details of each student. It references student_Id as foreign key.
• Grade Table – It consists of Student grade details for each subject. It references subject_code and student_id as foreign key.

In this example, we are going to combine all three tables to find subject wise grade details for each student.

1.Student Table (Left table)

2.Subject Table (Right table)

3.Grade Table

Step 1 : Let’s combine the student and subject table using left join. The Student table returns all the rows. The subject table returns rows based on the student_id value. If it is matched, it will return the value. Otherwise it will return the null value.

SELECT stu.Student_Id,
stu.Name,
sub.Subject_Code 
FROM Student stu LEFT JOIN Subject sub 
On stu.Student_Id = sub.Student_Id

Output after combining Student and Subject Table

Step 2 : In the previous step, we got the result set of Student and subject table. Now we need to perform the left join of this result set with Grade table. It will give the grade details of each subject.

SELECT stu.Student_Id,
stu.Name,
sub.Subject_Code,
gr.Grade 
FROM Student stu LEFT JOIN Subject sub 
On stu.Student_Id = sub.Student_Id
LEFT JOIN Grade gr 
ON gr.student_id = stu.student_id
and gr.subject_code = sub.subject_code;

Output after combining Student,Subject and Grade Table

Finally the left join provided the subject wise grade details for each student in Oracle. The student_id #8933 doesn’t have any subject in the Subject table. So it returned null values in the subject_code and grade column.