EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Full Outer Join
Secondary Sidebar
Oracle Tutorial
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Oracle Full Outer Join

By Priya PedamkarPriya 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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,992 ratings)

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
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

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

EDUCBA Login

Forgot Password?

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

EDUCBA

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

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

Let’s Get Started

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