SQL> SQL> -- You can use outer joins to give you a lot of different results. SQL> -- The question is, will you be able to interpret the results. SQL> -- it is better to come up with question first and then try to find a solution for it, SQL> -- not the other way around. SQL> SQL> select * from t1_a; C1 C2 ---------- -- 1 xx 2 xx 3 xx 4 xx 5 xx 5 rows selected. SQL> select * from t2_a; C1 C2 ---------- -- 3 yy 4 ww 5 pp 6 oo 4 rows selected. SQL> select * from t3_a; C1 C2 ---------- -- 5 00 6 11 7 22 8 33 4 rows selected. SQL> SQL> select t1.*, t2.* from t1_a t1 2 left outer join t2_a t2 on 3 t1.c1=t2.c1; C1 C2 C1 C2 ---------- -- ---------- -- 3 xx 3 yy 4 xx 4 ww 5 xx 5 pp 1 xx 2 xx 5 rows selected. SQL> SQL> select t1.*, t2.* from t1_a t1 2 left outer join t2_a t2 on 3 t1.c1=t2.c1 4 and t2.c2='yy'; C1 C2 C1 C2 ---------- -- ---------- -- 3 xx 3 yy 5 xx 4 xx 1 xx 2 xx 5 rows selected. SQL> SQL> -- Same as above SQL> with t2 as (select * from t2_a where c2='yy') 2 select t1.*, t2.* from t1_a t1 3 left outer join t2 on 4 t1.c1=t2.c1; C1 C2 C1 C2 ---------- -- ---------- -- 3 xx 3 yy 5 xx 4 xx 1 xx 2 xx 5 rows selected. SQL> SQL> -- "Where" applied after outer join SQL> select t1.*, t2.* from t1_a t1 2 left outer join t2_a t2 on 3 t1.c1=t2.c1 4 where t2.c2='yy'; C1 C2 C1 C2 ---------- -- ---------- -- 3 xx 3 yy 1 row selected. SQL> SQL> -- syntax error here SQL> select t1.*, t2.* from t1_a t1 2 where t1.c1>1 3 left outer join t2_a t2 on 4 t1.c1=t2.c1 5 and t2.c2='yy'; left outer join t2_a t2 on * ERROR at line 3: ORA-00933: SQL command not properly ended SQL> SQL> -- Outer join to more than 1 table SQL> select t1.*, t2.*,t3.* from t1_a t1 2 left outer join t2_a t2 on 3 t1.c1=t2.c1 4 and t2.c2='yy' 5 left outer join t3_a t3 on 6 t1.c1=t3.c1; C1 C2 C1 C2 C1 C2 ---------- -- ---------- -- ---------- -- 5 xx 5 00 4 xx 3 xx 3 yy 1 xx 2 xx 5 rows selected. SQL> SQL> select t1.*, t2.*,t3.* from t1_a t1 2 left outer join t2_a t2 on 3 t1.c1=t2.c1 4 and t2.c2='yy' 5 left outer join t3_a t3 on 6 t1.c1=t3.c1 and t3.c2='11'; C1 C2 C1 C2 C1 C2 ---------- -- ---------- -- ---------- -- 4 xx 5 xx 3 xx 3 yy 1 xx 2 xx 5 rows selected. SQL> SQL> select t1.*, t2.*,t3.* from t1_a t1 2 left outer join t2_a t2 on 3 t1.c1=t2.c1 4 and t2.c2='yy' 5 left outer join t3_a t3 on 6 t1.c1=t3.c1 and t3.c2='11' 7 where t1.c1>1; C1 C2 C1 C2 C1 C2 ---------- -- ---------- -- ---------- -- 4 xx 5 xx 3 xx 3 yy 2 xx 4 rows selected. SQL> SQL> -- Think nested loop, for example... T1 in the outer, t2 in the inner SQL> -- When the combination of t1.*, t2.* satifies the condition(t1.c1>1) (4*1) , list both SQL> -- When the combination fails the check, (1 from t1), we list only the "rows" from t1 SQL> -- no rows from from t2 will be considered. SQL> -- 4 + 1 = 5 SQL> select t1.*, t2.* from t1_a t1 2 left outer join t2_a t2 on 3 t1.c1>1 and t2.c2='yy'; C1 C2 C1 C2 ---------- -- ---------- -- 1 xx 2 xx 3 yy 3 xx 3 yy 4 xx 3 yy 5 xx 3 yy 5 rows selected. SQL> SQL> -- Combination of t1.*, t2.* satifies the condition(t1.c1>1) (4*4) SQL> -- 1 row from t1 failed the check. SQL> -- 16+1=17 SQL> select t1.*, t2.* from t1_a t1 2 left outer join t2_a t2 on 3 t1.c1>1; C1 C2 C1 C2 ---------- -- ---------- -- 1 xx 2 xx 3 yy 2 xx 4 ww 2 xx 5 pp 2 xx 6 oo 3 xx 3 yy 3 xx 4 ww 3 xx 5 pp 3 xx 6 oo 4 xx 3 yy 4 xx 4 ww C1 C2 C1 C2 ---------- -- ---------- -- 4 xx 5 pp 4 xx 6 oo 5 xx 3 yy 5 xx 4 ww 5 xx 5 pp 5 xx 6 oo 17 rows selected. SQL> SQL> -- Combination of t1.*, t2.* satifies the condition(t2.c2='yy') (5*1) SQL> -- No row from t1 fails to find a row in t2 that meet the condition. SQL> -- 5+0=5 SQL> select t1.*, t2.* from t1_a t1 2 left outer join t2_a t2 on 3 t2.c2='yy'; C1 C2 C1 C2 ---------- -- ---------- -- 1 xx 3 yy 2 xx 3 yy 3 xx 3 yy 4 xx 3 yy 5 xx 3 yy 5 rows selected. SQL> SQL> spool off