表连接异同（等值、无索引）

一、说明：

1、菜鸟水文....高手请指正

2、本文中大批量测试数据均为Sql Data Generator 1(Red Gate) 生成

3、限于篇幅，本文中的表连接均为等值连接。

4、本文中的表均无索引器（被博主删掉了）

5、本文中的表没有建立主外键关系（避免影响测试）

6、本文侧重于比较内连接、外连接、交叉连接的异同

二、名词

1、匹配数据、非匹配数据

是这样的表连接就像是连个表之间的连线（映射），这样的连线（映射）可以是一对多、一对一、多对多的关系，当然也存在左右两个表没有连线的情况。博主（仅仅是我，不要误导大家）把存在连线的数据叫做匹配数据，不存在连线的数据叫做非匹配数据。

2、功用

在相同测试数据，不同表连接查询出的数据相同的情况下称之为表连接功用相同，反之则称之为公用不同。比如内连接和交叉连接功用

相同，左连接和右连接的功用不同（以下文中给出的数据为测试数据）。

三、表连接种类

1、内连接

2、外连接

          1)左链接

          2)右连接

          3)全连接

3、交叉连接

四、视图（SQL Dependency Tracker(Red Gates)）

说明：默认Teacher（教师）表为左表，Coures（课程）表为右表。SQL脚本在文末

五、数据

说明：这些数据是为了比较各种表连接的异同，所插入到数据库中的（非生成）。

六、表连接

1、内连接

 

 

从图中可以看到内连接只是把匹配数据筛选出来了

2、左连接

左连接除了把匹配数据筛选出来之外，还筛选出来了左（Teacher）表中非匹配数据

3、右连接

右连接反之.....

4、全连接

 

全连接是把做链接筛选出来的数据的集合和右连接筛选出来的数据结合做并运算。

因为画的图不形象干脆不画了。有兴趣的园友可以自己试一试。脚本将在文末提供。

5、交叉连接

交叉连接和有意思，它与内连接筛选出来的数据相同，即功用相同。

七、表连接的异同

相同点：

1、不管内连接、外连接还是交叉连接都可能筛选出匹配数据。

2、内连接和交叉连接的功用相同

不同点：

1、外连接和内连接的功用不同

2、外连接和交叉连接的功用不同

3、左连接、右连接、全连接的功用不同

八、性能测试

1、声明

1）对于左右连接这些功用不同的表连接，测试性能没意义。只用内连接和外连接这些功用相同的表连接测试才有意义。所以测试的重点放在内连接和外连接上。

2）每次生成的测试数据均含有非匹配数据。

3）本次所做测试仅限于在我的PC上，仅限于SQL SERVER 2008，仅限于这两张表、仅限于那个时间点。博主想说的是测试的数据的查询时间是相对的，不是绝对的。

2、内连接效率比外连接高？

传说，内连接的效率比外连接的效率高，从本次测试来看这个是实话。

不过如果你所做的软件不会出现大量的数据，内连接还是交叉连接就无所谓了。虽说如此这些数据仍能体现出内连接要比交叉连接效率高。

友情提示：如果面试的时候遇到即可写交叉连接也可以写内连接的题的话，一定要写内连接。博主在四月份北京的面试的时候，对内连接和交叉连接的性能还不太清楚。结果在面试我一家特别中意的公司的时候悲剧了，最后迫于无奈来烟台发展。这些都是博主血和泪的教训啊。

3、适时的使用标量函数。

如果只是为了获得群集的标量的话，博主建议使用DB内置的标量函数

相信博友们也遇到过奇葩同事的Code，把数据从数据库中取出来放在内存（DataSet）之中，然后计算数据的数量的情况吧。抛开数据库查询语句时间放在一边，这本身就是对资源的一种巨大的浪费，而且这种问题比较低级。

4、SELECT * 效率一定低？

对于筛选出出所有数据的情况SELECT *的效率一定低吗？最少我这次测试答案是否定的。

以内连接为例

SELECT * FROM dbo.Teacher T
    INNER JOIN dbo.Course C
　　ON T.T#=C.T#

与

SELECT T.Name,
        T.T#,
        C.C#,
        C.Name,
        C.T# 
    FROM dbo.Teacher T
    INNER JOIN dbo.Course C
    ON T.T#=C.T#

测试数据量和匹配量具达到100W的情况下，二者查询时间均为11秒。假如改变第二个SQL

 

SELECT T.Name,T.T# 
    FROM dbo.Teacher T
    INNER JOIN dbo.Course C
　　ON T.T#=C.T#

 

查询耗时为8秒。

虽说如此，在查询出所有字段的时候我仍推荐写出每个字段名，即这个问题的第二个SQL因为这样更有益于SQL的维护。

5、特殊情况

在特殊情况也许需求之要求你取出左表的匹配数据。例，查询出有课程安排的教师的相关信息。

即：

 

如上图红线所圈的数据。这个时候用连接查询反而效率不高

以内连接为例

SELECT T.Name,T.T# 
    FROM dbo.Teacher T
    INNER JOIN dbo.Course C
　　ON T.T#=C.T#

与

SELECT T#,Name
    FROM dbo.Teacher
    WHERE T# IN 
    (
        SELECT T# 
            FROM dbo.Course
            WHERE dbo.Course.T#=dbo.Teacher.T#
　　)

测试数据量和匹配量具达到100W的情况下，使用内连接查询时间为8秒，而使用IN 查询只用了6秒。

那么使用内置的EXISTS函数是不是效率会更高一些呢？

 

SELECT T#,Name
    FROM dbo.Teacher
    WHERE EXISTS 
    (
        SELECT T# 
            FROM dbo.Course
            WHERE dbo.Course.T#=dbo.Teacher.T#
　　)

 

答案是肯定的，使用EXISTS函数查询时间为5秒。

6、总结

P（*）>P(^*)

在功用相同的条件下

P(EXISTS)>P(IN)>P(内连接)>P（交叉连接）

九、SQL脚本

 

CREATE DATABASE Test
GO
USE Test

CREATE TABLE [Teacher]
(
    [T#] INT PRIMARY KEY,
    [Name] NVARCHAR(50) NOT NULL,
 
);
GO
CREATE TABLE [Course]
(
    [C#] INT PRIMARY KEY,
    [Name] NVARCHAR(50) NOT NULL,
    [T#] INT
);
GO
INSERT dbo.Teacher
        ( T#, Name )
SELECT    1, -- T# - int
          '张三'  -- Name - nvarchar(50)
UNION ALL 
SELECT    2,
          '李四'
UNION ALL 
SELECT    3,
          '王五'
UNION ALL 
SELECT    4,
          '赵六'
          
INSERT dbo.Course
        ( C#, Name, T# )
SELECT    1, -- C# - int
          '高数', -- Name - nvarchar(50)
          1  -- T# - int
UNION ALL
SELECT    2,
          '数据结构',
          2
UNION ALL
SELECT    3,
          '社交礼仪',
          3
UNION ALL
SELECT    4,
          '爱文化',
          NULL