多表匹配目标表查询并动态添加新列（union和union all关键字的使用详解）

在开发过程中遇到一个表需要从其他表的关联关系中进行匹配并且筛选的需求。我将其抽象成以下的需求进行模拟。

有多个不同颜色的篮子，里面放苹果，香蕉，梨，桃子；一个篮子里可以放不同的水果。
一种水果在一个篮子里只能有一条记录，但是桃子除外，因为桃子还分为红桃子和黄桃子；除此之外，篮子中的水果还分为熟的和不熟的。

需求如下：

查询出成熟水果所存放的篮子信息，并且在每条查询记录后面标记是什么水果。

表结构：

篮子表：

name	type	key	comment
basket_id	int	☑️	篮子id
basket_name	varchar		篮子名字

苹果存放记录表：

name	type	key	comment
apple_id	int	☑️	苹果存放记录id
basket	int		篮子id
apple_num	int		苹果数量
ripe	varchar		是否成熟

梨存放记录表：

name	type	key	comment
pear_id	int	☑️	梨存放记录id
basket	int		篮子id
pear_num	int		梨数量
ripe	varchar		是否成熟

香蕉存放记录表：

name	type	key	comment
banana_id	int	☑️	香蕉存放记录id
basket	int		篮子id
banana_num	int		香蕉数量
ripe	varchar		是否成熟

桃子存放记录表：

name	type	key	comment
peach_id	int	☑️	桃子存放记录id
basket	int		篮子id
peach_num	int		桃子数量
peach_color	varchar		桃子颜色
ripe	varchar		是否成熟

导出带有数据的sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for apple
-- ----------------------------
DROP TABLE IF EXISTS `apple`;
CREATE TABLE `apple` (
  `apple_id` int(11) NOT NULL AUTO_INCREMENT,
  `basket` int(11) DEFAULT NULL,
  `apple_num` int(11) DEFAULT NULL,
  `ripe` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`apple_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of apple
-- ----------------------------
BEGIN;
INSERT INTO `apple` VALUES (1, 1, 20, '1');
INSERT INTO `apple` VALUES (2, 2, 20, '1');
INSERT INTO `apple` VALUES (3, 3, 30, '1');
INSERT INTO `apple` VALUES (4, 4, 40, '1');
INSERT INTO `apple` VALUES (5, 6, 10, '1');
INSERT INTO `apple` VALUES (6, 9, 5, '1');
INSERT INTO `apple` VALUES (7, 7, 32, '0');
COMMIT;

-- ----------------------------
-- Table structure for banana
-- ----------------------------
DROP TABLE IF EXISTS `banana`;
CREATE TABLE `banana` (
  `banana_id` int(11) NOT NULL AUTO_INCREMENT,
  `basket` int(11) DEFAULT NULL,
  `banana_num` int(11) DEFAULT NULL,
  `ripe` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`banana_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of banana
-- ----------------------------
BEGIN;
INSERT INTO `banana` VALUES (1, 1, 30, '1');
INSERT INTO `banana` VALUES (2, 2, 50, '1');
INSERT INTO `banana` VALUES (3, 5, 34, '1');
INSERT INTO `banana` VALUES (4, 4, 41, '1');
INSERT INTO `banana` VALUES (5, 6, 30, '1');
INSERT INTO `banana` VALUES (6, 9, 50, '1');
INSERT INTO `banana` VALUES (7, 8, 22, '0');
COMMIT;

-- ----------------------------
-- Table structure for basket
-- ----------------------------
DROP TABLE IF EXISTS `basket`;
CREATE TABLE `basket` (
  `basket_id` int(11) NOT NULL AUTO_INCREMENT,
  `basket_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`basket_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of basket
-- ----------------------------
BEGIN;
INSERT INTO `basket` VALUES (1, '红篮子');
INSERT INTO `basket` VALUES (2, '黄篮子');
INSERT INTO `basket` VALUES (3, '蓝篮子');
INSERT INTO `basket` VALUES (4, '绿篮子');
INSERT INTO `basket` VALUES (5, '紫篮子');
INSERT INTO `basket` VALUES (6, '橙篮子');
INSERT INTO `basket` VALUES (7, '青篮子');
INSERT INTO `basket` VALUES (8, '白篮子');
INSERT INTO `basket` VALUES (9, '黑篮子');
COMMIT;

-- ----------------------------
-- Table structure for peach
-- ----------------------------
DROP TABLE IF EXISTS `peach`;
CREATE TABLE `peach` (
  `peach_id` int(11) NOT NULL AUTO_INCREMENT,
  `basket` int(11) DEFAULT NULL,
  `peach_num` int(11) DEFAULT NULL,
  `peach_color` varchar(255) DEFAULT NULL,
  `ripe` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`peach_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of peach
-- ----------------------------
BEGIN;
INSERT INTO `peach` VALUES (1, 2, 20, 'yellow', '1');
INSERT INTO `peach` VALUES (2, 2, 10, 'red', '1');
INSERT INTO `peach` VALUES (3, 3, 15, 'yellow', '1');
INSERT INTO `peach` VALUES (4, 4, 22, 'yellow', '1');
INSERT INTO `peach` VALUES (5, 7, 12, 'yellow', '1');
INSERT INTO `peach` VALUES (6, 7, 34, 'red', '1');
INSERT INTO `peach` VALUES (7, 8, 32, 'red', '1');
INSERT INTO `peach` VALUES (8, 9, 43, 'yellow', '1');
INSERT INTO `peach` VALUES (9, 9, 37, 'red', '1');
INSERT INTO `peach` VALUES (10, 1, 22, 'red', '0');
COMMIT;

-- ----------------------------
-- Table structure for pear
-- ----------------------------
DROP TABLE IF EXISTS `pear`;
CREATE TABLE `pear` (
  `pear_id` int(11) NOT NULL AUTO_INCREMENT,
  `basket` int(11) DEFAULT NULL,
  `pear_num` int(11) DEFAULT NULL,
  `ripe` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`pear_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of pear
-- ----------------------------
BEGIN;
INSERT INTO `pear` VALUES (1, 1, 30, '1');
INSERT INTO `pear` VALUES (2, 2, 30, '1');
INSERT INTO `pear` VALUES (3, 3, 43, '1');
INSERT INTO `pear` VALUES (4, 5, 51, '1');
INSERT INTO `pear` VALUES (5, 7, 30, '1');
INSERT INTO `pear` VALUES (6, 9, 15, '1');
INSERT INTO `pear` VALUES (7, 8, 11, '1');
INSERT INTO `pear` VALUES (8, 6, 32, '0');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

sql语句：

使用union all：

使用union all的sql的查询结果会将不同颜色的桃子都当作单独的记录显示；

select c.* from (
select a1.*,'apple' type from basket a1,apple b1 where a1.basket_id = b1.basket and b1.ripe='1'
UNION all
select a2.*,'banana' type from basket a2,banana b2 where a2.basket_id = b2.basket and b2.ripe='1'
UNION all
select a3.*,'pear' type from basket a3,pear b3 where a3.basket_id = b3.basket and b3.ripe='1'
UNION all
select a4.*,'peach' type from basket a4,peach b4 where a4.basket_id = b4.basket and b4.ripe='1'
) c

使用union：

使用union的sql的查询结果会将不同颜色的桃子合并去重显示；

select c.* from (
select a1.*,'apple' type from basket a1,apple b1 where a1.basket_id = b1.basket and b1.ripe='1'
UNION
select a2.*,'banana' type from basket a2,banana b2 where a2.basket_id = b2.basket and b2.ripe='1'
UNION
select a3.*,'pear' type from basket a3,pear b3 where a3.basket_id = b3.basket and b3.ripe='1'
UNION
select a4.*,'peach' type from basket a4,peach b4 where a4.basket_id = b4.basket and b4.ripe='1'
) c

注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
union和union all的区别：
UNION 操作符将选取不同的值。使用 UNION ALL则允许重复的值。