[Mysql] 多表连接查询_mysql多表连接查询

在之前的学习当中，我们查询使用的都是一张表，而在实际的数据分析业务中，经常会使用多张表。将多张表连接起来就是多表连接(从一个表扩展为两个表，也可以更多个表)

多表连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回指定检索列，这些指定检索列可以出自不同表

相比子查询，性能更好，且可以从多个表中选择结果字段

数据导入

DROP TABLE IF EXISTS user_info; 
CREATE TABLE user_info( 
user_id VARCHAR(8), 
age INT, 
gender VARCHAR(8) 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
user_info (user_id,age,gender) 
VALUE ('u001',18,'male') 
,('u002',20,'male') 
,('u003',34,'female') 
,('u004',23,'female') 
,('u005',28,'male'); 
 
DROP TABLE IF EXISTS order_info; 
CREATE TABLE order_info( 
order_id VARCHAR(8), 
order_amount INT, 
user_id VARCHAR(8) 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
order_info (order_id,order_amount,user_id) 
VALUE ('o001',2000,'u001') 
,('o002',1300,'u001') 
,('o003',180,'u003') 
,('o004',340,'u005') 
,('o005',670,'u003')
,('o006',900,'u006');

user_info表(用户信息表)

order_info表(商品信息表)

常用的多表连接类型

mysql支持三种类型的连接查询，分别为:

内连接查询:查询的结果为两个表匹配到的数据

左(外)连接查询:查询的结果为两个表匹配到的数据，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，对于右表中不存在的数据使用NULL填充

右(外)连接查询:查询的结果为两个表匹配到的数据，右表的记录将会全部表示出来，而左表只会显示符合搜索条件的记录，对于左表中不存在的数据使用NULL填充

使用JOIN连接多张表，并且在连接时需要使用ON指出连接条件

如果多表连接LEFT JOIN和RIGHT JOIN没有使用ON指定条件，则会抛出异常，多表连接INNER JOIN没有使用ON，则会出现笛卡尔积

语法结构:

SELECT *
FROM 表1
INNER 或 LEFT 或 RIGHT JOIN 表2
ON 表1.列 = 表2.列;

 在使用ON指定条件时，符号"="只是常用的条件，也可以使用">"、"<"等其他符号

---

案例1:将商品订单信息与用户信息关联起来，得到一张包含更多字段信息的表

-- INNER JOIN
SELECT user_info.*, order_info.* 
FROM user_info 
INNER JOIN order_info 
ON user_info.user_id = order_info.user_id;
 
-- JOIN(单独使用JOIN默认采用内连接INNER JOIN)
SELECT user_info.*, order_info.* 
FROM user_info JOIN order_info 
ON user_info.user_id = order_info.user_id;

结果展示:

上述代码中使用的是INNER JOIN(内连接)

ON之后的条件(ON user_info.user_id = order_info.user_id)指明了在连接两张表时采用的方式:若用户ID一致，则连接在一起，即两张表用户ID一致的行连接为新的行(横向连接)，形成新的表 

上述展示结果图中可以发现没有被匹配上的用户有u002和u004，这是因为商品订单表中没有出现这两位用户，同时商品订单表中的o006没有被匹配上，这是因为该订单的下单用户u006没有出现在用户信息表中

INNER JOIN只返回同时存在于两张表的行数据(保留两张表共有的部分信息)

注意INNER JOIN查询的写法:

1.先确定主表，仍然使用FROM <表1>的语法

2.再确定需要连接的表，使用INNER JOIN <表2>的语法

3.然后确定连接条件，使用ON <条件...>

4.可选：加上WHERE子句、ORDER BY等子句

为了书写方便，多表连接通常会对原始表名起别名

-- INNER JOIN
SELECT a.*, b.* 
FROM user_info AS a 
INNER JOIN order_info AS b 
ON a.user_id = b.user_id;
 
-- JOIN(单独使用JOIN默认采用内连接INNER JOIN)
SELECT a.*, b.* 
FROM user_info AS a JOIN order_info AS b 
ON a.user_id = b.user_id;

上述代码，user_info表取别名为a，order_info表取别名为b，SELECT之后的a.*，b.*代表了a表中的所有字段和b表中的所有字段

案例2:使用左连接将用户信息表与商品订单表进行关联

-- LEFT JOIN是LEFT OUTER JOIN的简写
-- LEFT JOIN
SELECT a.*, b.* 
FROM user_info AS a 
LEFT JOIN order_info AS b 
ON a.user_id = b.user_id;
 
-- LEFT OUTER JOIN
SELECT a.*, b.* 
FROM user_info AS a 
LEFT OUTER JOIN order_info AS b 
ON a.user_id = b.user_id;

结果展示:

从上述结果图可以看出，左表(用户信息表user_info) 的信息全部被保留了，右表(商品订单表order_info)的部分订单信息因没有和左表关联而没有被保留，如订单o006，同时有些用户没有关联订单信息，在订单信息部分显示为空(Null)，因为这些用户没有消费过，即这些用户没有出现在商品订单表中

LEFT JOIN保留左表所有信息，保留右表能和左表关联的部分信息

注意:多表连接查询检索的字段需标记表名

SELECT user_id 
FROM user_info AS a 
LEFT JOIN order_info AS b 
ON a.user_id = b.user_id;

抛出异常:[Err] 1052 - Column 'user_id' in field list is ambiguous

上述代码在多表连接后查询user_id，但是这里的两张表都有user_id字段，所以不知道查询的是哪张表，即字段归属模糊，这就需要给字段加上表名

-- 查询a表的user_id字段
SELECT a.user_id 
FROM user_info AS a 
LEFT JOIN order_info AS b 
ON a.user_id = b.user_id;

结果展示:

案例3:使用右连接将用户信息表与商品订单表进行关联

-- RIGHT JOIN是RIGHT OUTER JOIN的简写
-- RIGHT JOIN
SELECT a.*, b.* 
FROM user_info AS a 
RIGHT JOIN order_info AS b 
ON a.user_id = b.user_id;
 
-- RIGHT OUTER JOIN
SELECT a.*, b.* 
FROM user_info AS a 
RIGHT OUTER JOIN order_info AS b 
ON a.user_id = b.user_id;

结果展示:

从上述结果图可以看出，右表(商品订单表order_info)的信息全部被保留了，左表(用户信息表user_info) 的部分用户信息因没有和右表关联而没有被保留，如用户u002和u004，同时有些订单没有关联用户信息，在用户信息部分显示为空(Null)，这是因为这些订单中的信息不全，即商品订单表中的部分用户信息在用户信息表中对应不到具体用户，例如，订单o006中的用户u006不在用户信息表中

RIGHT JOIN保留右表所有信息，保留左表和右表关联的部分信息(和LEFT JOIN结果相反)

---

常用的多表连接方式为INNER JOIN和LEFT JOIN，对于左右(外)连接，只需要记住LEFT JOIN的用法即可，这是因为RIGHT JOIN和LEFT JOIN可以相互转化

A LEFT JOIN B = B RIGHT JOIN A 

---

补充知识点1

Mysql中的多表连接查询只提供了内连接，左(外)连接与右(外)连接

Mysql目前未实现全外连接的功能

SELECT a.*, b.* 
FROM user_info AS a 
FULL JOIN order_info AS b 
ON a.user_id = b.user_id;

上述结果报错

如果想要实现同样效果，则可以考虑使用如下方法进行替代:

SELECT a.*,b.*
FROM user_info AS a LEFT JOIN order_info AS b 
ON a.user_id = b.user_id 
UNION 
SELECT a.*,b.* 
FROM user_info AS a RIGHT JOIN order_info AS b 
ON a.user_id = b.user_id;

结果展示:

从上述代码可以看出全外连接是通过一个左(外)连接和一个右(外)连接进行UNION实现的 

全外连接保留两张表所有信息

左(外)连接 = 左表全部记录 + 相关联结果
右(外)连接 = 右表全部记录 + 相关联结果

---

全外连接 = 左表全部记录 + 右表全部记录 + 相关联结果

               = 左(外)连接 + 右(外)连接 - 相关联结果(即去重复)

补充知识点2

交叉连接(CROSS JOIN)

详细资料 | CROSS JOIN 交叉连接

除了交叉连接，其他连接一般都需要使用ON指定条件

补充知识点3

INNER JOIN是显式内连接

隐式内连接

内连接还有一种隐式的写法，即不需要显式的指定INNER JOIN关键字，需要注意使用隐式内连接条件的关键字要使用WHERE而不再是ON

数据导入

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`  (
  `sid` int(11) NOT NULL,
  `cid` int(11) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
INSERT INTO `students` VALUES (1, 1, 'Odin');
INSERT INTO `students` VALUES (2, 1, 'Jack');
INSERT INTO `students` VALUES (3, 2, 'Lee');
INSERT INTO `students` VALUES (4, 2, 'Bob');
INSERT INTO `students` VALUES (5, 3, 'Tom');
 
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses`  (
  `cid` int(11) NOT NULL,
  `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
INSERT INTO `courses` VALUES (1, '语文');
INSERT INTO `courses` VALUES (2, '数学');
INSERT INTO `courses` VALUES (3, '英语');

students表

courses表

问题:查询学生报班学习的课程，按sid，name，cname顺序进行输出

SELECT a.sid, a.name, b.cname
FROM students AS a, courses AS b
WHERE a.cid = b.cid;

结果展示:

这两个表用WHERE子句进行连接，WHERE子句指示Mysql匹配students表中的cid和courses表中的cid

缺乏WHERE子句的连接条件

SELECT a.sid, a.name, b.cname
FROM students AS a, courses AS b;

结果展示:

返回的结果为笛卡尔积，即检索出的行的数目是第一张表中的行数乘以第二张表中的行数

笛卡尔积:就是A，B两个集合，取 A，B所有的组合情况，比如A有3条记录，B有6条，查询就会有3*6=18条数据

上述隐式内查询可等同于如下代码:

-- 显式内连接
SELECT a.sid, a.name, b.cname
FROM students AS a
INNER JOIN courses AS b
ON a.cid = b.cid;