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…），并且把连接条件和过滤条件严格分开来。