14｜第十四话：基础篇-MySQL之连接查询

上一篇文章中，我们接触到了用户表和用户收货地址表，分别存储用户基本信息和用户相应的收货地址信息，一个用户可能会设置多个收货地址，显然是一对多的关系。

当我们查询某个用户信息时，只需要查询用户表即可，但是当需要同时查询某个用户以及他所设置的收货地址的信息时，由于一部分信息属于用户表，一部分信息属于用户收货地址表，就会涉及到联表查询。

或许你会想，为啥要增加难度用这么多表来存储呢？用一张表来存储用户信息和收货地址信息不是更方便吗？

确实是可以的，我们可以将两张表的字段合一合，变成一张大的用户信息表，单表的select查询我们已经十分熟悉。

但是，别忘了一个用户可能会设置很多个收货地址信息，也就是说，每当为一个用户新增一个收货地址信息时，都必须把该用户的基本信息再抄一遍，这种用户信息的冗余存储带来以下问题：

浪费存储空间。
当修改某个用户的基本信息时，就需要修改很多地方，容易造成信息的不一致，增大维护的困难。

所以，为了尽可能少的存储冗余信息，一开始我们就将用户信息表和用户收货地址表分开，这两张表通过用户ID这个枢纽进行关联。

一、连接的概念

拆分之后的表的确解决了数据冗余问题，但是查询数据却成了一个问题。到目前为止，我们主要接触的还是单表的查询，那么多张表如何同时去查询呢？连接查询登场！

我们要想用好连接查询，得先理解连接的概念。

连接的本质就是把各个表中的记录都取出来依次匹配的组合加入结果集并返回给用户。

MySQL 的连接查询，通常都是将来自两个或多个表的记录行结合起来，基于这些表之间的共同字段，进行数据的拼接。

连接从大的方面分为：内连接和外连接，他们的区别是：

内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的。
外连接：假设A和B表进行连接，使用外连接的话， AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表。当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

为了方便说明，我们新建两张表，表结构和数据都十分简单：

CREATE TABLE t1 (m1 int, n1 char(1));
CREATE TABLE t2 (m2 int, n2 char(1));
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

二、交叉连接

我们先来看看连接中写法最简单的一种，叫做交叉连接，交叉连接是内连接的一种，当我们执行如下SQL时，会得到这样的结果：

mysql> select * from t1,t2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
|    3 | c    |    2 | b    |
|    1 | a    |    3 | c    |
|    2 | b    |    3 | c    |
|    3 | c    |    3 | c    |
|    1 | a    |    4 | d    |
|    2 | b    |    4 | d    |
|    3 | c    |    4 | d    |
+------+------+------+------+
9 rows in set (0.00 sec)

可以注意到，交叉连接写法十分简单，只要在FROM语句后边跟多个用逗号,隔开的表名就好了，这里得到了9行数据，我们将这样的结果集称之为笛卡尔积。

什么是笛卡尔积？假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。如果A表示某学校学生的集合，B表示该学校所有教师的集合，则A与B的笛卡尔积表示学生选择老师所有可能的情况。

笛卡尔积特点：它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行一一匹配。

因为表t1中有3条记录，表t2中也有3条记录，所以这两个表连接之后的笛卡尔积就有3×3=9行记录。

另外，查询列表处的*代表从FROM语句后列出的表中选取每个列，上边的查询语句其实和下边这几种写法都是等价的：

写法一：SELECT t1.m1, t1.n1, t2.m2, t2.n2 FROM t1, t2;
写法二：SELECT m1, n1, m2, n2 FROM t1, t2;
写法三：SELECT t1.*, t2.* FROM t1, t2;

当连接的两张表中有相同名称的字段时，为了避免二义性，别名就显然尤为重要了！

三、连接过程

我们接着上面所提到的笛卡尔积，容易想到，如果我们乐意，可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产生的笛卡尔积就有100×100×100=1000000行数据！所以在连接的时候过滤掉特定记录组合是有必要的。

我们来看看下面这个语句：

mysql> SELECT * FROM t1, t2 WHERE t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.01 sec)

可以看到，我们的目的是取出t1.m1 = t2.m2的数据，最终只有两条记录满足，其他都不能满足：

具体的SQL执行流程为：

1、首先确定第一个需要查询的表，这个表称之为驱动表。此处假设使用t1作为驱动表，由于没有其他限制条件，那么最终会遍历得到里面的三条数据；
2、从上一步骤中获取到每一条记录，去t2表中进行匹配，因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为被驱动表。由于t1表有三条记录，需要查询三次t2表。步骤也比较容易理解：

a、第一条记录中t1.m1等于1，此时相当于t2表有了一个t2.m2=1的过滤条件，然后在t2表中执行单表查询，发现无️此记录；
b、第二条记录中t1.m1等于2，此时相当于t2表有了一个t2.m2=2的过滤条件，然后在t2表中执行单表查询，发现一条记录；
c、第三条记录中t1.m1等于3，此时相当于t2表有了一个t2.m2=3的过滤条件，然后在t2表中执行单表查询，发现一条记录；

