外连接 ON 条件的三个作用

SELECT *
  FROM a LEFT JOIN b ON a.id=b.id
    AND a.id=1 AND b.id=2

这里 ON 中有三个条件，其意义分别为：

• a.id=b.id 两个表的连接条件，只有 a.id 与 b.id 相同的记录才进行匹配。
• a.id=1 符合这个条件的记录被另一个表匹配。
• b.id=2 符合这个条件的记录去匹配另一个表。

可以看到 a.id=b.id 指定如何匹配；a.id=1 表示谁被匹配（不表示最终结果的筛选条件）；b.id=2 表示用谁去匹配（不表示最终结果的筛选条件）。

实例

表 a：

id    v
----
1     a1
2     a2
3     a3
4     a4
5     a5

表 b：

id    v
----
1     b1
2     b2
3     b3
4     b4
5     b5

SQL 语句：

SELECT *
  FROM a LEFT JOIN b ON a.id=b.id
    AND a.id=1 AND b.id=2

结果：

1 a1 NULL NULL
2 a2 NULL NULL
3 a3 NULL NULL
4 a4 NULL NULL
5 a5 NULL NULL 

为什么会有这样的结果呢，据条件可知：a 表只有第一条记录被匹配，后面的记录都不被匹配；b 表只拿第二条记录去匹配 a 表的第二条记录，可是 a 表的第二条记录不参加匹配，所以 b 表结果全部为 NULL。

SQL 语句：

SELECT *
  FROM a LEFT JOIN b ON a.id=b.id
    AND a.id>1 AND b.id=2

结果：

1 a1 NULL NULL
2 a2 2    b2
3 a3 NULL NULL
4 a4 NULL NULL
5 a5 NULL NULL 

RIGHT JOIN

RIGHT JOIN 和 LEFT JOIN 类似，只是颠倒一下“主表”和“辅表”，如下 SQL 语句

SELECT *
  FROM a RIGHT JOIN b ON a.id=b.id
    AND a.id=1 AND b.id=2

a.id=1 表示用谁去匹配（不表示最终结果的筛选条件）；b.id=2 表示谁被匹配（不表示最终结果的筛选条件）。

 

来源：http://www.cftea.com/c/2007/12/UIEINMQSAOVJIRP5.asp

 

所以，对于下例：

WITH TEMP_TAB1 AS
(
  SELECT 1 ID , 'MINGMING' NAME ,0 SCORE FROM DUAL UNION ALL
  SELECT 9 ID , 'MINGMING' NAME ,20 SCORE FROM DUAL UNION ALL
  SELECT 2 ID , 'HUAHUA' NAME , 80 SCORE FROM DUAL UNION ALL
  SELECT 3 ID , 'NIUNIU' NAME, 90 SCORE FROM DUAL UNION ALL
  SELECT 4 ID , 'MEI' NAME ,70 SCORE FROM DUAL UNION ALL
  SELECT 5 ID , 'YOU' NAME, 40 SCORE FROM DUAL
) ,
TEMP_TAB2 AS
(
  SELECT 1 ID , 'REN' NAME , 20 SCORE FROM DUAL UNION ALL
  SELECT 3 ID , 'NIUNIU'  NAME ,30 SCORE FROM DUAL UNION ALL 
  SELECT 4 ID , 'RURU'  NAME ,60 SCORE FROM DUAL UNION ALL
  SELECT 2 ID , 'HONGHONG'  NAME ,40 SCORE FROM DUAL
)
SELECT *
  FROM TEMP_TAB1 A
 left outer join TEMP_TAB2 B
 ON A.ID = B.ID
and A.id>1;

与

WITH TEMP_TAB1 AS
(
  SELECT 1 ID , 'MINGMING' NAME ,0 SCORE FROM DUAL UNION ALL
  SELECT 9 ID , 'MINGMING' NAME ,20 SCORE FROM DUAL UNION ALL
  SELECT 2 ID , 'HUAHUA' NAME , 80 SCORE FROM DUAL UNION ALL
  SELECT 3 ID , 'NIUNIU' NAME, 90 SCORE FROM DUAL UNION ALL
  SELECT 4 ID , 'MEI' NAME ,70 SCORE FROM DUAL UNION ALL
  SELECT 5 ID , 'YOU' NAME, 40 SCORE FROM DUAL
) ,
TEMP_TAB2 AS
(
  SELECT 1 ID , 'REN' NAME , 20 SCORE FROM DUAL UNION ALL
  SELECT 3 ID , 'NIUNIU'  NAME ,30 SCORE FROM DUAL UNION ALL 
  SELECT 4 ID , 'RURU'  NAME ,60 SCORE FROM DUAL UNION ALL
  SELECT 2 ID , 'HONGHONG'  NAME ,40 SCORE FROM DUAL
)
SELECT *
  FROM TEMP_TAB1 A
 left outer join TEMP_TAB2 B
 ON A.ID = B.ID
and B.id>1;

结果是一样的！原来理解是用A的话，那么A为1 的记录就不会出来；用B的话，A的可以出来，只是B的记录为空！这种理解与实际情况又不相符！