Filter
Top Products
Using SQL Select
128 DataWindow .NET
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 Tab le3, 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
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:
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" LEFT OUTER JOIN "department" ON
"employee"."dept_id" = "department"."dept_id"}
If you select the condition with rows from department that have no
employee
, you create a right outer join instead.