所以最后查出来两条符合条件的记录，并且将两张表的数据组合起来一起返回！从上面的步骤可以看出，这个两表连接查询共需要查询1次t1表，3次t2表。也就是说在两表连接查询中，驱动表只需要查询一次，被驱动表可能会被查询多次。

用SQL表示以上过程：

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2 WHERE t2.m2=1;
Empty set (0.00 sec)

mysql> SELECT * FROM t2 WHERE t2.m2=2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2 WHERE t2.m2=3;
+------+------+
| m2   | n2   |
+------+------+
|    3 | c    |
+------+------+
1 row in set (0.00 sec)

当然了，我们可以组合使用一些过滤条件，比如我们研究下这个查询语句的执行流程：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

大致的执行过程如下：

1、还是假设t1为驱动表，首先到t1表中找满足t1.m1 > 1的记录，符合这个条件的t1表记录有两条，即m1=2和m1=3这两条记录；
2、从上一步骤中获取到每一条记录，去t2表中进行匹配：

a、第一条记录中t1.m1等于2，那么下面针对t2表的过滤条件即：t2.m2 = 2并且t2.n2 < ‘d’，t2表正好有一条符合的记录。
b、第一条记录中t1.m1等于3，那么下面针对t2表的过滤条件即：t2.m2 = 3并且t2.n2 < ‘d’，t2表正好有一条符合的记录。

用SQL表达大概可表示为如下步骤：

mysql> SELECT * FROM t1 WHERE t1.m1 > 1;
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t2 WHERE t2.m2 = 2 AND t2.n2 < 'd';
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2 WHERE t2.m2 = 3 AND t2.n2 < 'd';
+------+------+
| m2   | n2   |
+------+------+
|    3 | c    |
+------+------+
1 row in set (0.00 sec)

- 最终结果
mysql> SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.01 sec)

四、接着聊内连接

上面其实仅仅提到了一种最简单的内连接语法，就是直接把需要连接的多个表都放到FROM子句后边。其实针对内连接，MySQL提供了好多不同的语法，我们以t1和t2表为例瞅瞅：

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

也就是说在MySQL中，下边这几种内连接的写法都是等价的：

SELECT * FROM t1, t2;
SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;

这里需要注意的是，由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。下面我们聊外连接的时候会说明。

在内连接中，由于凡是不符合ON子句或WHERE子句中的条件的记录都会被过滤掉，其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去，所以对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。

内连接还有一个有趣的概念：自连接。也就是说自己连接自己，比方说我们可以对两个t1表来生成笛卡尔积，就像这样：

mysql> SELECT * FROM t1, t1;
ERROR 1066 (42000): Not unique table/alias: 't1'

报错原因是不允许FROM子句中出现相同的表名。我们这里需要的是两张一模一样的t1表进行连接，为了把两个一样的表区分一下，需要为表定义别名。比如这样：

mysql> SELECT * FROM t1 AS table1, t1 AS table2;
+------+------+------+------+
| m1   | n1   | m1   | n1   |
+------+------+------+------+
|    1 | a    |    1 | a    |
|    2 | b    |    1 | a    |
|    3 | c    |    1 | a    |
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
|    3 | c    |    2 | b    |
|    1 | a    |    3 | c    |
|    2 | b    |    3 | c    |
|    3 | c    |    3 | c    |
+------+------+------+------+
9 rows in set (0.00 sec)

或许有读者会有疑问，自连接有实际应用场景吗？答案是当然，我们来设计一个新的场景吧。

五、自连接的实际举例

假设有这么一个场景：电商的首页有一个一级商品分类列表，一级分类下有二级分类和三级分类，形如：

你所接到的需求为：前端给你传递一级分类ID，让你根据一级分类ID查出来下面的所有二级分类和三级分类，比如一级分类为“饼干/膨化”，将返回三个二级分类：“饼干”、“薯片”、“虾条”，其中“饼干”下面还有好几个三级分类，比如“苏打饼干”、“夹心饼干”等等。

表结构已经帮你设计好了：

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) NOT NULL COMMENT '分类名称',
  `type` int(11) NOT NULL COMMENT '分类类型',
  `father_id` int(11) NOT NULL COMMENT '父id',
  `logo` varchar(64) DEFAULT NULL COMMENT '图标',
  `slogan` varchar(64) DEFAULT NULL COMMENT '口号',
  `cat_image` varchar(64) DEFAULT NULL COMMENT '分类图',
  `bg_color` varchar(32) DEFAULT NULL COMMENT '背景颜色',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=utf8mb4 COMMENT='商品分类 ';

示例数据为：

