Using ANSI outer joins

All InfoMaker database interfaces provide support for ANSI SQL-92 outer join SQL syntax generation. InfoMaker 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):

OUTER-join ::= 
table-reference {LEFT | RIGHT} OUTER JOIN table-reference ON search-condition 

table-reference ::= 
table_view_name [correlation_name] | OUTER-join

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 InfoMaker, 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.

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:

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 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.

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:

employee.dept_id = department.dept_id and rows from department that have no employee

The syntax generated is:

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.

For more about outer joins, see your DBMS documentation.