Access before the filter

This post is about what happens during anti-join operation.

Usually, when there is a JOIN+FILTER, the FILTER is made before the JOIN (the access). This is because in this way the CBO can JOIN only a subset of rows of a table with another one.

But there is a case when the FILTER is applied after the ACCESS.

In this post, I show you when it happens.

For this example, I created 2 tables. Here you can find the script for creating and populating them.

These are their contents:

NAMES table

DETAILS table

The ID column is the one I use in order to join the tables.

Well. What happens when I make a join between those two tables?

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  join details d 

    on (n.id = d.id);

Here it is.

Join 01

Now I add a filter to my query

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  join details d 

    on (n.id = d.id)

 where n.name='Diana';

This is the execution plan

Join 02

This is exactly what I aspect. The filter is before of the access (the join).

In "Join 02" picture, the CBO apply the filter to the NAMES table and then make the join. Obviously, the access is made through the "ID" columns of both tables.

For my goal, I change a little the where condition:

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  join details d 

    on (n.id = d.id)

 where d.street is NULL;

This is the execution plan

Join 03

As usual, the filter is applied before the join (the access). The steps are applied in this order:

1. FILTER (id 2 of execution plan) => D.STREET IS NULL
2. ACCESS (id 1 of execution plan) at row 1 => N.ID=D.ID

At this point I re-run all previous statements, using a (LEFT) OUTER JOIN

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  left join details d 

    on (n.id = d.id);

Join 04

The difference between the select, are;

1. Join 01: ACCESS => N.ID=D.ID
2. Join 04: ACCESS => N.ID=D.ID (+)

Using the filter....

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  left join details d 

    on (n.id = d.id)

 where n.name='Diana';

Join 05

... the ACCESS and the FILTER are the same (more or less)

1. Join 02: ACCESS => N.ID=D.ID    / FILTER => N.NAME='DIANA'
2. Join 05: ACCESS => N.ID=D.ID(+) / FILTER => N.NAME='DIANA'

For the last step, I apply the filter:

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  left join details d 

    on (n.id = d.id)

 where d.street is NULL;

Join 06

Here is the point. How you can see in this last case, the access (the join) is before the filter. It is the opposite of the Join 03. This is because now we make an ANTI (OUTER) JOIN. This is the order of operations:

1. ACCESS, (id 2 of execution plan) => N.ID=D.ID(+)
2. FILTER, (id 1 of execution plan) => D.STREET IS NULL