Using ANSI outer joins
All PowerBuilder database interfaces provide support for ANSI
SQL-92 outer join SQL syntax generation. PowerBuilder supports both
left and right outer joins in graphics mode in the SQL Select
painter, and full outer and inner joins in syntax mode. Depending on
your database interface, you might need to set the OJSyntax DBParm
to enable ANSI outer joins. For more information, see the section called “OJSyntax” in Connection Reference.
The syntax for ANSI outer joins is generated according to the
following BNF (Backus Naur form):
|
1 2 3 4 |
OUTER-join ::= table-reference {LEFT | RIGHT} OUTER JOIN table-reference ON search-condition table-reference ::= table_view_name [correlation_name] | OUTER-join |
Order of evaluation and
nesting
In ANSI SQL-92, when nesting joins, the result of the first
outer join (determined by order of ON conditions) is the operand of
the outer join that follows it. In PowerBuilder, an outer join is
considered to be nested if the table-reference on the left of the
JOIN has been used before within the same outer join nested
sequence.
The order of evaluation for ANSI syntax nested outer joins is
determined by the order of the ON search conditions. This means that
you must create the outer joins in the intended evaluation order and
add nested outer joins to the end of the existing sequence, so that
the second table-reference in the outer join BNF above will always
be a table_view_name.
Nesting example
For example, if you create a left outer join between a column
in Table1 and a column in Table2, then join the column in Table2 to
a column in Table3, the product of the outer join between Table1 and
Table2 is the operand for the outer join with Table3.
For standard database connections, the default generated
syntax encloses the outer joins in escape notation {oj …} that is
parsed by the driver and replaced with DBMS-specific grammar:
|
1 2 3 |
SELECT Table1.col1, Table2.col1, Table3.col1 FROM {oj {oj Table1 LEFT OUTER JOIN Table2 ON Table1.col1 = Table2.col1} LEFT OUTER JOIN Table3 ON Table2.col1 = Table3.col1} |
Table references
Table references are considered equal when the table names are
equal and there is either no alias (correlation name) or the same
alias for both. Reusing the operand on the right is not allowed,
because ANSI does not allow referencing the table_view_name twice in
the same statement without an alias.
Determining left and right outer
joins
When you create a join condition, the table you select first
in the painter is the left operand of the outer join. The table that
you select second is the right operand. The condition you select
from the Joins dialog box determines whether the join is a left or
right outer join.
For example, suppose you select the dept_id column in the
employee table, then select the dept_id column in the department
table, then choose the following condition:
|
1 |
employee.dept_id = department.dept_id and rows from department that have no employee |
The syntax generated is:
|
1 2 |
SELECT employee.dept_id, department.dept_id FROM {oj "employee" RIGHT OUTER JOIN "department" ON "employee"."dept_id" = "department"."dept_id"} |
If you select the condition, rows from employee that have no
department, you create a left outer join instead.
Equivalent statements
The syntax generated when you select table A then table B
and create a left outer join is equivalent to the syntax generated
when you select table B then table A and create a right outer
join.
For more about outer joins, see your DBMS
documentation.