INSERT INTO `category` (`id`, `name`, `type`, `father_id`, `logo`, `slogan`, `cat_image`, `bg_color`)
VALUES
 (1,'甜点/蛋糕',1,0,'img/cake.png','每一道甜品都能打开你的味蕾','http://122.152.205.72:88/foodie/category/cake.png','#fe7a65'),
 (2,'饼干/膨化',1,0,'img/cookies.png','嘎嘣脆，一听到声音就开吃','http://122.152.205.72:88/foodie/category/cookies.png','#f59cec'),
 (3,'熟食/肉类',1,0,'img/meat.png','食肉者最爱绝佳美食','http://122.152.205.72:88/foodie/category/meat.png','#b474fe'),
 (4,'素食/卤味',1,0,'img/luwei.png','香辣甜辣麻辣，辣了才有味','http://122.152.205.72:88/foodie/category/duck.png','#82ceff'),
 (5,'坚果/炒货',1,0,'img/jianguo.png','酥脆无比，休闲最佳','http://122.152.205.72:88/foodie/category/nut.png','#c6a868'),
 (6,'糖果/蜜饯',1,0,'img/sweet.png','甜味是爱美者的最爱','http://122.152.205.72:88/foodie/category/mango.png','#6bdea7'),
 (7,'巧克力',1,0,'img/chocolate.png','美容养颜，男女都爱','http://122.152.205.72:88/foodie/category/chocolate.png','#f8c375'),
 (8,'海鲜/海味',1,0,'img/lobster.png','吃货们怎么能少了海鲜呢？','http://122.152.205.72:88/foodie/category/crab.png','#84affe'),
 (9,'花茶/果茶',1,0,'img/tea.png','绿茶红茶怎能少得了','http://122.152.205.72:88/foodie/category/tea.png','#ff9229'),
 (10,'生鲜/蔬果',1,0,'img/food.png','新鲜少不了，每日蔬果生鲜','http://122.152.205.72:88/foodie/category/meat2.png','#6cc67c'),
 (11,'蛋糕',2,1,NULL,NULL,NULL,NULL),
 (12,'点心',2,1,NULL,NULL,NULL,NULL),
 (13,'饼干',2,2,NULL,NULL,NULL,NULL),
 (14,'薯片',2,2,NULL,NULL,NULL,NULL),
 (15,'虾条',2,2,NULL,NULL,NULL,NULL),
 (16,'肉铺',2,3,NULL,NULL,NULL,NULL),
 (17,'肉松',2,3,NULL,NULL,NULL,NULL),
 (18,'香肠',2,3,NULL,NULL,NULL,NULL),
 (19,'豆干',2,4,NULL,NULL,NULL,NULL),
 (20,'干笋',2,4,NULL,NULL,NULL,NULL),
 (21,'鸭脖',2,4,NULL,NULL,NULL,NULL),
 (22,'坚果',2,5,NULL,NULL,NULL,NULL),
 (23,'锅巴',2,5,NULL,NULL,NULL,NULL),
 (24,'糖果',2,6,NULL,NULL,NULL,NULL),
 (25,'蜜饯',2,6,NULL,NULL,NULL,NULL),
 (26,'巧克力',2,7,NULL,NULL,NULL,NULL),
 (27,'果冻',2,7,NULL,NULL,NULL,NULL),
 (29,'海鲜',2,8,NULL,NULL,NULL,NULL),
 (30,'海味',2,8,NULL,NULL,NULL,NULL),
 (31,'茶叶',2,9,NULL,NULL,NULL,NULL),
 (32,'奶茶',2,9,NULL,NULL,NULL,NULL),
 (33,'果茶',2,9,NULL,NULL,NULL,NULL),
 (34,'蔬菜',2,10,NULL,NULL,NULL,NULL),
 (35,'水果',2,10,NULL,NULL,NULL,NULL),
 (36,'肉类',2,10,NULL,NULL,NULL,NULL),
 (37,'蒸蛋糕',3,11,NULL,NULL,NULL,NULL),
 (38,'软面包',3,11,NULL,NULL,NULL,NULL),
 (39,'脱水蛋糕',3,11,NULL,NULL,NULL,NULL),
 (40,'马卡龙',3,11,NULL,NULL,NULL,NULL),
 (41,'甜甜圈',3,11,NULL,NULL,NULL,NULL),
 (42,'三明治',3,11,NULL,NULL,NULL,NULL),
 (43,'铜锣烧',3,11,NULL,NULL,NULL,NULL),
 (44,'肉松饼',3,12,NULL,NULL,NULL,NULL),
 (45,'华夫饼',3,12,NULL,NULL,NULL,NULL),
 (46,'沙琪玛',3,12,NULL,NULL,NULL,NULL),
 (47,'鸡蛋卷',3,12,NULL,NULL,NULL,NULL),
 (48,'蛋饼',3,12,NULL,NULL,NULL,NULL),
 (49,'凤梨酥',3,12,NULL,NULL,NULL,NULL),
 (50,'手撕面包',3,12,NULL,NULL,NULL,NULL),
 (51,'苏打饼干',3,13,NULL,NULL,NULL,NULL),
 (52,'夹心饼干',3,13,NULL,NULL,NULL,NULL),
 (53,'闲饼干',3,13,NULL,NULL,NULL,NULL),
 (54,'甜饼干',3,13,NULL,NULL,NULL,NULL),
 (55,'威化饼干',3,13,NULL,NULL,NULL,NULL),
 (56,'酥饼干',3,13,NULL,NULL,NULL,NULL),
 (57,'曲奇',3,13,NULL,NULL,NULL,NULL),
 (58,'无糖饼干',3,13,NULL,NULL,NULL,NULL),
 (59,'早餐饼干',3,13,NULL,NULL,NULL,NULL),
 (60,'薯条',3,14,NULL,NULL,NULL,NULL),
 (61,'薯片',3,14,NULL,NULL,NULL,NULL),
 (62,'山药薯片',3,14,NULL,NULL,NULL,NULL),
 (63,'网红薯片',3,14,NULL,NULL,NULL,NULL),
 (64,'蛋黄薯片',3,14,NULL,NULL,NULL,NULL),
 (65,'虾条',3,15,NULL,NULL,NULL,NULL),
 (66,'怀旧虾条',3,15,NULL,NULL,NULL,NULL),
 (67,'8090后虾条',3,15,NULL,NULL,NULL,NULL),
 (68,'进口虾条',3,15,NULL,NULL,NULL,NULL),
 (69,'DIY虾条',3,15,NULL,NULL,NULL,NULL),
 (73,'猪肉脯',3,16,NULL,NULL,NULL,NULL),
 (74,'牛肉脯',3,16,NULL,NULL,NULL,NULL),
 (75,'鸡肉脯',3,16,NULL,NULL,NULL,NULL),
 (76,'散装肉脯',3,16,NULL,NULL,NULL,NULL),
 (77,'猪肉干',3,16,NULL,NULL,NULL,NULL),
 (78,'牛肉干',3,16,NULL,NULL,NULL,NULL),
 (79,'牛肉粒',3,16,NULL,NULL,NULL,NULL),
 (80,'牛肉松',3,17,NULL,NULL,NULL,NULL),
 (81,'猪肉松',3,17,NULL,NULL,NULL,NULL),
 (82,'儿童肉松',3,17,NULL,NULL,NULL,NULL),
 (83,'腊味香肠',3,18,NULL,NULL,NULL,NULL),
 (84,'广式香肠',3,18,NULL,NULL,NULL,NULL),
 (85,'火腿肠',3,18,NULL,NULL,NULL,NULL),
 (86,'烤肠',3,18,NULL,NULL,NULL,NULL),
 (87,'鸡肉肠',3,18,NULL,NULL,NULL,NULL),
 (88,'鱼肠',3,18,NULL,NULL,NULL,NULL),
 (89,'酒味香肠',3,18,NULL,NULL,NULL,NULL),
 (90,'香菇',3,19,NULL,NULL,NULL,NULL),
 (91,'零食',3,19,NULL,NULL,NULL,NULL),
 (92,'豆腐干',3,19,NULL,NULL,NULL,NULL),
 (93,'辣条',3,19,NULL,NULL,NULL,NULL),
 (94,'泡椒脆笋',3,20,NULL,NULL,NULL,NULL),
 (95,'野生笋干',3,20,NULL,NULL,NULL,NULL),
 (96,'扁尖笋',3,20,NULL,NULL,NULL,NULL),
 (97,'农家笋',3,20,NULL,NULL,NULL,NULL),
 (98,'卤味鸭脖',3,21,NULL,NULL,NULL,NULL),
 (99,'麻辣鸭脖',3,21,NULL,NULL,NULL,NULL),
 (100,'武汉鸭脖',3,21,NULL,NULL,NULL,NULL),
 (101,'鸭锁骨',3,21,NULL,NULL,NULL,NULL),
 (102,'鸭胗',3,21,NULL,NULL,NULL,NULL),
 (103,'鸭翅',3,21,NULL,NULL,NULL,NULL),
 (104,'网红坚果',3,22,NULL,NULL,NULL,NULL),
 (105,'瓜子',3,22,NULL,NULL,NULL,NULL),
 (106,'碧根果',3,22,NULL,NULL,NULL,NULL),
 (107,'松子',3,22,NULL,NULL,NULL,NULL),
 (108,'山核桃',3,22,NULL,NULL,NULL,NULL),
 (109,'开心果',3,22,NULL,NULL,NULL,NULL),
 (110,'腰果',3,22,NULL,NULL,NULL,NULL),
 (111,'网红锅巴',3,23,NULL,NULL,NULL,NULL),
 (112,'蛋黄锅巴',3,23,NULL,NULL,NULL,NULL),
 (113,'水果糖',3,24,NULL,NULL,NULL,NULL),
 (114,'薄荷糖',3,24,NULL,NULL,NULL,NULL),
 (115,'夹心糖',3,24,NULL,NULL,NULL,NULL),
 (116,'奶糖',3,24,NULL,NULL,NULL,NULL),
 (117,'喜糖',3,24,NULL,NULL,NULL,NULL),
 (118,'麦芽糖',3,24,NULL,NULL,NULL,NULL),
 (119,'话梅',3,25,NULL,NULL,NULL,NULL),
 (120,'果干',3,25,NULL,NULL,NULL,NULL),
 (121,'草莓干',3,25,NULL,NULL,NULL,NULL),
 (122,'芒果干',3,25,NULL,NULL,NULL,NULL),
 (123,'菠萝干',3,25,NULL,NULL,NULL,NULL),
 (124,'山楂糕',3,25,NULL,NULL,NULL,NULL),
 (125,'黑巧克力',3,26,NULL,NULL,NULL,NULL),
 (126,'白巧克力',3,26,NULL,NULL,NULL,NULL),
 (127,'夹心巧克力',3,26,NULL,NULL,NULL,NULL),
 (128,'榛果巧克力',3,26,NULL,NULL,NULL,NULL),
 (129,'日本生巧',3,26,NULL,NULL,NULL,NULL),
 (130,'果肉布丁',3,27,NULL,NULL,NULL,NULL),
 (131,'果冻碎碎冰',3,27,NULL,NULL,NULL,NULL),
 (132,'果汁果冻',3,27,NULL,NULL,NULL,NULL),
 (133,'帝王蟹',3,29,NULL,NULL,NULL,NULL),
 (134,'大龙虾',3,29,NULL,NULL,NULL,NULL),
 (135,'鲍鱼',3,29,NULL,NULL,NULL,NULL),
 (136,'海参',3,29,NULL,NULL,NULL,NULL),
 (137,'鱿鱼',3,29,NULL,NULL,NULL,NULL),
 (138,'三文鱼',3,29,NULL,NULL,NULL,NULL),
 (139,'扇贝',3,29,NULL,NULL,NULL,NULL),
 (140,'干贝',3,30,NULL,NULL,NULL,NULL),
 (141,'海苔',3,30,NULL,NULL,NULL,NULL),
 (142,'虾仁',3,30,NULL,NULL,NULL,NULL),
 (143,'海鱼干',3,30,NULL,NULL,NULL,NULL),
 (144,'水蜜桃茶',3,33,NULL,NULL,NULL,NULL),
 (145,'白桃乌龙茶',3,33,NULL,NULL,NULL,NULL),
 (146,'柠檬片',3,33,NULL,NULL,NULL,NULL),
 (147,'白茶',3,31,NULL,NULL,NULL,NULL),
 (148,'红茶',3,31,NULL,NULL,NULL,NULL),
 (149,'绿茶',3,31,NULL,NULL,NULL,NULL),
 (150,'铁观音',3,31,NULL,NULL,NULL,NULL),
 (151,'胖大海',3,31,NULL,NULL,NULL,NULL),
 (152,'碧螺春',3,31,NULL,NULL,NULL,NULL),
 (153,'速冲奶茶',3,32,NULL,NULL,NULL,NULL),
 (154,'珍珠奶茶',3,32,NULL,NULL,NULL,NULL),
 (155,'袋装奶茶',3,32,NULL,NULL,NULL,NULL),
 (156,'英式奶茶',3,32,NULL,NULL,NULL,NULL),
 (157,'黄瓜',3,34,NULL,NULL,NULL,NULL),
 (158,'芋头',3,34,NULL,NULL,NULL,NULL),
 (159,'玉米',3,34,NULL,NULL,NULL,NULL),
 (160,'南瓜',3,34,NULL,NULL,NULL,NULL),
 (161,'荸荠',3,34,NULL,NULL,NULL,NULL),
 (162,'山药',3,34,NULL,NULL,NULL,NULL),
 (163,'秋葵',3,34,NULL,NULL,NULL,NULL),
 (164,'红薯/紫薯',3,34,NULL,NULL,NULL,NULL),
 (165,'土豆',3,34,NULL,NULL,NULL,NULL),
 (166,'水蜜桃',3,35,NULL,NULL,NULL,NULL),
 (167,'西瓜',3,35,NULL,NULL,NULL,NULL),
 (168,'苹果',3,35,NULL,NULL,NULL,NULL),
 (169,'凤梨',3,35,NULL,NULL,NULL,NULL),
 (170,'草莓',3,35,NULL,NULL,NULL,NULL),
 (171,'葡萄',3,35,NULL,NULL,NULL,NULL),
 (172,'杨梅',3,35,NULL,NULL,NULL,NULL),
 (173,'牛排',3,36,NULL,NULL,NULL,NULL),
 (174,'鸡肉',3,36,NULL,NULL,NULL,NULL),
 (175,'猪肉',3,36,NULL,NULL,NULL,NULL),
 (176,'羊肉',3,36,NULL,NULL,NULL,NULL),
 (177,'蹄子',3,36,NULL,NULL,NULL,NULL),
 (178,'糖醋排骨',3,36,NULL,NULL,NULL,NULL),
 (179,'第四级分类',4,37,NULL,NULL,NULL,NULL);

