SQL Server中 JOIN的分类和用法

JOIN和WHERE一样都可以实现表与表之间的连接，所不同的是JOIN是显式连接，而WHERE则是隐式连接。不过，使用JOIN的显式连接方式更能体现数据库的语言规范，并且也易于检查语句中的连接条件是否正确。SQLServer本身，比如在视图中，也是使用JOIN而不是WHERE。

首先建立两张表，并插入必要的测试数据：

create table emp

(

 ID int Identity(1,1),

 Name Varchar(15),

 DepID int

 )

 --set Identity_insert emp on

 create table dep

(

 DepID int Identity(1,1),

 DepName Varchar(15),

 )

  --set Identity_insert dep on

 GO

Insert Into emp   SELECT 'Jim',1  UNION ALL   SELECT 'John',1  UNION ALL   SELECT 'Rose',2  UNION ALL  SELECT 'Allen',4

Insert Into dep   SELECT 'hr'  UNION ALL  SELECT 'marketing'  UNION ALL   SELECT 'admin'

GO

 

在SQLServer中，JOIN可以分成OUTER JOIN和INNER  JOIN

1． INNER JOIN

INNER JOIN 等价于 JOIN，是常用的连接。采用INNER   JOIN会返回两个表中所有满足连接条件的结果集，而不满足条件的结果集将不会出现在返回结果中。其实  INNERJOIN的功能类似于WHERE，下面是两个分别使用两者的例子，所得到的结果集是一样的。

SELECT * from emp,dep where emp.DepID=dep.DepID

ID    Name    DepID   DepID   DepName

1      Jim         1              1              hr

2      John      1              1              hr

3      Rose      2              2              marketing

 

SELECT * from emp inner join dep on emp.DepID=dep.DepID

ID    Name    DepID   DepID   DepName

1      Jim         1              1              hr

2      John      1              1              hr

3      Rose      2              2              marketing

2． OUTER JOIN

OUTER  JOIN分成FULL   OUTER JOIN，LEFT  OUTER  JOIN（等价于LEFT   JOIN）和RIGHT  OUTER  JOIN（RIGHT  JOIN）三类。

 

a.      LEFT   JOIN   返回左表中的所有记录和右表中满足连接条件的记录，对于左表中有的并且右表中又没有与之对应的满足连接条件的记录，以null填充右表字段。

SELECT * from emp left join dep onemp.DepID=dep.DepID

ID            Name    DepID   DepID   DepName

1              Jim         1              1              hr

2              John      1              1              hr

3              Rose      2              2              marketing

4              Allen      4              NULL     NULL

简单的说就是：左边的全都有，右边的该有的就有，不该有的填null。

 

b.     RIGHT  JOIN    返回右表中的所有记录和左表中满足连接条件的记录，对于右表中有的并且左表中又没有与之对应的满足连接条件的记录，以null填充左表字段。

       SELECT * from emp right joindep on emp.DepID=dep.DepID

       ID             Name    DepID   DepID   DepName

       1               Jim         1              1                             hr

       2               John      1              1                             hr

       3               Rose      2              2                             marketing

        NULL     NULL     NULL     3                             admin     

      简单的说就是：右边的全都有，左边的该有的就有，不该有的填null。

 

c.      FULL   OUTER  JOIN   返回左表和右表中的所有记录，对于左表中有的并且右表中又没有与之对应的满足连接条件的记录，以及对于右表中有的并且左表中又没有与之对应的满足连接条件的记录，以null填充相应字段。

SELECT * from emp full outer join dep onemp.DepID=dep.DepID

ID            Name    DepID   DepID   DepName

1              Jim         1              1              hr

2              John      1              1              hr

3              Rose      2              2              marketing

4              Allen      4              NULL     NULL

NULL     NULL     NULL     3              admin

简单的说就是：左边右边的全都有，不该有的填null。

 

3． CROSS  JOIN

CROSS  JOIN  返回左表和右表所有记录的笛卡儿积，也就是对两表中的记录进行组合，例子如下：

SELECT *   from  emp  CROSS  JOIN   dep

ID    Name    DepID   DepID   DepName

1      Jim         1              1              hr

2      John      1              1              hr

3      Rose      2              1              hr

4      Allen      4              1              hr

5      Vincent                NULL     1              hr

6      Lily          NULL     1              hr

1      Jim         1              2              marketing

2      John      1              2              marketing

3      Rose      2              2              marketing

4      Allen      4              2              marketing

5      Vincent                NULL     2              marketing

6      Lily          NULL     2              marketing

1      Jim         1              3              admin

2      John      1              3              admin

3      Rose      2              3              admin

4      Allen      4              3              admin

5      Vincent                NULL     3              admin

6      Lily          NULL     3              admin

 

4． NATURAL   JOIN

NATURAL  JOIN就是自然连接，其功能INNER   JOIN相同，但在结果集中只会出现一列来代表连接条件中的两个连接兼容列。但NATURAL JOIN是ORALE的用法，SQL   Server里面不支持NATURAL  JOIN。

 

5． 在JOIN中使用WHERE

 

SELECT * FROM emp LEFT JOIN dep ON emp.DepID=dep.DepID WHERE emp.DepID =1

ID    Name    DepID   DepID   DepName

1      Jim         1              1              hr

2      John      1              1              hr

 

注意，上面的查询结果和下面的查询结果是完全不同的，虽然其连接条件本身是相同的。

SELECT * FROM emp LEFT JOIN dep ON emp.DepID=dep.DepID AND emp.DepID=1

ID    Name    DepID   DepID   DepName

1      Jim         1              1              hr

2      John      1              1              hr

3      Rose      2              NULL     NULL

4      Allen      4              NULL     NULL

 

6． 在JOIN中连接多个数据表

INNER JOIN 连接两个数据表的用法：

SELECT * FROM 表1INNER JOIN 表2ON 表1.字段号=表2.字段号

 

INNER JOIN 连接三个数据表的用法：

SELECT * FROM (表1INNER JOIN 表2ON 表1.字段号=表2.字段号)INNER JOIN 表3ON 表1.字段号=表3.字段号

 

INNER JOIN 连接四个数据表的用法：

SELECT * FROM ((表1INNER JOIN 表2ON 表1.字段号=表2.字段号)INNER JOIN 表3ON 表1.字段号=表3.字段号)INNER JOIN 表4ON Member.字段号=表4.字段号

 

以此类推。