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 OUTER Join in Oracle
 

OUTER Join in Oracle

Priya Pedamkar
Article byPriya Pedamkar

Updated March 27, 2023

OUTER Join in Oracle

 

 

What is an OUTER join in Oracle?

A JOIN is a query that combines rows from two or more Tables, View or Materialized View. There are four Oracle proprietary joins, an OUTER join is one of them. An OUTER join returns all rows that satisfy the join condition and also returns non-matching rows from one table and for those non-matching rows other table returns null. It means it returns matching and non-matching rows from one table and from another table returns matching rows and null for non-matching rows.

Watch our Demo Courses and Videos

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

Points of Concentration

  • An OUTER join combines multi Tables, View or Materialized View to retrieve data.
  • An OUTER join returns all rows that satisfy the join condition and also returns non-matching rows.
  • Oracle provides an OUTER join operator (+) to perform an OUTER join on multiple Tables and returns all rows from one table and NULL from another table for non-matching rows.
  • If the OUTER join operator ‘(+)’ appears with the column of table A, Oracle returns all rows of table B and table A returns NULL for all rows that have no matching row(s).
  • The columns in the conditions need not be part of the SELECT list.

Syntax:

SELECT Table1.Column_1, Table2.Column_n FROM Table1, Table2 WHERE    Table1.Column_2 (+) = Table2.Column_2;

SELECT Table1.Column_1, Table2.Column_n FROM Table1, Table2 WHERE  Table1.Column_2 = Table2.Column_2 (+);

Description:

  • Col_1/2/n: The column(s) or calculation as per your requirement.
  • Table 1/2: As per your requirement
  • WHERE: It’s mandatory to use the OUTER join operator.
  • +: It is an OUTER join operator.

Example:

SELECT E.Ename, D.Deptno, D.Dname FROM Emp E, Dept D
WHERE E.Deptno (+) = D.Deptno ORDER BY E.Deptno;

Output:

Outer join in oracle - example1

SELECT E.Ename, D.Deptno, D.Dname FROM Emp E, Dept D
WHERE E.Deptno=D.Deptno (+) ORDER BY E.Deptno;

Output:

Outer join in oracle - example2

Explanation: In the first example, OUTER join operator appears in Emp table (Alias name is E) so it returns all matching rows from the table Emp but Dept table (Alias name is D) returns all matching rows with non-matching rows and for that non-matching rows Emp table returns null values. According to ANSI, it’s RIGHT OUTER join.

But in the second example, OUTER join operator (+) appears in Dept table (Alias name is D) and it returns only matching rows because all rows of Emp table matched with Dept table rows and there is no any unmatched row in Emp table. That’s why it returns only matched rows. According to ANSI, it’s LEFT OUTER join.

Rules and Restrictions

Before implementing Oracle OUTER Join, must know some important rules and restrictions of OUTER Join. Listed below:

  • The OUTER join operator (+) can appear only in the WHERE clause.
  • The OUTER join operator (+) can appear in the context of the left correlation in the FROM clause and can be applied only to a column of a Table or View.
  • If ‘A’ and ‘B’ are joined by multiple join conditions, the OUTER join operator (+) must be applied in all those conditions.
  • The OUTER join operator (+) can be applied only to a column, not to an arbitrary expression.
  • The OR logical operator cannot be used to combine the other condition if a condition containing the OUTER join operator (+)
  • Using an IN operator, the column cannot perform conditional compare with The OUTER join operator (+) with an expression.
  • A condition cannot compare any column marked with the OUTER join operator (+) with a subquery.
  • If the OUTER join operator (+) appears with a column, a condition cannot be applied on that column to compare with a subquery.

Implementations of Oracle OUTER join with Examples

In this section, we’ll see the implementation of Oracle OUTER join and its behavior. For that, we will use the below sample tables (Emp, Dept) with 14 and 8 records respectively to understand the Oracle OUTER join behavior.

SELECT * from Emp;

Output:

Outer join - from Emp

SELECT * from Dept;

Output:

 from Dept

  • The OUTER join operator (+) with other clauses

SELECT E.Ename, D.Deptno, D.Dname FROM Emp E, Dept D
WHERE E.Deptno=D.Deptno (+) ORDER BY E.Deptno (+);

Output:

OUTER join operator

SELECT E.Ename, D.Deptno, D.Dname FROM Emp E, Dept D
WHERE E.Deptno=D.Deptno (+) GROUP BY E.Ename (+), D.Deptno (+),D.Dname (+);

Output:

OUTER join operator.1

In the above example, the SELECT statement returns an “invalid character” error message. WHY?

Because The OUTER join operator (+) can appear only in the WHERE clause but in the above example, the OUTER join operator (+) appears in the other clauses as well.

  • The OUTER join for multi conditional query

SELECT E.Ename, D.Deptno, D.Dname, D.Loc FROM Emp E, Dept D
WHERE E.Deptno (+) = D.Deptno AND E.Deptno = 10 ORDER BY E.Deptno;

Output:

multi conditional query 1

SELECT E.Ename, D.Deptno, D.Dname, D.Loc FROM Emp E, Dept D
WHERE E.Deptno (+) = D.Deptno AND E.Deptno (+) = 10 ORDER BY E.Deptno;</code?

Output:

ORDER BY

The above two examples are multi conditional, in the above first example OUTER join operator (+) appears in the first condition only but in the second example, OUTER join operator (+) appears in both the conditions. And because of that results are different.

In the first example, the second condition (without (+) operator) behaves a normal condition that filters the rows and returns the rows that belong to Deptno 10.

But in the second example OUTER join operator (+) appears in all the conditions, it returns all rows (matched and unmatched) of Dept table and matched rows from the Emp table and nulls for the unmatched rows.

TIPS:

  • NVL function can be used to fill the nulls for the unmatched rows.
  • The LOB column(s) cannot be specified in the WHERE clause when the WHERE clause contains any JOINS

Conclusion

Oracle OUTER JOIN is a query that combines multi Tables, View or Materialized View to retrieve data. If you are looking for all records from one table and only matched rows from another table, Oracle OUTER join can be used because it returns matching and non-matching rows from one table and matching rows from another table and nulls for the unmatched row(s).

Recommended Articles

This is a guide to OUTER Join in Oracle. Here we discuss the introduction, Points of Concentration and Implementations of Oracle OUTER join with Examples. You can also go through our other related articles to learn more–

  1. Oracle UNION ALL
  2. Left Join in Oracle
  3. Oracle Operators
  4. ORDER BY in Oracle

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