Outer Joins
The SQL construct for outer joins varies widely among database vendors. For this reason, the DB Interface Module provides a variety of methods to generate vendor-specific outer join constructs. The major difference between these methods is whether or not they comply with the ANSI SQL 92 syntax. ANSI-compliant outer joins are written as part of the SQL FROM clause; ANSI-noncompliant joins are written as part of the SQL WHERE clause.
You must use ANSI syntax, and write outer joins in the FROM clause, if you use any of the following DB Access Modules:
• DB2 CLI
• Microsoft SQL Server
• PostgreSQL
• ODBC
• MySQL
With the DB Access Modules listed below, you can use either ANSI or non-ANSI syntax. If you use non-ANSI syntax, you must write outer joins in the WHERE clause.
• Oracle OCI
• Sybase Open Client
If you use ODBC or Oracle OCI access modules, please note their unique features:
• The ODBC interface can support either ANSI or non-ANSI syntax, depending on the compliance of the backend database. However, you must use ANSI syntax with the Rogue Wave ODBC access module.
• Oracle OCI supports both ANSI-compliant outer joins and ANSI-noncompliant outer joins using the Oracle join operator (+). Oracle recommends using the ANSI-compliant outer joins in the FROM clause rather than using the join operator in the WHERE clause.
See the appropriate guide for your DB Access Module to determine the supported syntax and other particulars of outer joins. The following two sections demonstrate how an application can generate appropriate outer join syntax for ANSI-compliant databases in the FROM clause, and for ANSI-noncompliant databases in the WHERE clause.
Outer Join Constructs in the FROM Clause (ANSI-Compliant)
To generate a SQL 92 outer join construct as part of the FROM clause, use the following procedure:
1. Create an
RWDBJoinExpr using any of the related global functions, such as
rwdbOuter().
2. Set any join condition or indicate the join columns using the overloaded method RWDBJoinExpr::on(). If only the join columns are specified, the join condition defaults to equality on common columns.
3. Add the join expression to the FROM clause using RWDBSelector::from(), or add it to another join expression to form a nested join expression.
The following three examples show how to implement three kinds of outer joins in ANSI-compliant syntax: the simple outer join, the nested outer join, and the multiple outer join.
To use this code, the <ver> placeholder in any library names would need to be replaced with the actual digits representing the library version number.
A Simple Outer Join
// This example shows a right outer join of two tables,
// emp and dept.
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main()
{
RWDBDatabase myDbase = RWDBManager::database(
"msq<ver>12d.dll", // Access Module name
"server_name", // server name
"user_name", // user name
"pass_word", // password
"db_name" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable employee = myDbase.table("emp");
RWDBTable dept = myDbase.table("dept");
RWDBSelector selector = myDbase.selector();
selector << employee["empnum"] << employee["ename"]
<< dept["dname"];
RWDBJoinExpr join = rwdbRightOuter(employee, dept); //1
join.on(employee["deptno"] == dept["deptno"]); //2
selector.from(join); //3
RWDBReader rdr = selector.reader(myConnection);
int empnum;
RWCString ename, dname;
while (rdr())
{
rdr >> empnum >> ename >> dname;
std::cout << empnum << "\t" << ename << "\t"
<< dname
<< std::endl;
}
return 0;
}
On //1, an isolated right outer join is constructed between the tables emp and dept using the global function rwdbRightOuter().
On
//2, the join criterion is specified on the outer join using the
on() method of
RWDBJoinExpr. The join criterion specifies that the rows in both the tables have to be joined with their respective
deptno fields matching. This makes the join expression complete and ready to be attached to the
FROM clause of the selector.
On //3, the join is explicitly attached to the FROM clause of the selector, completing the join procedure. Join expressions must be explicitly joined to the selector. They are not implicitly attached.
To use this code, the <ver> placeholder in any library names would need to be replaced with the actual digits representing the library version number.
A Nested Outer Join
// The join of emp and dept, from the previous example,
// joins itself to a third table, loc.
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main()
{
RWDBDatabase myDbase = RWDBManager::database(
"msq<ver>12d.dll", // Access Module name
"server_name", // server name
"user_name", // user name
"pass_word", // password
"db_name" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable employee = myDbase.table("emp");
RWDBTable dept = myDbase.table("dept");
RWDBTable locate = myDbase.table("loc");
RWDBSelector selector = myDbase.selector();
selector << employee["empnum"] << employee["ename"]
<< dept["dname"]
<< locate["lname"];
RWDBJoinExpr join1 = rwdbRightOuter(employee, dept); //1
join1.on(employee["deptno"] == dept["deptno"]); //2
RWDBJoinExpr join2 = rwdbLeftOuter(join1, locate); //3
join2.on(dept["locno"] == locate["locno"]); //4
selector.from(join2); //5
RWDBReader rdr = selector.reader(myConnection);
int empnum;
RWCString ename, dname, lname;
while (rdr())
{
rdr >> empnum >> ename >> dname
>> lname;
std::cout << empnum << "\t" << ename << "\t"
<< dname << "\t" << lname
<< std::endl;
}
return 0;
}
On //1, an isolated right outer join is constructed between the tables emp and dept using the global function rwdbRightOuter().
On
//2, the join criterion is specified on the outer join using the
on() method of
RWDBJoinExpr. The join criterion specifies that the rows in both
emp and
dept must be joined with their respective
deptno fields matching. This makes the current join expression complete and ready to be attached to another join expression.
On //3, another join expression is created by joining the previously constructed outer join and table loc using the global function rwdbLeftOuter(). Using a join expression instead of table creates a nested join. You can have any level of nesting in the join expression.
On
//4, the join criterion is attached to the second outer join using the
on() method on
RWDBJoinExpr. The join criterion specifies that the rows in both
dept and
loc have to be joined with their respective
locno fields matching. This makes the nested join expression complete and ready to be attached to the
FROM clause of the selector.
On //5, this join is explicitly attached to the FROM clause of the selector, completing the nested join procedure. Join expressions must be explicitly joined to the selector; they are not implicitly attached.
To use this code, the <ver> placeholder in any library names would need to be replaced with the actual digits representing the library version number.
A Multiple Outer Join
// This example shows the use of multiple joins in a query.
// More than one join are included in the FROM clause.
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main()
{
RWDBDatabase myDbase = RWDBManager::database(
"msq<ver>12d.dll", // Access Module name
"server_name", // server name
"user_name", // user name
"pass_word", // password
"db_name" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable employee = myDbase.table("emp");
RWDBTable salary = myDbase.table("salary");
RWDBTable dept = myDbase.table("dept");
RWDBTable locate = myDbase.table("loc");
RWDBSelector selector = myDbase.selector();
selector << employee["empnum"] << employee["ename"]
<< dept["dname"] << locate["lname"]
<< salary["base_salary"];
RWDBJoinExpr join1 = rwdbLeftOuter(employee, salary); //1
join1.on(employee["empnum"] == salary["empnum"]); //2
RWDBJoinExpr join2 = rwdbLeftOuter(dept, locate); //3
join2.on(dept["locno"] == locate["locno"]); //4
selector.from(join1); //5
selector.from(join2); //6
selector.where(employee["deptno"] == dept["deptno"]);
RWDBReader rdr = selector.reader(myConnection);
int empnum;
RWCString ename, dname, lname;
RWDecimalPortable salary;
while (rdr())
{
rdr >> empnum >> ename >> dname >> lname >> salary;
std::cout << empnum << "\t" << ename << "\t"
<< dname << "\t" << lname << "\t"
<< salary << std::endl;
}
return 0;
}
On //1, an isolated left outer join is constructed between the tables emp and salary using the global function rwdbLeftOuter().
On
//2, the join criterion is specified on the outer join using the
on() method of
RWDBJoinExpr. The join criterion specifies that the rows in
emp and
salary must be joined with their respective
empnum fields matching. This makes the first join expression complete and ready to be attached to the
FROM clause of the selector.
On //3, another join expression is created by joining the tables dept and loc using the global function rwdbLeftOuter().
On
//4, the join criterion is specified on the second outer join using the
on() method on
RWDBJoinExpr. The join criterion specifies that the rows in both
dept and
loc must be joined with their respective
locno fields matching. This makes the second join expression complete and ready to be attached to the
FROM clause of the selector.
On //5 and //6, both these joins are explicitly attached to the FROM clause of the selector, completing the multiple join procedure. Join expressions must be explicitly joined to the selector; they are not implicitly attached.
Outer Join Constructs In the WHERE Clause (ANSI-Noncompliant)
If your database does not support SQL 92 syntax, you can generate an outer join construct as part of the WHERE clause. Use the following procedure:
2. Specify the criterion, combined with any other criterions, in the WHERE clause.
The following code shows how to implement the procedure:
To use this code, the <ver> placeholder in any library names would need to be replaced with the actual digits representing the library version number.
An Outer Join in the WHERE Clause
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main() {
RWDBDatabase myDbase = RWDBManager::database(
"ctl<ver>12d.dll", // Access Module name
"sybserver", // server name
"tutor", // user name
"passwd", // password
"db" // database name
);
RWDBConnection myConnection = myDbase.connection();
RWDBTable purchases = myDbase.table("purchase");
RWDBTable videos = myDbase.table("videos");
RWDBSelector selector = myDbase.selector();
selector << purchases["orderNum"]
<< purchases["supplierID"] << videos["title"];
selector.where(purchases["videoID"].leftOuterJoin(videos["ID"])); //1
RWDBReader rdr = selector.reader(myConnection);
int purchaseOrderNumber, supplierID;
RWCString title;
while (rdr()) {
rdr >> purchaseOrderNumber >> supplierID >> title;
std::cout << purchaseOrderNumber << "\t"
<< supplierID << "\t" << title
<< std::endl;
}
return 0;
}
On
//1, a left outer join expression is constructed and set in the
WHERE clause. The criterion says that the two tables
purchase and
videos should be joined such that the
videoID column in
purchase should match the
ID column in
videos. In this case, the tables list for the
FROM clause is implicitly derived from the selection list. The rest of the code is similar to
Example 4, “Performing a join.”Please note that different databases may impose restrictions on generating outer join constructs in the WHERE clause:
• Some databases do not support outer joins in which one table is outer to more than one other table; other databases give unexpected results under these circumstances. All other joins besides outer joins should work consistently across all databases.
Please see the appropriate DB Access Module guide for more details.