SQL Server中 JOIN的分类和用法

最新推荐文章于 2024-03-13 16:46:35 发布

Lemon2050

最新推荐文章于 2024-03-13 16:46:35 发布

阅读量1.6k

点赞数 1

分类专栏： SQL Server 自动化测试文章标签： join sql server null sqlserver insert table

本文链接：https://blog.csdn.net/JZF2050/article/details/6775049

版权

自动化测试同时被 2 个专栏收录

25 篇文章 1 订阅

订阅专栏

SQL Server

0 篇文章 0 订阅

订阅专栏

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

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'

在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.字段号

以此类推。