Left join in Oracle with examples

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.

Venn diagram of Sql Left 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.

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)

Student Table in Oracle
Student Table

2. Payment Table (Right table)

Payment Table in Oracle
Payment 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.

Left join output

Left join example in Oracle
Left join example in Oracle

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.

where condition in Left join
where condition in Left join

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)

Student Table
Student Table

2.Subject Table (Right table)

Subject Table
Subject Table

3.Grade Table

Grade Table
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.

Output after combining Student and Subject Table

Left join of two tables in Oracle
Left join of two tables in Oracle

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.

Output after combining Student,Subject and Grade Table

Left join of multiple tables in Oracle
Left join of multiple tables in Oracle

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.