内连接、外连接[左连接、右连接、全连接]、交叉连接、自连接

内连接、外连接[左连接、右连接、全连接]、交叉连接、自连接

　　记得有一次面试官问我sql有哪些连接，我比较常用左连接，内连接用的很少。只记得这两种连接。

其实那时侯就在实际运用中，写过一些业务逻辑较复杂的存储过程了。但是，毕竟是在被人刷掉的面试中，难免会有些介怀。

下面我们创建两个表做连接结果测试，来说明各种连接的特性！[本篇内容只考虑等值连接]

连接操作的共性：第一步均为将所有参与操作的表进行了一个笛卡尔积，然后才依据各连接条件进行记录筛选。

CREATE TABLE table_1

(

　　id INT IDENTITY(1,1) PRIMARY KEY,

　　name VARCHAR(50),

　　classid INT

)
GO

INSERT INTO table_1(name,classid) SELECT '苹果',1
UNION ALL SELECT '香蕉',1
UNION ALL SELECT '草莓',1
UNION ALL SELECT '西瓜',1
UNION ALL SELECT '樱桃',1
UNION ALL SELECT '荔枝',1
UNION ALL SELECT '芒果',1
UNION ALL SELECT '木瓜',1 --table_2没有此项
UNION ALL SELECT '香梨',1 --table_2没有此项
GO

CREATE TABLE table_2
(

　　id INT IDENTITY(1,1) PRIMARY KEY,

　　name VARCHAR(50),

　　classid INT

)
GO

INSERT INTO table_2(name,classid) SELECT '苹果',1
UNION ALL SELECT '香蕉',1
UNION ALL SELECT '草莓',1
UNION ALL SELECT '西瓜',1
UNION ALL SELECT '樱桃',1
UNION ALL SELECT '荔枝',1
UNION ALL SELECT '芒果',1
UNION ALL SELECT '龙眼',1 --table_1没有此项
UNION ALL SELECT '芭乐',1 --table_1没有此项
UNION ALL SELECT '榴莲',1 --table_1没有此项

GO

复制代码

内连接

内连接有三种写法。

SELECT a.*,b.* FROM table_1 a,table_2 b WHERE a.[name]=b.[name] --写法一

 SELECT a.*,b.* FROM table_1 a JOIN table_2 b ON a.[name]=b.[name] --写法二

SELECT a.*,b.* FROM table_1 a INNER JOIN table_2 b ON a.[name]=b.[name] --写法三

复制代码

内连接是在每个表中找出符合条件的共有记录。以下是内连接找出的记录集，没有在结果集中发现各表私有记录集。

1 苹果 1 1 苹果 1
2 香蕉 1 2 香蕉 1
3 草莓 1 3 草莓 1
5 西瓜 1 5 西瓜 1
6 樱桃 1 6 樱桃 1
9 荔枝 1 9 荔枝 1
10 芒果 1 10 芒果 1

外连接

外连接有三种方式  左连接、 右连接、 全连接
左连接 右连接

SELECT a.*,b.* FROM table_1 a LEFT JOIN table_2 b ON a.[name]=b.[name] --左连接写法一
SELECT a.*,b.* FROM table_1 a LEFT OUTER JOIN table_2 b ON a.[name]=b.[name] --左连接写法二

SELECT a.*,b.* FROM table_1 a RIGHT JOIN table_2 b ON a.[name]=b.[name] --右连接写法一
SELECT a.*,b.* FROM table_1 a RIGHT OUTER JOIN table_2 b ON a.[name]=b.[name] --右连接写法二

复制代码

左连接是根据最左表的记录，在被连接右表中找出符合条件的记录与之匹配，找不到与左表匹配的，用null填充。以下是左连接结果集。

1 苹果 1 1 苹果 1
2 香蕉 1 2 香蕉 1
3 草莓 1 3 草莓 1
5 西瓜 1 5 西瓜 1
6 樱桃 1 6 樱桃 1
9 荔枝 1 9 荔枝 1
10 芒果 1 10 芒果 1
12 木瓜 1 NULL NULL NULL
13 香梨 1 NULL NULL NULL

右连接则是根据最右表的记录，在被连接的左表中找出符合条件的记录与之匹配，找不到匹配的，用null填充。以下是右连接结果集。

1 苹果 1 1 苹果 1
2 香蕉 1 2 香蕉 1
3 草莓 1 3 草莓 1
5 西瓜 1 5 西瓜 1
6 樱桃 1 6 樱桃 1
9 荔枝 1 9 荔枝 1
10 芒果 1 10 芒果 1
NULL NULL NULL 12 龙眼 1
NULL NULL NULL 13 芭乐 1
NULL NULL NULL 14 榴莲 1

全连接

SELECT * FROM table_1 a RIGHT JOIN table_2 b ON a.[name]=b.[name] --全连接写法一
SELECT a.*,b.* FROM table_1 a RIGHT OUTER JOIN table_2 b ON a.[name]=b.[name] --全连接写法二

复制代码

返回符合条件的所有表的记录，没有与之匹配的，用null填充。以下是结果集。

1 苹果 1 1 苹果 1
2 香蕉 1 2 香蕉 1
3 草莓 1 3 草莓 1
5 西瓜 1 5 西瓜 1
6 樱桃 1 6 樱桃 1
9 荔枝 1 9 荔枝 1
10 芒果 1 10 芒果 1
NULL NULL NULL 12 龙眼 1
NULL NULL NULL 13 芭乐 1
NULL NULL NULL 14 榴莲 1
12 木瓜 1 NULL NULL NULL
13 香梨 1 NULL NULL NULL

交叉连接

SELECT a.*,b.* FROM table_1 a CROSS JOIN table_2 b --交叉连接

SELECT a.*,b.* FROM table_1 a,table_2 b --内连接

--因为都是用笛卡尔积运算，所以结果一致。

复制代码

在这个笛卡尔积中，结果集的记录数将等于第一个表的记录数乘以第二个表中的记录数。

本例table_1有9条记录，table_2有10条记录，结果集将产生9*10=90条结果集，由于数据量过多，在此不列出。

自连接

顾名思义，就是连接的表都是同一个表。同样可以有内连接、外连接各种组合方式，按实际应用去组合。

SELECT a.*,b.* FROM table_1 a,table_1 b WHERE a.[name]=b.[name] --连接的两表是同一个表，别称不一样


SELECT a.*,b.* FROM table_1 a LEFT JOIN table_1 b ON a.[name]=b.[name] --左连接写法

--......

复制代码

结果集的产生和各种连接的原理一致。以下是内连接结果集。

1 苹果 1 1 苹果 1
2 香蕉 1 2 香蕉 1
3 草莓 1 3 草莓 1
5 西瓜 1 5 西瓜 1
6 樱桃 1 6 樱桃 1
9 荔枝 1 9 荔枝 1
10 芒果 1 10 芒果 1
12 木瓜 1 12 木瓜 1
13 香梨 1 13 香梨 1