join中同一条件写在on和where的区别

在表连接中，对左表的条件写在on和where里，结果有什么不同？右表又是什么结果？
分别测试下left join和inner join。

一、环境 - Oracle

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
sample data列一下初代复联。 Love U 3000

-- 命令行登录oracle： sqlplus username/psw@oracle_name

SQL> Set linesize 300; -- 每行展示300个字符
SQL> set pagesize 30; -- 每页展示30行

-- sample data

SQL> select * from avenger t;

HERO                 GENDER
-------------------- -------
iron_man             m
captain_america      m
hulk                 m
thor                 m
hawk_eye             m
black_widow          f


SQL> select * from movie t;

HERO                  MOVIE_CNT
-------------------- ----------
iron_man                      3
captain_america               3
hulk                          2
thor                          3
hawk_eye                      0
black_widow                   0

二、left join

avenger表作为左表，用hero关联，无其他条件。

-- left join

SQL> select * from
  2  (select * from avenger) t1
  3  left join
  4  (select * from movie) t2
  5  on t1.hero = t2.hero;

HERO                 GENDER  HERO                  MOVIE_CNT
-------------------- ------- -------------------- ----------
iron_man             m       iron_man                      3
captain_america      m       captain_america               3
hulk                 m       hulk                          2
thor                 m       thor                          3
hawk_eye             m       hawk_eye                      0
black_widow          f       black_widow                   0

1. left join，左表加条件，写在on里

-- left join，左表加条件，写在on里

SQL> select * from
  2  (select * from avenger) t1
  3  left join
  4  (select * from movie) t2
  5  on t1.hero = t2.hero
  6  and t1.gender = 'f';

HERO                 GENDER     HERO                  MOVIE_CNT
-------------------- ---------- -------------------- ----------
black_widow          f          black_widow                   0
iron_man             m
captain_america      m
thor                 m
hulk                 m
hawk_eye             m

宸查€夋嫨6琛屻€


鎵ц璁″垝
----------------------------------------------------------
Plan hash value: 2546118694

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     6 |   834 |    10   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |         |     6 |   834 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| AVENGER |     6 |   504 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| MOVIE   |     6 |   330 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("AVENGER"."HERO"="MOVIE"."HERO"(+) AND
              "AVENGER"."GENDER"=CASE  WHEN ("MOVIE"."HERO"(+) IS NOT NULL) THEN 'f'
              ELSE 'f' END )

此时gender='f’是作为关联的条件，而不是左表的筛选条件。
因此只关联了black_widow，其他都是null。

2. left join，左表加条件，写在where里

-- left join，左表加条件，写在where里

SQL> select * from
  2  (select * from avenger) t1
  3  left join
  4  (select * from movie) t2
  5  on t1.hero = t2.hero
  6  where t1.gender