Outer Joins
Oracle supports both of the following:
the ANSI-compliant syntax for outer joins with the join condition in the FROM clause (recommended)the Oracle-specific syntax using the Oracle join operator (+) in the WHERE clause (ANSI-noncompliant)Oracle recommends using the former option, the FROM clause OUTER JOIN syntax rather than the latter Oracle join operator. See the examples on constructing outer joins in the section “Outer Join Constructs In the FROM Clause (ANSI-Compliant),” in the DB Interface Module User’s Guide.
The following example shows how you would write an outer join for Oracle. You may assume that myDbase is a valid RWDBDatabase instance.
An Outer Join for Oracle in ANSI-compliant Syntax
RWDBTable employee = myDbase.table("emp");
RWDBTable depart = myDbase.table("dept");
RWDBTable locate = myDbase.table("loc");
RWDBSelector selector = myDbase.selector();
selector << employee["empnum"] << employee["ename"]
<< employee["deptno"] << depart["deptno"]
<< depart["dname"] << depart["locno"]
<< locate["locno"] << locate["lname"];
// Define and declare join1 as a right outer join between
// the tables emp and dept.
RWDBJoinExpr join1 = rwdbRightOuter(employee, depart);
// Attach the join criterion to the join1 using
// the on() method. This criterion joins the two tables
// emp and dept by their deptno columns.
join1.on(employee["deptno"] == depart["deptno"]);
// Define and declare join2 as a left outer join
// between join1 and table loc, forming a nested join.
RWDBJoinExpr join2 = rwdbLeftOuter(join1, locate);
// Attach the join criterion to the join2 using
// the on() method. This criterion joins the two tables
// dept and loc by their locno columns.
join2.on(depart["locno"] == locate["locno"]);
// Attach join2 explicitly to the selector FROM clause
// using the from() method
selector.from (join2);
