Oracle Database - Effect of a filter on a outer join table

About

When a filter is set on a outer join table, the result is the same that if a inner/equality join was used.

The procedure is as follow:

  • The outer join sql return all values and full in the missing values with a NULL value
  • The filter on the outer table suppress all records include the NULL values/outer records.

Creation of the example schema

The inner table

create table inner_table as
select
object_name
from all_objects
where
object_type = 'TABLE' and
rownum < 5

The outer table

create table outer_table as
select
object_name,
case when rownum < 3 then 'True' else 'False' end status
from inner_table
where
rownum < 4

Result

Without the filter on the outer table

[email protected]>select
  2  inner_table.object_name,
  3  status
  4  from inner_table,
  5  outer_table
  6  where
  7  inner_table.object_name = outer_table.object_name (+);

OBJECT_NAME                    STATUS
------------------------------ -----
DUAL                           True
SYSTEM_PRIVILEGE_MAP           True
TABLE_PRIVILEGE_MAP            False
STMT_AUDIT_OPTION_MAP          Null

All objects are present, even the object STMT_AUDIT_OPTION_MAP (which has no parent entry in the outer table) with the value NULL on the STATUS column.

With the filter on the outer table

[email protected]>select
  2  inner_table.object_name,
  3  status
  4  from inner_table,
  5  outer_table
  6  where
  7  inner_table.object_name = outer_table.object_name (+)
  8  and status = 'True';

OBJECT_NAME                    STATUS
------------------------------ -----
DUAL                           True
SYSTEM_PRIVILEGE_MAP           True

We can see than all records without the True value disappeared and also the record with the NULL value of the outer join.


Powered by ComboStrap