“饼干/膨化”这个一级分类的ID为2，如何根据2来查询出所属的二级分类和三级分类呢？

我们先拆解分析下，首先我们可以轻易地根据father_id=2查出来所有的二级分类：

mysql> select * from category where father_id=2;
+----+--------+------+-----------+------+--------+-----------+----------+
| id | name   | type | father_id | logo | slogan | cat_image | bg_color |
+----+--------+------+-----------+------+--------+-----------+----------+
| 13 | 饼干   |    2 |         2 | NULL | NULL   | NULL      | NULL     |
| 14 | 薯片   |    2 |         2 | NULL | NULL   | NULL      | NULL     |
| 15 | 虾条   |    2 |         2 | NULL | NULL   | NULL      | NULL     |
+----+--------+------+-----------+------+--------+-----------+----------+
3 rows in set (0.00 sec)

可以找到三个二级分类的信息，下面就需要再根据二级分类的ID分别查询三级分类的信息：

mysql> select * from category where father_id=13;
+----+--------------+------+-----------+------+--------+-----------+----------+
| id | name         | type | father_id | logo | slogan | cat_image | bg_color |
+----+--------------+------+-----------+------+--------+-----------+----------+
| 51 | 苏打饼干     |    3 |        13 | NULL | NULL   | NULL      | NULL     |
| 52 | 夹心饼干     |    3 |        13 | NULL | NULL   | NULL      | NULL     |
| 53 | 闲饼干       |    3 |        13 | NULL | NULL   | NULL      | NULL     |
| 54 | 甜饼干       |    3 |        13 | NULL | NULL   | NULL      | NULL     |
| 55 | 威化饼干     |    3 |        13 | NULL | NULL   | NULL      | NULL     |
| 56 | 酥饼干       |    3 |        13 | NULL | NULL   | NULL      | NULL     |
| 57 | 曲奇         |    3 |        13 | NULL | NULL   | NULL      | NULL     |
| 58 | 无糖饼干     |    3 |        13 | NULL | NULL   | NULL      | NULL     |
| 59 | 早餐饼干     |    3 |        13 | NULL | NULL   | NULL      | NULL     |
+----+--------------+------+-----------+------+--------+-----------+----------+
9 rows in set (0.01 sec)

