EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Left Join in Oracle

Left Join in Oracle

Priya Pedamkar
Article byPriya Pedamkar

Updated March 24, 2023

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.

ADVERTISEMENT
Popular Course in this category
ORACLE DBA Database Management System Course Bundle - 2 Courses in 1

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

Left Join in Oracle - 1

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:

Left Join in Oracle - 2

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:

with USING Clause

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
ADVERTISEMENT
GOLANG Course Bundle - 6 Courses in 1
23+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
iOS DEVELOPER Course Bundle - 61 Courses in 1
147+ Hours of HD Videos
61 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JAVA SERVLET Course Bundle - 18 Courses in 1 | 6 Mock Tests
56+ Hours of HD Videos
18 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
RED HAT LINUX Course Bundle - 5 Courses in 1
28+ Hours of HD Videos
5 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW