EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • 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
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Full Outer Join
 

Oracle Full Outer Join

Priya Pedamkar
Article byPriya Pedamkar

Oracle Full Outer Join

Introduction to Oracle Full Outer Join

Oracle Full Outer Join is a clause used in Oracle database to join two or more tables based on a join condition which returns all the columns with rows in the LEFT-HAND table as well as all the columns with rows in the RIGHT-HAND table with their values. When the join condition is met but in case if the join condition is not met by one of the rows of either table then the columns of that table will return with NULL values in the final result set.

 

 

Syntax:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

SELECT column/columns
FROM Left_table
FULL OUTER JOIN Right_table
ON join_condition;

Parameters:

  • Left_table: The table on the left side of the FULL OUTER JOIN condition.
  • Right_table: The table on the Right Side of the FULL OUTER JOIN condition.
  • join_condition: The condition based on which the result set will be retrieved from the database.

How does Full Outer Join Works?

  • It is important to know actually how the full outer join works in Oracle. If we look at the syntax we will see that there are two tables one is called the left _table or T1 and the other as Right_table or T2. When the FULL OUTER JOIN query is executed in the database and if the rows from both T1 and T2 meet the join condition specified in the query then the query will return columns of both rows in the result set. We can say that the row in the T1 table matches the row in the T2 table. We can also write FULL OUTER JOIN as FULL JOIN, both mean the same.
  • The next case can be that suppose the row in the T2 table does not have any row in T1 table which matches the join condition provided in the query. In that case, the full outer join will return columns from T2 table with null values for the columns in the T1 table.
  • Another case can be the vice-versa, Let us suppose that the T1 table does not have any row in the T2 table which matches the join condition provided in the query. In that case, the full outer join will return columns from T1 table and null values in the columns of T2 table.

Examples of Oracle Full Outer Join

Given below are the examples:

Example #1

In this example, we will try to extract data from two tables named Employee and Vehicle based on the join condition that both of them have the same value in the employee id column. So as we discussed earlier the result set will be based on the join condition.

Code:

SELECT
m.NAME,
v.VEHICLE_NAME
FROM
employee m
FULL OUTER JOIN vehicle v ON m.EMPLOYEE_ID = v.EMPLOYEE_ID
ORDER BY
m.NAME;

In the above query if we see the Left-hand table is an employee and the Right-hand table is a vehicle. So as we have discussed in the earlier section of this article that the rows of the two columns which are specified in the SELECT statement satisfy the join condition will show their actual value in the result set and the rows of the columns present in the vehicle table which do not satisfy the join condition will have null values. So, let us run the query in Oracle SQL developer and look at the output.

Output:

Example #1

If we see the output, It has a mix of values as well as NULL values. The rows of the vehicle table which did not satisfy the join condition have returned as NULL values.

Example #2

  • In this example, we are going to use FULL OUTER JOIN to find employees who do not have any Vehicle assigned to them.
  • To execute this example we will have to modify the query as in earlier example if we see that the result set contained both NULL and NOT NULL values. In this query we are going to use a WHERE condition in the query so that the result set is filtered with only the employee names that do not have any vehicle assigned.

Code:

SELECT
m.NAME,
v.VEHICLE_NAME
FROM
employee m
FULL OUTER JOIN vehicle v ON m.EMPLOYEE_ID = v.EMPLOYEE_ID
WHERE v.VEHICLE_NAME IS NULL
ORDER BY
m.NAME;

If we look at the query above we can see that there is a WHERE condition added after the join condition. The WHERE condition selects only those rows where the vehicle name is NULL. Let us execute the query in Oracle SQL developer.

Output:

oracle full outer join 2

In the above output, we can see the result only contains the employee names with no assigned vehicle.

Example #3

  • In this example, we are going to use FULL OUTER JOIN to find the Vehicles which are not assigned to any employee.
  • To execute this condition we will just modify the WHERE condition of the previous example and in place of the Vehicle name we are going to use Employee name.

Code:

SELECT
m.NAME,
v.VEHICLE_NAME
FROM
employee m
FULL OUTER JOIN vehicle v ON m.EMPLOYEE_ID = v.EMPLOYEE_ID
WHERE m.NAME IS NULL
ORDER BY
m.NAME;

As we can see we have just changed the column name in the WHERE condition. Let us run the query in SQL developer and check the output.

Output:

oracle full outer join 3

As we can see that the output contains the vehicles which do not have an employee assigned to them.

Conclusion

In this article, we have discussed the definition of the FULL OUTER JOIN and also about the working of the same. Later on, we also saw some examples and executed them in the SQL developer to get an understanding of how to use it in the database.

Recommended Articles

This is a guide to Oracle Full Outer Join. Here we discuss the introduction, examples and how does Full Outer Join Works? You may also have a look at the following articles to learn more –

  1. MySQL Outer Join
  2. Types of Joins in SQL Server
  3. Table in MySQL
  4. SELECT in MySQL
  5. OUTER Join in Oracle | Examples

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
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 Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW