SQL——联接分类和本质

目录

1、嵌套循环联接

2、合并联接

3、哈希联接

联接（Join）是将两个表合并为一个表的操作。SOL的联接分为外联接、内联接和交叉联接。本文将主要通过查询执行计划的角度从本质上讲解联接。

1、嵌套循环联接

嵌套循环联接（Nested Loop Join）也称为“嵌套选代”，它将一个联接输入用作外部输入表（显示为图形执行计划中的顶端输入），将另一个联接输入用作内部（底端）输入表。外部循环逐行处理外部输入表。内部循环会针对每个外部行执行，在内部输入表中搜索匹配行。

最简单的情况是，搜索时扫描整个表或索引，这称为“单纯嵌套循环联接”。如果搜索时使用索引，则称为“索引嵌套循环联接”。如果将索引生成为查询计划的一部分（并在查询完成后立即将索引破坏），则称为“临时索引套循环联接”。查询优化器考虑了所有这些不同的情况。

如果外部输入较小而内部输入较大，且预先创建了案引，则使用嵌套循环联接尤其有效，在许多小事务中（如那些只影响较小的一组行的事务），索引嵌套循环联接优于合并联接和哈希联接。但在大型查询中，嵌套循环联接通常不是最佳选择。

在程序中可以理解为嵌套的for循环语句，先是对外部输入表循环找到每行数据，再使用循环对内部输入表的每行数据与外部输入表的数据进行匹配，直到这2个循环都完成。例如创建班级表和学生表，然后对这两个表进行内联接，则系统将采用嵌套循环联接对这两个表进行处理。如下：

IF OBJECT_ID('Class') IS NOT NULL
    DROP TABLE Class;
GO

CREATE TABLE Class
(
    CID INT NOT NULL PRIMARY KEY,
    CName VARCHAR(10) NOT NULL
);
GO

IF OBJECT_ID('Student') IS NOT NULL
    DROP TABLE Student;
GO

CREATE TABLE Student
(
    SID INT IDENTITY PRIMARY KEY,
    CID INT NOT NULL,
    SName VARCHAR(10) NOT NULL,
    CONSTRAINT FK_Student_Class FOREIGN KEY(CID) REFERENCES Class(CID)
);
GO

--插入测试数据
INSERT INTO Class VALUES
(1,'01'),
(2,'02');

INSERT INTO Student(CID,SName) VALUES
(1,'s11'),
(1,'s12'),
(2,'s21'),
(2,'s22'),
(2,'s23');

内联接查询这2个表，同时返回查询的执行计划，查询代码如下：

--内联接
SELECT *
FROM Student AS S
INNER JOIN Class AS C
ON S.CID = C.CID;

 

执行计划：

 

2、合并联接

合并联接（Merge Join）要求两个输入都在合并列上排序，其由联接谓词的等效（ON）子句定义。通常，利用査询优化器扫描索引（如果在适当的一组列上存在索引），或在合并联接的下面放一个排序运算符。在极少数情况下，虽然可能有多个等效子句，但只用其中一些可用的等效子句获得合并列。

由于每个输入都已排序，因此 Merge Join运算符将从每个输入获取一行并将其进行比较。例如，对于内联接操作（ INNER．JON），如果获取的行相等则返回该行。如果行不相等，则抛弃值较小的行并从该输入获得另一行进行比较。这一过程将重复进行，直到处理完所有的行为止。

合并联接操作可以是常规操作，也可以是多对多操作。多对多合并联接使用临时表存储行。如果每个输入中有重复值，则在处理其中一个输入中的每个重复项时，另一个输入必须重绕到重复项的开始位置。

如果存在驻留谓词，则所有满足合并谓词的行都将对该驻留谓词取值，而只返回那些满足该驻留谓词的行。

合并联接本身的速度很快，但如果需要执行排序操作，选择合并联接就会非常费时。然而，如果数据量很大且能够从现有B树索引中获得预排序的所需数据，则合并联接通常是最快的可用联接算法。例如仍然使用前面创建的班级和学生表，由于合并联接在两表数据量并不小而且联接列已排序的情况下发生，所以需要向表中添加大量数据，同时还要为进行联接的列CID排序，具体SQL代码如下：

SET NOCOUNT ON
--创建大量的测试数据
DECLARE @i INT =3
WHILE @i