mysql> select * from category where father_id=14;
+----+--------------+------+-----------+------+--------+-----------+----------+
| id | name         | type | father_id | logo | slogan | cat_image | bg_color |
+----+--------------+------+-----------+------+--------+-----------+----------+
| 60 | 薯条         |    3 |        14 | NULL | NULL   | NULL      | NULL     |
| 61 | 薯片         |    3 |        14 | NULL | NULL   | NULL      | NULL     |
| 62 | 山药薯片     |    3 |        14 | NULL | NULL   | NULL      | NULL     |
| 63 | 网红薯片     |    3 |        14 | NULL | NULL   | NULL      | NULL     |
| 64 | 蛋黄薯片     |    3 |        14 | NULL | NULL   | NULL      | NULL     |
+----+--------------+------+-----------+------+--------+-----------+----------+
5 rows in set (0.01 sec)

mysql> select * from category where father_id=15;
+----+---------------+------+-----------+------+--------+-----------+----------+
| id | name          | type | father_id | logo | slogan | cat_image | bg_color |
+----+---------------+------+-----------+------+--------+-----------+----------+
| 65 | 虾条          |    3 |        15 | NULL | NULL   | NULL      | NULL     |
| 66 | 怀旧虾条      |    3 |        15 | NULL | NULL   | NULL      | NULL     |
| 67 | 8090后虾条    |    3 |        15 | NULL | NULL   | NULL      | NULL     |
| 68 | 进口虾条      |    3 |        15 | NULL | NULL   | NULL      | NULL     |
| 69 | DIY虾条       |    3 |        15 | NULL | NULL   | NULL      | NULL     |
+----+---------------+------+-----------+------+--------+-----------+----------+
5 rows in set (0.00 sec)

