首页 > 数据库 >SQL 中各种连接 JOIN

SQL 中各种连接 JOIN

时间：2022-12-19 16:45:35浏览次数：56  
标签：JOIN 连接 字段 INNER SQL Table id

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
参考（图片来源）地址

标签：JOIN,连接,字段,INNER,SQL,Table,id
From： https://www.cnblogs.com/wandaren/p/16992515.html

相关文章

  • 【mysql-01】开放远程连接权限遇到的问题
    环境电脑本机ip：192.xxx.xxx.xxx虚拟机A的ip：192.168.132.100虚拟机B的ip：192.168.132.101虚拟机A上的mysql用户权限情况：如图，在虚拟机A上的mysql开放了本地电脑ip的......
  • MySQL 8.0 还能为你自动创建主键？！
    MySQL8.0.31版本引入了一个参数​​sql_generate_invisible_primary_key​​​，开启这个参数，会在建表时，检查表中是否有主键，如果没有主键，则会自动创建。该参数非常实用，减少了......
  • 数据库文件导入报错"MySQL server has gone away"
    今天mysql从一个mysql库中导入另一个mariadb库中是总是报：ERROR2006(HY000)atline176infile:'xxx.sql':MySQLserverhasgoneaway但我是在当前服务器上导入的......
  • SQL基础——聚合与排序
    聚合与排序​​前言​​​​思维导图​​​​聚合函数​​​​示例表3-1​​计算表中数据的行数COUNT函数​​​​示例代码3.1计算全部数据的行数​​​​执行结果​​​......
  • mysql查询当天所有数据sql语句
    mysql查询当天的所有信息：select*fromtestwhereyear(regdate)=year(now())andmonth(regdate)=month(now())andday(regdate)=day(now())这个有一些繁琐，还有简单的写......
  • MySQL用户管理
    1.查看用户及用户权限mysql中的用户信息和权限等都存储在一个名为mysql的数据库中。其中主要用到的是user、db、tables_priv、columns_priv、procs_priv这五张表，最重要的......
  • SQL Server数据库 附加数据库时出错。有关详细信息，请单击“消息”列中的超链接。
    SQLServer数据库附加数据库时出错。有关详细信息，请单击“消息”列中的超链接。我们出现这个问题的原因，这是权限的问题，提升一下权限就行了。步骤：1.找到文件鼠标右键单击......
  • docker-compose入门以及部署SpringBoot+Vue+Redis+Mysql(前后端分离项目)以若依前后端
    场景若依前后端分离版手把手教你本地搭建环境并运行项目：https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/108465662上面在搭建起来前后端分离版的项目后。......
  • MySQL笔记(一)之基础知识
    一、​​mysql​​数据类型​​mysql​​支持的所有的数据类型。1.1.字符串类型​​char(1)​​​是固定长度，我们设置长度是多少，只能输入多少。长度可以再​​0-255​​的......
  • MySQL系列之表和数据类型
    MySQL之表和数据类型一.查看数据库版本selectversion();二.数据库操作2.1.创建数据库createdatabaseifnotexiststest_dbdefaultcharsetutf8collateutf8_gener......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99