Left join in Oracle with examples
Contents
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.
|
1 2 3 4 |
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.
|
1 2 3 4 |
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.
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 8 9 |
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.
