Outer joins - where to filter?

Last week a colleague asked me during a coffee break why sometimes some outer joins does not show lines with NULL values for parents without children. 

Of course we had no sample schema at hand, but the rough situation he described was 

If I do a outer ANSI join, but then filter in the WHERE for a childs column, the outer join ( parents without children) disappears. 

The way he described it, it doesn't look right. But before doing the headless chicken run, I decided to read the docu and do some testcases. 

My results are simple: the SQL is correct - it just needs to be written correct! 

Here is my little testcase. 

All the tables and queries can be found on livesql. 

I have 2 Tables: PAR - and CLD, where CLD has a column P_ID with a foreign key to PAR.ID. 

The description of my query can be something like 

Give me all Parents and their Children with an AGE_RANGE greater than 10.

The first approach is 

select /* ANSI 1 */ pr.*, cd.*
from par pr left outer join cld cd on 
    (pr.id = cd.p_id and age_range > 10)
;

ID	P_NAME	ID_1	P_ID	C_NAME	AGE_RANGE
1	P_one
2	P_two
3	P_three
4	P_four	4	4	4_20	20

That was the expected output. 

But the filter age_range > 10 in the WHERE part of the SQL changes the result: 

select /* ANSI 2 */  pr.*, cd.*
from par pr left outer join cld cd on pr.id = cd.p_id 
where age_range > 10
;  

ID	P_NAME	ID_1	P_ID	C_NAME	AGE_RANGE
4	P_four	4	4	4_20	20

The difference is obvious, the filter in the WHERE part made all parents without children disappear. 
A small clue can be found in the explain plans. 

The Plan for ANSI 1

Plan hash value: 2247011116
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 344 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 4 | 344 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| PAR | 4 | 120 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| CLD | 1 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PR"."ID"="CD"."P_ID"(+))
3 - filter("AGE_RANGE"(+)>10)

The Plan for ANSI 2

Plan hash value: 3984166157
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 86 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 86 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | CLD | 1 | 56 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PAR_PK | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PAR | 1 | 30 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("CD"."AGE_RANGE">10)
4 - access("PR"."ID"="CD"."P_ID")

In ANSI 1 we have a HASH JOIN OUTER and on CLD a filter("AGE_RANGE"(+)>10).  

In ANSI 2 instead there are NESTED LOOPS and a filter("CD"."AGE_RANGE">10). 

Both are correct, but they answer different requirements! 

ANSI 1: 

Give me all Parents; show me their Children also, but only if the Childrens AGE_RANGE is greater than 10. 

 

ANSI 2:

Create a result set with all Parrents and their children if any exist; then filter this resultset for all rows which match AGE_RANGE is greater than 10. 

To be more explicit, I also show another variation of the ANSI 1 which generates the same result:

select /*+ ANSI 3 */  pr.*, cd.*
from par pr left outer join 
     (select /*+ NO_MERGE */ * from cld where age_range > 10 ) cd 
                                   on pr.id = cd.p_id 
;

 

---

The same can be applied in Oracle notation, and here a little trap exists: 
A "simple" translation of ANSI 1 to Oracle syntax might look like

select /* Oracle 2 */  pr.*, cd.*
from par pr , cld cd
where pr.id = p_id (+)
  and cd.age_range > 10
;

But it is labeled Oracle 2 as it generates the identical result set as ANSI 2! 

For a correct translation of ANSI 1, this query is required: 

select /* Oracle 1 */  pr.*, cd.*
from par pr , cld cd
where pr.id = p_id (+)
  and cd.age_range (+)> 10 
;

So be precise which query you write - it should match the question you ask!

All the tables, inserts, queries and explain plans are visible on livesql; the statements also on my git.