我们能不能只用一个SQL来实现上面这个需求呢？答案就在于自连接。

分析这里的分类最多只有三层，即一级分类、二级分类、三级分类，那么只需要使用category自连接category一次，就可以同时查出二级分类和三级分类。二级分类和三级分类的连接枢纽就在于father_id。

category中father_id等于2的三条记录作为驱动表，此时驱动表的中记录都是二级分类，左连接category表的时候，使用二级分类的ID等于三级分类的father_id作为连接条件，就可以查出所有符合条件的三级分类，具体SQL如下：

select
    f.id as id,
    f.name as `name`,
    f.type as type,
    f.father_id as fatherId,
    c.id as subId,
    c.name as `subName`,
    c.type as subType,
    c.father_id as subFatherId
from
    category f
left JOIN
    category c
on f.id = c.father_id
where f.father_id = 2;

结果为：

+----+--------+------+----------+-------+---------------+---------+-------------+
| id | name   | type | fatherId | subId | subName       | subType | subFatherId |
+----+--------+------+----------+-------+---------------+---------+-------------+
| 13 | 饼干   |    2 |        2 |    51 | 苏打饼干      |       3 |          13 |
| 13 | 饼干   |    2 |        2 |    52 | 夹心饼干      |       3 |          13 |
| 13 | 饼干   |    2 |        2 |    53 | 闲饼干        |       3 |          13 |
| 13 | 饼干   |    2 |        2 |    54 | 甜饼干        |       3 |          13 |
| 13 | 饼干   |    2 |        2 |    55 | 威化饼干      |       3 |          13 |
| 13 | 饼干   |    2 |        2 |    56 | 酥饼干        |       3 |          13 |
| 13 | 饼干   |    2 |        2 |    57 | 曲奇          |       3 |          13 |
| 13 | 饼干   |    2 |        2 |    58 | 无糖饼干      |       3 |          13 |
| 13 | 饼干   |    2 |        2 |    59 | 早餐饼干      |       3 |          13 |
| 14 | 薯片   |    2 |        2 |    60 | 薯条          |       3 |          14 |
| 14 | 薯片   |    2 |        2 |    61 | 薯片          |       3 |          14 |
| 14 | 薯片   |    2 |        2 |    62 | 山药薯片      |       3 |          14 |
| 14 | 薯片   |    2 |        2 |    63 | 网红薯片      |       3 |          14 |
| 14 | 薯片   |    2 |        2 |    64 | 蛋黄薯片      |       3 |          14 |
| 15 | 虾条   |    2 |        2 |    65 | 虾条          |       3 |          15 |
| 15 | 虾条   |    2 |        2 |    66 | 怀旧虾条      |       3 |          15 |
| 15 | 虾条   |    2 |        2 |    67 | 8090后虾条    |       3 |          15 |
| 15 | 虾条   |    2 |        2 |    68 | 进口虾条      |       3 |          15 |
| 15 | 虾条   |    2 |        2 |    69 | DIY虾条       |       3 |          15 |
+----+--------+------+----------+-------+---------------+---------+-------------+
19 rows in set (0.01 sec)

