sql JOIN ON VS WHERE

问题
现有表EMP和DEPT如下：

EMP 
EMPID  EMPNAME DEPTID
1   张三     1
2   李四     2
3   王五     2
4   赵六     2
5   孙七     3


DEPT 
DEPTID  DEPTNAME
1     开发
2     测试
3     需求
4     销售


现在要看开发部门的人员情况。

这个问题只需以EMP.DEPTID等于DEPT.DEPTID为连接条件来连接两张表。一般来说，跨表查询有两种实现方法：ANSI SQL和dialect SQL



方法1

ANSI SQL，使用JOIN…ON…子句。SQL statement如下：

SELECT EMP.* FROM EMP

JOIN DEPT

ON EMP.DEPTID = DEPT.DEPTID

WHERE DEPT.DEPTID = 1



方法2

Dialect SQL，把连接条件和过滤条件都放在WHERE子句里。SQL statement如下：

SELECT EMP.* FROM EMP, DEPT

WHERE EMP.DEPTID = DEPT.DEPTID AND DEPT.DEPTID = 1



这两种方法本身都没有问题。观察这两个SQL语句可见，二者的区别在于前者把连接条件和过滤条件分开了，而后者没有分开。那么问题就来了，连接条件和过滤条件需要分开吗？在ANSI写法下，如果不分开会怎么样？例如下面的SQL statement：



方法3

SELECT EMP.* FROM EMP

JOIN DEPT

ON EMP.DEPTID = DEPT.DEPTID AND DEPT.DEPTID = 1



虽然“DEPT.DEPTID = 1”并不是连接条件，但是这里把它放到ON子句里，也能得到正确的结果。那么WHERE和ON到底有何不同呢？

分析
我上网查了一些资料，自己也做了一些试验。目前得到的结论为，对于内连接（inner join），二者并无不同（至少从结果上看，至于效率就不清楚了）。所以上面的例子中，这三种写法都OK。当然，把过滤条件放在ON子句里，可读性会差很多。

对于外连接，二者还是确有不同的。比如，我要得到某个部门（如开发）的人数，由于有的部门可能没有员工，所以这里必须用外连接。SQL statement如下：



方法一

SELECT DEPT.DEPTID, DEPT.DEPTNAME, COUNT(EMP.EMPID) AS EMPNUMBER

FROM DEPT

LEFT JOIN EMP

ON EMP.DEPTID=DEPT.DEPTID

WHERE DEPTNAME = '开发'

GROUP BY DEPT.DEPTID, DEPT.DEPTNAME



结果如下：

DEPTID  DEPTNAME EMPNUMBER
1       开发       1




这里连接条件和过滤条件就不能混用，如果把过滤条件也放到连接条件里，SQL statement如下：

方法二

SELECT DEPT.DEPTID, DEPT.DEPTNAME, COUNT(EMP.EMPID) AS EMPNUMBER

FROM DEPT

LEFT JOIN EMP

ON EMP.DEPTID=DEPT.DEPTID AND DEPTNAME = '开发'

GROUP BY DEPT.DEPTID, DEPT.DEPTNAME



方法二的实际结果跟预期结果有所不同，如下：

DEPTID  DEPTNAME  EMPNUMBER
1       开发        1
2       测试        0
3       需求        0
4       销售        0




要了解为何两个SQL语句的结果不同，就要明白SQL执行的顺序。如下：

1)       用笛卡尔乘积连接两张表；

2)       用ON条件过滤结果集；

3)       LEFT、RIGHT、FULL条件起作用；

4)       WHERE条件过滤结果集；

5)       GROUP BY起作用。

下面分别列出两种方法中每一步执行的结果。

方法一
1)    用笛卡尔乘积连接两张表；

DEPTID  DEPTNAME  EMPID  EMPNAME  DEPTID
1          开发    1      张三    1
1          开发    2      李四    2
1          开发    3      王五    2
1          开发    4      赵六    2
1          开发    5      孙七    3
2          测试    1      张三    1
2          测试    2      李四    2
2          测试    3      王五    2
2          测试    4      赵六    2
2          测试    5      孙七    3
3          需求    1      张三    1
3          需求    2      李四    2
3          需求    3      王五    2
3          需求    4      赵六    2
3          需求    5      孙七    3
4          销售    1      张三    1
4          销售    2      李四    2
4          销售    3      王五    2
4          销售    4      赵六    2
4          销售    5      孙七    3

2)    用ON条件过滤结果集；

DEPTID    DEPTNAME   EMPID  EMPNAME  DEPTID
1            开发    1        张三    1
2            测试    2        李四    2
3            需求    5        孙七    3

3)    LEFT、RIGHT、FULL条件起作用；

DEPTID    DEPTNAME    EMPID    EMPNAME    DEPTID
1           开发       1         张三    1
2           测试       2         李四    2
3           需求       5         孙七    3
4           销售      NULL       NULL    NULL

4)    WHERE条件过滤结果集；

DEPTID    DEPTNAME  EMPID    EMPNAME    DEPTID
1            开发    1         张三    1

5)    GROUP BY起作用。

DEPTID    DEPTNAME    EMPNUMBER
1          开发         1

方法二
1)    用笛卡尔乘积连接两张表；同方法一
2)    用ON条件过滤结果集；

DEPTID  DEPTNAME  EMPID  EMPNAME  DEPTID
1          开发    1      张三    1

3)    LEFT、RIGHT、FULL条件起作用；

DEPTID  DEPTNAME  EMPID  EMPNAME  DEPTID
1         开发    1       张三    1
2         测试    NULL    NULL    NULL
3         需求    NULL    NULL    NULL
4         销售    NULL    NULL    NULL

4)    WHERE条件过滤结果集；无WHERE过滤条件
5)    GROUP BY起作用。

DEPTID  DEPTNAME  EMPNUMBER
1          开发    1
2          测试    0
3          需求    0
4          销售    0

结论
对于外连接，一定注意不要把连接条件和过滤条件混淆了。其实，即使对于内连接，把连接条件和过滤条件分开来，对于可读性而言也很有好处。总而言之，推荐大家在跨表查询时，使用标准SQL写法（JOIN…ON…），并且把连接条件和过滤条件严格分开来。