About Join in Oracle 8i
The Oracle relational database uses Structured Query Language, or SQL, to build and maintain database structures, modify data and generate reports from the data. As with other databases that use SQL, the JOIN clause of the SELECT statement is fundamentally important to Oracle, as it temporarily connects separate tables to form more complete data structures. Oracle version 8i used a plus symbol to indicate a variation called the outer join.
Other People Are Reading
-
Select and Join
-
Oracle relational databases use SQL’s SELECT statement to displays lists of data, including fields in tables and calculated results. SELECT has optional clauses to conditionally extract information, sort it and create group headings and totals. With the JOIN clause, you create a virtual table by linking two or more separate tables on common data fields. For example, an orders table has an order number, item code and quantity purchased, but no item description. The item description resides in the items table. To print a list of orders with descriptions, you join the orders and items tables on the item code. By joining the two tables, you have access to the fields in both.
Inner Join
-
The JOIN clause in Oracle 8i SQL has a two basic variations, inner and outer. An inner join combines records from a pair of tables, but if one table has no records that match the other’s, the resulting virtual table contains no records for either. An inner join has entries only where both tables have matching records. For example, an order comments table has order number and comments fields. Not all orders have comments, however; if you link the orders table with the order comments table using an inner join, the virtual table contains only orders with comments.
-
Explicit Outer Join
-
An outer join always includes records from one of the two linked tables regardless whether the other has matching entries or not. If the join is a left outer join, SQL includes the left table; a right outer join includes records from the table on the right. When the SQL SELECT statement includes the words, “LEFT OUTER JOIN” or “RIGHT OUTER JOIN,” it is called an explicit join. The following SELECT statement includes all order records, including those with comments:
SELECT orders.order_number, customer_code, comments FROM orders LEFT OUTER JOIN order_comments ON orders.order_number = order_comments.order_number;
Oracle began using the explicit outer join format with version 9i of their software.
Implicit Outer Join
-
In addition to an explicit outer join, Oracle 8i has an optional syntax that lets you put the join in the WHERE clause. In this case, you specify a left outer join by placing a plus symbol to the left of an equals sign; a right outer join has the plus symbol on the right. This SELECT statement does a left outer join between the orders and order comments tables as before, but it uses the implicit syntax:
SELECT orders.order_number, customer_code, comments FROM orders, order_comments WHERE orders.order_number += order_comments.order_number;
Though the implicit outer join is more compact and easier to write, experts prefer the explicit method as it adheres more closely to standard SQL syntax and avoids ambiguities in the join process.
-
References
- Photo Credit Thinkstock Images/Comstock/Getty Images
