Mysql联合查询union和union all的使用介绍

本文介绍如何在SQL中进行联合查询，并演示了不同类型的UNION查询，包括UNION ALL（保留重复项）、标准UNION（去除重复项）以及带来源标记的联合查询。此外还展示了如何创建和填充数据库表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

http://www.itxm.net/a/shujuku/2016/1205/841.html

https://www.cnblogs.com/wangyayun/p/6133540.html

适应于少量表的级联查询

-- 建表、填充数据
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `company` VALUES ('1', '小米');
INSERT INTO `company` VALUES ('2', '华为');

DROP TABLE IF EXISTS `fixshop`;
CREATE TABLE `fixshop` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '维修点',
  `title` varchar(50) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `fixshop` VALUES ('3', '魅族', '0');
INSERT INTO `fixshop` VALUES ('4', '苹果', '0');

DROP TABLE IF EXISTS `shop`;
CREATE TABLE `shop` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shopname` varchar(50) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `shop` VALUES ('1', '小米', '0');
INSERT INTO `shop` VALUES ('4', '苹果', '1');

-- union 及 union on 的使用

-- union all 查询所有结果(包含重复项)
(SELECT id,name from company ) union all (SELECT id,title from fixshop ) union all (SELECT id,shopname from shop );

-- union  查询结果(不包含重复项)
(SELECT id,name from company ) union  (SELECT id,title from fixshop ) union  (SELECT id,shopname from shop );

-- 查询结果并标记来源
(SELECT id,name,1 as class from company ) union  (SELECT id,title,2 as class from fixshop ) union  (SELECT id,shopname,3 as class from shop where status = 0);

-- 模糊查询结果(类似应用首页的全模块搜索)
(SELECT id,name,1 as class from company where name LIKE "%米%" ) union  (SELECT id,title,2 as class from fixshop where title LIKE "%米%") union  (SELECT id,shopname,3 as class from shop where status = 0 and shopname LIKE "%米%") ORDER BY class;