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.
