I prefer the “Oracle syntax” for outer joins over the ANSI syntax.
I like the conciseness of the Oracle syntax, and I’ve been using it for the last 30 years or so.
I totally understand people that prefer the ANSI syntax.
I agree that the ANSI syntax is richer and more complete (although Oracle 12c reduced the gaps by adding more capabilities to the Oracle syntax).
I get it that for many people the ANSI syntax is easier to read and write.
I believe that both styles are legit. But I think it’s important to choose only one of them and make this choice part of your coding standards – whether it is for a specific project or company-wide or anything in between; in the level in which coding standards are defined in your organization.
By the way, even when using the ASNI syntax, Oracle converts it internally to its proprietary syntax
So for me the standard is using the Oracle syntax, and in 99% of the queries it’s trivial. But sometimes there are queries that are more challenging. This post is about one such case.
I want to get all the columns from a specific record in T1. In addition, I want to get the column X of a specific record from T2, if such a record exists.
This is the query using the ANSI syntax:
select t1.*, t2.x
from t1
left join t2 on t2.id = :b2
where t1.id = :b1;
How can we write it using the Oracle syntax?
One of the limitations of the Oracle syntax for left/right outer join is that there must be at least one join condition. If there are only filter conditions, the outer join operator (+) is silently ignored and the query becomes de facto an inner join. So writing the query like this
select t1.*, t2.x
from t1, t2
where t1.id = :b1
and t2.id(+) = :b2;
will not give us the required results. This is not an outer join. We can see from the execution plan that the join operation is NESTED LOOPS and not NESTED LOOPS OUTER.
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 113 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 113 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 26 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 87 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | T1_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."ID"=TO_NUMBER(:B2))
5 - access("T1"."ID"=TO_NUMBER(:B1))
Since we need only one column from T2, we can simply use a scalar subquery:
select t1.*,
(select t2.x from t2 where t2.id = :b2) x
from t1
where t1.id = :b1;
But what if we want to get all the columns from T2? I wouldn’t use a scalar subquery for every column.
This is the query using the ANSI syntax:
select t1.*, t2.* from t1 left join t2 on t2.id = :b2 where t1.id = :b1;
We can get an idea how to write it using the Oracle syntax, by looking at the execution plan of the previous query:
ORA$BASE> @x 2 select t1.*, t2.* 3 from t1 4 left join t2 on t2.id = :b2 5 where t1.id = :b1; Explained. ORA$BASE> @xplan PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1875989973 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 139 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 1 | 139 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 87 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | T1_PK | 1 | | 1 (0)| 00:00:01 | | 4 | VIEW | VW_LAT_C83A7ED5 | 1 | 52 | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 52 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."ID"=TO_NUMBER(:B1)) 6 - access("T2"."ID"=TO_NUMBER(:B2)) 19 rows selected.
The execution plan shows that Oracle does a NESTED LOOPS OUTER Join between T1 and a LATERAL Inline View (note the VIEW operation with the VW_LAT… name) that contains the subquery on T2.
We can do the same. We put the subquery on T2 inside an inline view, and then apply the outer join operator (+) on the inline view. In order to do it we have to declare the inline view as LATERAL (although we don’t refer to any column of T1 in the inline view), because (+) is not allowed on non-lateral inline views.
select t1.*, t2.*
from t1,
lateral (select t2.* from t2 where t2.id = :b2)(+) t2
where t1.id = :b1;
Anther option is to trick the optimizer, and make it believe the filter condition on T2 is actually a join condition. We can do it by including some reference to T1 in the filtering expression that doesn’t change the expression value. Here are two examples:
select t1.*, t2.*
from t1, t2
where t1.id = :b1
and t2.id(+) = nvl2(t1.id,:b2,:b2) ;
select t1.*, t2.*
from t1, t2
where t1.id = :b1
and t2.id(+) = t1.id-t1.id + :b2 ;