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.
