Filtering inner (null-supplying) table in outer join

Let us consider below tables to demonstrate filtering inner table in outer join

SQL> select * from t1;

         A          B
---------- ----------
         1         10
         2         20
         3         30

3 rows selected.

SQL> select * from t2;

         B          C
---------- ----------
        20        200
        30        300
        40        400

3 rows selected.

This is regular outer join where t1 is outer (row preserving) table and t2 is inner (null supplying) table.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         2         20         20        200
         3         30         30        300
         1         10

3 rows selected.

Filter predicate in WHERE clause: It filters from result like below, after performing join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b where t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300

1 row selected.

Filter predicate in join condition: It filters rows from inner (null supplying) table t2, before performing join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1 left outer join t2 on t1.b = t2.b and t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300
         2         20
         1         10

3 rows selected.

Here are the equivalent SQLs in Oracle's dialect.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1, t2 where t1.b = t2.b(+) and t2.c=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300

1 row selected.

Observe (+) in filter predicate of t2 below, which actually filters rows before join operation.

SQL> select a, t1.b t1_b, t2.b t2_b, c from t1, t2 where t1.b = t2.b(+) and t2.c(+)=300;

         A       T1_B       T2_B          C
---------- ---------- ---------- ----------
         3         30         30        300
         2         20
         1         10

3 rows selected.