Limitation of Oracle syntax for left outer join (+)
Oracle guys usually use Oracle native left outer join syntax to do left outer join, such as
cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+); C X Y D X Y ---------- ---------- ---------- ---------- ---------- ---------- 11 1 51 21 1 51 13 3 53 23 3 40 15 5 55 25 5 54 14 4 54 12 2 52
An more advanced way is:
cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+) and t2.y(+)>50; C X Y D X Y ---------- ---------- ---------- ---------- ---------- ---------- 11 1 51 21 1 51 15 5 55 25 5 54 14 4 54 13 3 53 12 2 52
Be note, this is different from:
cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+) and t2.y>50; C X Y D X Y ---------- ---------- ---------- ---------- ---------- ---------- 11 1 51 21 1 51 15 5 55 25 5 54
The t2.y(+) > 50, as a join condition, is applied at join time, but t2.y>50 is applied after the result set is return from joining. Also note that, as t2 > 50 make sure that the rows in t2 is not null, so the SQL statement will become an normal equi-join. This is clear in the execution plans:
cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+) and t2.y(+)>50; Execution Plan ---------------------------------------------------------- Plan hash value: 1823443478 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 65 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN OUTER | | 5 | 65 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| T1 | 5 | 30 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T2 | 1 | 7 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."X"="T2"."X"(+)) 3 - filter("T2"."Y"(+)>50) cbo@ORCL> select * from t1,t2 where t1.x = t2.x(+) and t2.y>50; Execution Plan ---------------------------------------------------------- Plan hash value: 2959412835 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 13 | 5 (20)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T2 | 1 | 7 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T1 | 5 | 30 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."X"="T2"."X") 2 - filter("T2"."Y">50)
But this method have its own limitations. The SQL Reference list such limitations:
- You cannot specify the (+) operator in a query block that also contains
FROMclause join syntax. - The (+) operator can appear only in the
WHEREclause or, in the context of left-correlation (when specifying theTABLEclause) in theFROMclause, and can be applied only to a column of a table or view. - If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
- The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
- You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. For example, the following statement is not valid:
– The following statement is not valid:
SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;
However, the following self join is valid:SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id
ORDER BY e1.employee_id, e1.manager_id, e2.employee_id; - The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
- A
WHEREcondition containing the (+) operator cannot be combined with another condition using theORlogical operator. - A
WHEREcondition cannot use theINcomparison condition to compare a column marked with the (+) operator with an expression.
It’s a little confused to read these statements, for the limitation I highlighted, there are two variations:
1. I can’t add OR operand predicates on the right table at join time, such the one below expressed in ANSI-SQL.
select t1.c,t2.d from t1 left outer join t2 on (t1.x = t2.x and (t2.y>23 or t2.y<23) );
2. I can’t combine OR operands as left outer join condition. such the one below expressed in ANSI-SQL.
select t1.c,t2.d from t1 left outer join t2 on (t1.x = t2.x or t1.y = t2.y);
Another limitation is that, I can’t add predicate on the left table at join time, such as the one below expressed in ANSI-SQL.
select * from t1 left outer join t2 on (t1.x = t2.x and t1.c = 11);
Clearly, Oracle recommend use ANSI-SQL style left outer join syntax, just as the examples above.
But another question comes, how Oracle implement ANSI-SQL left outer join syntax, and how to rewrite these unsupported statements in native/complex ways?
favorited this one, guy
I would like to consider the ability of saying thanks to you for your professional instruction I have always enjoyed checking out your site.