在之前的学习当中，我们查询使用的都是一张表，而在实际的数据分析业务中，经常会使用多张表。将多张表连接起来就是多表连接(从一个表扩展为两个表，也可以更多个表)
多表连接查询
当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回指定检索列，这些指定检索列可以出自不同表
相比子查询，性能更好，且可以从多个表中选择结果字段
数据导入
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
补充知识点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)
除了交叉连接，其他连接一般都需要使用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;
