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 OJSyntax in the
online Help.
The syntax for ANSI outer joins is generated according to
the following BNF (Backus Naur form):
|
1 |
OUTER-join ::= <br><span>table-reference</span> {LEFT | RIGHT} OUTER JOIN <span>table-reference</span> ON <span>search-condition</span> <br> <br><span>table-reference</span> ::= <br><span>table_view_name</span> [<span>correlation_name</span>] | 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 {ojthat is parsed by the driver
...}
and replaced with DBMS-specific grammar:
|
1 |
SELECT Table1.col1, Table2.col1, Table3.col1<br>FROM {oj {oj Table1 LEFT OUTER JOIN Table2 ON Table1.col1 = Table2.col1}<br>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 |
SELECT employee.dept_id, department.dept_id<br>FROM {oj "employee" RIGHT OUTER JOIN "department" ON "employee"."dept_id" = "department"."dept_id"} |
If you select the condition, rows from, you create
employee that have no department
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.