为了方便演示，SQL中f.father_id=2写的是固定的，实际业务可以根据接口实际接收到的一级分类ID动态处理即可。

六、外连接

上面说过，内连接会从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去，比如：

mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

我们也提到了驱动表和被驱动表的概念，如果我们希望：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集，这可以实现吗？

因为这是一个硬性的需求，我们拿比较实在的例子说明吧，还是拿用户表和收货地址表，我们稍微改改数据，变为：

mysql> select * from users;
+------------------+-----------------+--------------------------+----------+------------------------------------------------------------------------------+--------+-------+------+------------+---------------------+---------------------+
| id               | username        | password                 | realname | face                                                                         | mobile | email | sex  | birthday   | created_time        | updated_time        |
+------------------+-----------------+--------------------------+----------+------------------------------------------------------------------------------+--------+-------+------+------------+---------------------+---------------------+
| 190815GTKCBSS7MW | fossi           | Qpf0SxOVUjUkWySXOZ16kw== | NULL     | http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png | NULL   | NULL  |    2 | 1900-01-01 | 2022-08-15 22:11:58 | 2022-08-15 22:11:58 |
| 190816HH9RDPD6Y8 | 我去个地方      | Qpf0SxOVUjUkWySXOZ16kw== | NULL     | http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png | NULL   | NULL  |    2 | 1900-01-01 | 2022-08-16 23:14:12 | 2022-08-16 23:14:12 |
| 1908189H7TNWDTXP | alite           | Qpf0SxOVUjUkWySXOZ16kw== | NULL     | http://122.152.205.72:88/group1/M00/00/05/CpoxxFw_8_qAIlFXAAAcIhVPdSg994.png | NULL   | NULL  |    2 | 1900-01-01 | 2022-08-18 13:25:30 | 2022-08-18 13:25:30 |
| 200425CN4W2HZFK4 | yummy           | 4QrcOUm6Wau+VuBX8g+IPg== | NULL     | http://bloghello.oursnail.cn/avatar.png                                      | NULL   | NULL  |    2 | 1900-01-01 | 2022-04-25 17:46:25 | 2022-04-25 17:46:25 |
+------------------+-----------------+--------------------------+----------+------------------------------------------------------------------------------+--------+-------+------+------------+---------------------+---------------------+
4 rows in set (0.00 sec)


