百木园-与人分享，
就是让自己快乐。
当前位置：百木园 > 野生技术 > 正文

SQL 中各种连接 JOIN

2022-12-19 分类：野生技术 阅读(178) 评论(0)

image.png

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

数据模拟

mysql版本

select version();

image.png

sql语句

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


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

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

INSERT INTO test.Table_A (name) VALUES
	 (\'A1\'),
	 (\'A3\'),
	 (\'A4\'),
	 (\'A8\');

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

INSERT INTO test.Table_B (names) VALUES
	 (\'B1\'),
	 (\'B2\'),
	 (\'B3\'),
	 (\'B5\'),
	 (\'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条件连接查询，「 实际只有一张表在自我连接查询」。
  • 结果

image.png

内连接

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

image.pngimage.png

SELECT * from Table_A JOIN Table_B;
SELECT * from Table_A INNER JOIN Table_B;
  • 内连接可以没有连接条件，没有条件之后的查询结果，会保留所有结果（笛卡尔集），与交叉连接差不多。

image.png

等值连接

  • 在连接条件中使用等于号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列
SELECT * from Table_A A JOIN Table_B B ON A.id = B.id;
  • 查询结果，列数是 4 列，两张表的字段直接拼接在一起，重复的字段在后面添加数字序列以做区分

image.png

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

image.png

不等连接

  • 不等连接跟等值连接仅仅是连接条件中使用的运算符不一样，其余一致。不等连接使用的是除等于号运算符以外的其它比较运算符，如>、>=、<=、<、!>、!<和<> 等。
SELECT * from Table_A A JOIN Table_B B ON A.id < B.id;
  • 根据条件，一个个做比较，满足条件的所有结果

image.png

自然连接

  • 在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
SELECT * from Table_A NATURAL JOIN Table_B ; 
SELECT * from Table_A A NATURAL JOIN Table_B B WHERE A.id = B.id;
  • 查询结果，注意是已经删除了重复列，列数只有 3，这也是和等值连接的区别

image.png

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

image.png

交叉连接

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

image.png

SELECT * from Table_A CROSS JOIN Table_B;
  • 因为 A 表数据有 4 条，B 表数据有 5 条，4 x 5 = 20，因此交叉查询结果有 20 条，如下

image.png

外连接

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

左连接（左外连接）

  • 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;

image.png

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

image.png

左表唯一

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

image.png
image.png

右连接（右外连接）

  • 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;

image.png

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

image.png

右表唯一

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

image.png
image.png

多表链接语句语法

 -- 连接两个数据表的用法：
 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版本

image.png

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

image.png
image.png

  • 此查询将返回左表（表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;

image.png
image.png
参考（图片来源）地址

来源：https://www.cnblogs.com/wandaren/p/16992515.html
本站部分图文来源于网络，如有侵权请联系删除。

未经允许不得转载：百木园 » SQL 中各种连接 JOIN
标签：

相关推荐

  • 暂无文章

热门文章