SQL 中各种连接 JOIN

多表连接查询中的「多表」，可以是同一张表，自己和自己连接查询。相当于（可以理解为） A 表自己先复制自己后再和自己连接，如此称为「 自连接 」也可以在不同张表中连接查询，可分为「内连接」、「交叉连接」、「外连接」。
内连接根据所使用的比较方式不同，又分为「等值连接」、「自然连接」和「不等连接」三种，连接的结果只列出这些表中与连接条件相匹配的数据行。
与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。外连接分为「左外连接」或「左连接」（ OUTER JOIN 或 LEFT JOIN）、「右外连接」或「右连接」（RIGHT OUTER JOIN 或 RIGHT JOIN）和「全外连接」或「全连接」（FULL OUTER JOIN 或 FULL JOIN）三种。

数据模拟

mysql版本

select version();

sql语句

CREATE TABLE `bus_sche` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `lastStation` varchar(100) NOT NULL,
  `nextStation` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO test.bus_sche (lastStation,nextStation) VALUES
	 ('武汉','南昌'),
	 ('武汉','广西'),
	 ('南昌','厦门'),
	 ('广西','上海');


CREATE TABLE `Table_A` (
  `id` bigint NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO test.Table_A (id,name) VALUES
	 (1,'A1'),
	 (3,'A3'),
	 (4,'A4'),
	 (8,'A8');

CREATE TABLE `Table_B` (
  `id` bigint NOT NULL,
  `names` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO test.Table_B (id,names) VALUES
	 (1,'B1'),
	 (2,'B2'),
	 (3,'B3'),
	 (5,'B5'),
	 (6,'B6');

自连接

一张表中，假如有两个以上的字段，且这些字段有一定的关系，我们又刚好想摸清这些关系字段的数据，就可以在这上面做文章，俗称「自连接」。
一张表 bus_sche，为了简单，表中只有上一站地点和下一站地点及唯一标识

SELECT b.lastStation,b.nextStation,a.lastStation,a.nextStation 
FROM bus_sche a, bus_sche b 
WHERE b.nextStation = a.lastStation;

只在一张表中查询，表 bus_sche 使用了两个别名 bus_sche a, bus_sche b，因此相当于有两张表，用 WHERE条件连接查询，「 实际只有一张表在自我连接查询」。
结果

内连接

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。（内连接查询操作只列出与连接条件匹配的数据行，使用 INNER JOIN 或者直接使用 **JOIN **进行连接）。
两张表的 id ，A中有1、3、4、8，B中有1、2、3、5、6，还有一个字段分别是 name 和names，该字段数据都是按顺序的小写字母，前面再加个 A 或 B 为了方便区分属于哪个表。

SELECT * from Table_A JOIN Table_B;
SELECT * from Table_A INNER JOIN Table_B;

内连接可以没有连接条件，没有条件之后的查询结果，会保留所有结果（笛卡尔集），与交叉连接差不多。

等值连接

在连接条件中使用等于号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列

SELECT * from Table_A A JOIN Table_B B ON A.id = B.id;

查询结果，列数是 4 列，两张表的字段直接拼接在一起，重复的字段在后面添加数字序列以做区分

通俗讲就是根据条件，找到表 A 和表 B 的数据的交集(包含重复列)

不等连接

不等连接跟等值连接仅仅是连接条件中使用的运算符不一样，其余一致。不等连接使用的是除等于号运算符以外的其它比较运算符，如>、>=、<=、<、!>、!<和<> 等。

SELECT * from Table_A A JOIN Table_B B ON A.id < B.id;

根据条件，一个个做比较，满足条件的所有结果

自然连接

在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

SELECT * from Table_A NATURAL JOIN Table_B ; 
SELECT * from Table_A A NATURAL JOIN Table_B B WHERE A.id = B.id;

查询结果，注意是已经删除了重复列，列数只有 3，这也是和等值连接的区别

根据条件，找到表 A 和表 B 的数据的交集，但字段已经去重(不包含重复列)

交叉连接

交叉连接不带 WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积。从一张表中循环取出每一条记录，每条记录都会去另一张表中匹配每一条记录，匹配结果一定保留（因为无条件，如果有条件，则只保留满足条件的结果）。
假设 A 表有 n 条记录，B 表有 m 条记录，则结果为 n * m 条记录。

SELECT * from Table_A CROSS JOIN Table_B;

因为 A 表数据有 4 条，B 表数据有 5 条，4 x 5 = 20，因此交叉查询结果有 20 条，如下

外连接

外连接不只列出与连接条件相匹配的行，而且还加上左表(左外连接时)或右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

左连接（左外连接）

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

SELECT * from Table_A A LEFT JOIN Table_B B ON A.id = B.id;
SELECT * from Table_A A LEFT OUTER JOIN Table_B B ON A.id = B.id;

根据条件，用右表（B）匹配左表（A），能匹配，正确保留，不能匹配其他表的字段都置空 Null。也就是，根据条件找到表 A 和表 B 的数据的交集，再加上左表的数据集

左表唯一

SELECT * from Table_A A LEFT JOIN Table_B B ON A.id = B.id where B.id is null

右连接（右外连接）

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

SELECT * from Table_A A RIGHT JOIN Table_B B ON A.id=B.id;
SELECT * from Table_A A RIGHT OUTER JOIN Table_B B ON A.id=B.id;

根据条件，用左表（A）匹配右表（B），能匹配，正确保留，不能匹配其他表的字段都置空 Null。也就是，根据条件找到表 A 和表 B 的数据的交集，再加上右表的数据集

右表唯一

SELECT * from Table_A A RIGHT OUTER JOIN Table_B B ON A.id=B.id 
where A.id is null

多表链接语句语法

 -- 连接两个数据表的用法：
 FROM t1 INNER JOIN t2 ON t1.id=t2.id
 -- 语法格式可以概括为：
 FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

 -- 连接三个数据表的用法：
 FROM (t1 INNER JOIN t2 ON t1.id=t2.id) INNER JOIN t3 ON t1.name=t3.name
 -- 语法格式可以概括为：
 FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

 -- 连接四个数据表的用法：
 FROM ((t1 INNER JOIN t2 ON t1.id=t2.id) INNER JOIN t3 ON t1.name=t3.name) INNER JOIN t4 ON t1.city=t4.city
 -- 语法格式可以概括为：
 FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON 表1.字段号=表4.字段号

 -- 连接五个数据表的用法：
 FROM (((t1 INNER JOIN t2 ON t1.id=t2.id) INNER JOIN t3 ON t1.name=t3.name) INNER JOIN t4 ON t1.city=t4.city) INNER JOIN t5 ON t1.country=t5.country
 -- 语法格式可以概括为：
 FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON 表1.字段号=表4.字段号) INNER JOIN 表5 ON 表1.字段号=表5.字段号

全链接(全外连接)

根据条件找到表 A 和表 B 的数据的交集，再加上左右表的数据集

SQL Server版本

SELECT * FROM Table_A A FULL OUTER JOIN Table_B B ON A.id = B.id;

此查询将返回左表（表A）中的所有记录和右表（表B）中所有不匹配的记录

SELECT * FROM Table_A A FULL OUTER JOIN Table_B B ON A.id = B.id 
WHERE A.id IS NULL OR B.id IS NULL;

参考（图片来源）地址

posted @ 2022-12-19 16:34 花开重日阅读(436) 评论(0) 编辑收藏举报

刷新页面返回顶部

SQL 中各种连接 JOIN

数据模拟

mysql版本

sql语句

自连接

内连接

等值连接

不等连接

自然连接

交叉连接

外连接

左连接（左外连接）

左表唯一

右连接（右外连接）

右表唯一

多表链接语句语法

全链接(全外连接)

SQL Server版本

公告