Deep Analysis of Left Outer Join and Right Outer Join Using (+) Sign in Oracle 11g

Fundamental Concepts of Outer Joins

In relational databases, outer joins represent a crucial data query technique that preserves all records from one table even when no matching records exist in another table. Oracle Database provides two primary approaches for implementing outer joins: using standard ANSI SQL explicit JOIN syntax and Oracle-specific implicit syntax with the (+) symbol.

Working Principle of the (+) Symbol

In Oracle 11g, the (+) symbol denotes the "optional" table in join operations. When used in WHERE clause conditional expressions, it indicates that the table on the side containing the symbol may include null or missing values during the join. While this syntax offers conciseness, precise understanding of positional impact on join types is essential.

Consider the following two query examples:

SELECT P.Part_Id, S.Supplier_Name
FROM Part P, Supplier S
WHERE P.Supplier_Id = S.Supplier_Id (+)

In this query, the (+) symbol appears on the Supplier_Id column of the Supplier table, indicating that Supplier is the "optional" table. Functionally, this equates to P LEFT OUTER JOIN S, preserving all records from the Part table even when no matching supplier information exists in the Supplier table.

SELECT P.Part_Id, S.Supplier_Name
FROM Part P, Supplier S
WHERE S.Supplier_Id (+) = P.Supplier_Id

Although this query swaps the sides of the equality operator, the (+) symbol remains on the Supplier table's column, making it functionally identical to the first query. Syntactically, this can be interpreted as S RIGHT OUTER JOIN P, but the practical effect still preserves all Part table records.

Rules for Determining Join Types

When determining join types using the (+) symbol, understanding positional semantics is key:

• When the (+) symbol appears on the right side of the equals sign, the left table is the primary table, executing a left outer join
• When the (+) symbol appears on the left side of the equals sign, the right table is the primary table, executing a right outer join

However, it's important to recognize that TableA LEFT OUTER JOIN TableB is functionally equivalent to TableB RIGHT OUTER JOIN TableA. This equivalence means that the terms "left" or "right" primarily aid in visualizing join directionality, while actual data results remain consistent.

Implicit JOIN vs Explicit JOIN Comparison

Oracle supports two JOIN writing styles: implicit JOIN and explicit JOIN. Implicit JOIN is implemented by listing all tables in the FROM clause and specifying join conditions in the WHERE clause:

SELECT *
FROM A, B
WHERE A.column = B.column(+)

Explicit JOIN uses standard ANSI SQL syntax, directly associating join conditions with table inclusion:

SELECT *
FROM A
LEFT OUTER JOIN B ON A.column = B.column

While both syntaxes are functionally equivalent, explicit JOIN syntax is generally considered more readable and understandable, particularly for complex multi-table join queries. Implicit JOIN mixes join conditions with other WHERE conditions, potentially creating difficulties in comprehension and maintenance.

Practical Application Scenarios

Consider a practical business scenario: we need to display all part information, regardless of whether suppliers provide these parts. Using the (+) symbol syntax discussed in this article, this can be implemented as:

SELECT P.Part_Id, S.Supplier_Name
FROM Part P, Supplier S
WHERE P.Supplier_Id = S.Supplier_Id (+)

In this query, all records from the Part table will be returned. For part records without matching supplier information in the Supplier table, the Supplier_Name column will display as NULL values. This ensures business requirement completeness: "display all parts, irrespective of supplier availability."

Best Practice Recommendations

Based on Oracle official documentation and industry best practices, prioritizing explicit JOIN syntax for new database application development is recommended:

• Explicit JOIN syntax better aligns with ANSI SQL standards, offering improved portability
• Enhanced code readability, especially for complex multi-table joins
• Easier comprehension and maintenance with separated join and filter conditions
• Avoidance of implicit JOIN limitations and potential issues

However, for maintaining existing legacy code using the (+) symbol, understanding this syntax's principles and rules remains necessary.

Technical Details and Considerations

When using the (+) symbol for outer joins, several important details require attention:

• The (+) symbol must be applied to all columns involved in join conditions
• Mixing FROM clause JOIN syntax with (+) operator in the same query block is prohibited
• The (+) operator can only appear in the WHERE clause
• Self-outer-joins using the (+) operator are not permitted
• WHERE conditions containing the (+) operator cannot combine with other conditions using OR logical operator

Understanding these limitations helps prevent unexpected query results or syntax errors when using the (+) symbol.

Conclusion

The (+) symbol in Oracle 11g provides a concise method for implementing outer join operations, but its syntactic rules require accurate comprehension. By mastering how the (+) symbol's position determines join types and understanding differences between implicit and explicit JOINs, developers can write and maintain database queries more effectively. While modern development practices recommend explicit JOIN syntax, proficiency with the (+) symbol remains an important skill for understanding and maintaining existing codebases.