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

1 - 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.

2 - Creation of the example schema

2.1 - The inner table

CREATE TABLE inner_table AS
SELECT
object_name
FROM all_objects
WHERE
object_type = 'TABLE' AND
rownum < 5

2.2 - 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

3 - Result

3.1 - 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.

3.2 - 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.

db/oracle/filter_after_outer_join.txt ยท Last modified: 2017/09/13 16:15 by gerardnico