mysql> select * from user_address;                                              +------------------+------------------+-----------------+-------------+----------+--------+-----------+----------------------+--------+------------+---------------------+---------------------+
| id               | user_id          | receiver        | mobile      | province | city   | district  | detail               | extand | is_default | created_time        | updated_time        |
+------------------+------------------+-----------------+-------------+----------+--------+-----------+----------------------+--------+------------+---------------------+---------------------+
| 190825CG3AA14Y3C | 190815GTKCBSS7MW | fossi           | 13333333333 | 北京     | 北京   | 东城区    | 幸福小区12号街       | NULL   |          1 | 2022-08-25 17:34:14 | 2022-08-25 17:34:14 |
| 190825CG4ZCSSWM8 | 190816HH9RDPD6Y8 | 我去个地方      | 13666666666 | 北京     | 北京   | 海淀区    | 阳光里街道           | NULL   |          1 | 2022-08-25 17:34:24 | 2022-08-25 17:34:24 |
+------------------+------------------+-----------------+-------------+----------+--------+-----------+----------------------+--------+------------+---------------------+---------------------+
2 rows in set (0.01 sec)

我们进行内连接查询，同时查询用户表和用户收货地址表：

mysql> select u.id,u.username,ua.mobile,ua.province,ua.city,ua.district,ua.detail from users u inner join user_address ua on u.id = ua.user_id;
+------------------+-----------------+-------------+----------+--------+-----------+----------------------+
| id               | username        | mobile      | province | city   | district  | detail               |
+------------------+-----------------+-------------+----------+--------+-----------+----------------------+
| 190815GTKCBSS7MW | fossi           | 13333333333 | 北京     | 北京   | 东城区    | 幸福小区12号街       |
| 190816HH9RDPD6Y8 | 我去个地方      | 13666666666 | 北京     | 北京   | 海淀区    | 阳光里街道           |
+------------------+-----------------+-------------+----------+--------+-----------+----------------------+
2 rows in set (0.01 sec)

我们前边曾经为列命名过别名，与列的别名类似，我们也可以为表来定义别名，格式与定义列的别名一致，都是用空白字符或者AS隔开，这个在表名特别长的情况下可以让语句表达更清晰一些，这里给users取了一个别名u，给user_address取了一个别名叫做ua，相信十分容易理解。

可以发现一个问题，就是咱们的alite和yummy两个用户没有设置收货地址，但是我们仍然需要展示所有用户，如果他确实没有设置收货地址信息，则该用户关于收货地址字段显示为空即可。

为了解决这个问题，就有了内连接和外连接的概念：

对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。
对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：

左外连接：选取左侧的表为驱动表。
右外连接：选取右侧的表为驱动表。

我们着重说说左外连接，右外连接只是反过来而已，左外连接的语法为：

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

其中中括号里的OUTER单词是可以省略的。对于LEFT JOIN类型的连接来说，我们把放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。所以上述例子中t1就是外表或者驱动表，t2就是内表或者被驱动表。

需要注意的是，对于左（外）连接和右（外）连接来说，必须使用ON子句来指出连接条件。

这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的。所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。

对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。所以，在外连接中，表的顺序十分重要！

一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

再次回到我们的需求，我们希望查询所有用户以及对应的收货地址信息：

mysql> select u.id,u.username,ua.mobile,ua.province,ua.city,ua.district,ua.detail from users u left join user_address ua on u.id = ua.user_id;
+------------------+-----------------+-------------+----------+--------+-----------+----------------------+
| id               | username        | mobile      | province | city   | district  | detail               |
+------------------+-----------------+-------------+----------+--------+-----------+----------------------+
| 190815GTKCBSS7MW | fossi           | 13333333333 | 北京     | 北京   | 东城区    | 幸福小区12号街       |
| 190816HH9RDPD6Y8 | 我去个地方      | 13666666666 | 北京     | 北京   | 海淀区    | 阳光里街道           |
| 1908189H7TNWDTXP | alite           | NULL        | NULL     | NULL   | NULL      | NULL                 |
| 200425CN4W2HZFK4 | yummy           | NULL        | NULL     | NULL   | NULL      | NULL                 |
+------------------+-----------------+-------------+----------+--------+-----------+----------------------+
4 rows in set (0.00 sec)

可以看到，虽然alite和yummy确实没有设置收货地址，但是由于采用的是连接类型为左（外）连接，所以仍然把它放到了结果集中，只不过在对应的成绩记录的各列使用NULL值填充而已。

右（外）连接和左（外）连接的原理是一样一样的，语法也只是把LEFT换成RIGHT而已：

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

只不过驱动表是右边的表，被驱动表是左边的表，就不再赘述了，我们拿最简单的t1和t2表来展示内连接、左连接和右连接的区别吧：

mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
|    1 | a    | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t1 RIGHT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
| NULL | NULL |    4 | d    |
+------+------+------+------+
3 rows in set (0.00 sec)

内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集。

七、总结

本文核心梳理了连接的概念，以及最核心的内连接和外连接的写法、概念和区别。表的连接是平时业务开发中最常见的操作，我们简单分析了表连接过程的执行流程，可以看到，当使用不当时，表连接所要扫描的行数和执行时间可能会很长，为了保障效率，实际业务中请不要在一个SQL中做过多的表连接，要根据实际情况优化SQL，这一点也是后续我们分析MYSQL原理、优化SQL效率过程中还会提及和重点说明的地方。到目前为止，我们只需要知道连接的用法和大概的执行流程即可。