表连接(JOIN)

SQL DB2

了解表连接的人，大概都觉得它很简单。其实简单只是它的外表，如果没有深刻理解Join语句，稍不留神就会有逻辑错误，逻辑错误比语法错误更难发现。

知识点准备

要想正确使用Join语句，有2个知识点是必须掌握的。

第一：SQL语句执行的顺序

FROM  
JOIN ON  
WHERE  
GROUP BY  
HAVING  
SELECT  
ORDER BY  
FETCH FIRST  

第二：ON 和 WHERE 的区别：ON是用来定义连接条件的，WHERE用来过滤结果集

Example 1

我们来看一个例子，请看如下表定义：

    CREATE TABLE EMPLOY  
    (  
        NAME    VARCHAR(10),--姓名  
        DEPTNO    INTEGER--部门编号  
    );  
    INSERT INTO EMPLOY (NAME, DEPTNO) VALUES
        ('张三',10),  
        ('李四',20),  
        ('王五',10),  
        ('赵红',20);  
    CREATE TABLE DEPARTMENT  
    (  
        DEPTNO    INTEGER,--部门编号  
        DEPTNAME    VARCHAR(10)--部门名  
    );  
    INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME) VALUES   
    (10, '市场部'),  
    (20, '技术部');  

我们看一条查询语句：

    SELECT * FROM EMPLOY  E LEFT JOIN DEPARTMENT D   
    ON E.DEPTNO=D.DEPTNO AND D.DEPTNO=40  

如果你认为上面这条SQL语句不会返回任何结果的话，说明你还不清楚ON 和 WHERE的区别。
再次声明： ON是用来定义连接条件的，而不会过滤结果集。

Example 2

我们再看一个例子，假设现在让你查询一下所有的员工的姓名和部门名为市场部的部门，怎么查？如下：

SELECT E.NAME,D.DEPTNAME FROM EMPLOY  E LEFT JOIN DEPARTMENT D ON E.DEPTNO=D.DEPTNO   
WHERE D.DEPTNAME='市场部' 

如果你也写出上面的语句，那么你就错了，正确的写法应该是这样：

--方法1
SELECT E.NAME,D.DEPTNAME FROM EMPLOY  E LEFT JOIN DEPARTMENT D ON E.DEPTNO=D.DEPTNO AND D.DEPTNAME='市场部'  
--方法2
SELECT E.NAME,D.DEPTNAME FROM EMPLOY  E LEFT JOIN   
(  
    SELECT * FROM DEPARTMENT WHERE DEPTNAME='市场部'  
) AS D  
    ON E.DEPTNO=D.DEPTNO  

其他说明

以上只是举了一个简单的例子，其实，使用JOIN的时候，还有好多这样的陷阱，如：在同一个语句中使用Inner Join和Outer Join的时候，应该特别注意。再如：多个表连接的时候，特别要注意连接条件，如：假设A、B、C三个表都有NO字段，以下2个语句可能产生截然不同的结果。

--语句1：  
A LEFT JOIN B ON A.NO=B.NO  
LEFT JOIN C ON A.NO=C.NO  
--语句2：  
A LEFT JOIN B ON A.NO=B.NO  
LEFT JOIN C ON B.NO=C.NO  

综上所述，使用 Join的时候特别容易出错，所以我们鼓励，如果需要多表连接时，尽量不使用JOIN，而使用将连接条件定义在WHERE子句中的查询，如：SELECT * FROM A,B WHERE A.NO=B.NO。但是，使用这种方式连接表时，也不是没有缺点，它很容易产生笛卡儿乘积，从而使结果集倍增，既使你指定了正确的连接条件，如果连接条件不唯一，也会产生局部笛卡儿乘积，如果这时使用分组统计，很可能产生不正确的结果，所以，也必须加倍小心。