MariaDB 教程 / 用 UNION 和 UNION ALL 合并查询结果

UNION 把列结构相容的查询结果纵向合并,并删除重复的已选行;UNION ALL 则保留重复行。合并前应对齐列数、位置与含义,需要确定顺序时,在完整组合结果上使用最终 ORDER BY。

区分纵向合并结果与横向连接属性

在客户旁边显示订单属性,使用 JOIN;把首尔客户与有已支付订单的客户放进同一份目标名单,则可以使用 UNION。对应位置必须表示相同概念,客户编号和订单编号不能因为都是整数,就放进同一列当成同一种标识。

操作 重复处理 适用意图
UNION 删除完全相同的已选值组合 合并有重叠的目标人群
UNION ALL 保留重复出现的行 保留来自各输入的出现次数
JOIN 把匹配的记录与列连接在一起 一起读取有关联的事实

与 SELECT 与 DISTINCT 教程一样,去重需要查看全部已选列;它不同于 INNER JOIN 的键匹配。语法与排序范围的规则可查阅 MariaDB 官方 UNION 文档。

准备两组有重叠的客户

以 course_user 连接练习服务器,先核对 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;。本节独立使用 c30_customers、c30_products、c30_orders、c30_items 四表;若有同名对象,先检查定义和记录,不要覆盖未知用途的数据。

完整代码应在 MariaDB 客户端执行。也可按 SQL 文件与 SOURCE 教程保存为 UTF-8 文件,用实际路径加载。示例不依赖其他章节建好的表,父表在前、子表在后创建和插入。

USE shopcourse;
CREATE TABLE c30_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c30_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c30_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c30_order_customer FOREIGN KEY (customer_id)
  REFERENCES c30_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c30_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c30_item_order FOREIGN KEY (order_id)
  REFERENCES c30_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c30_item_product FOREIGN KEY (product_id)
  REFERENCES c30_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c30_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c30_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c30_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c30_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

姓名、城市与商品名保留原始韩文字符串。商品 1~4 表示笔记本、圆珠笔、马克杯和台灯;客户 1、3 的城市值 서울 表示首尔,客户 2、4 分别为釜山和大田。请按原样保存字面量,用编号核对结果。

首尔客户为 1、3,有 paid 订单的客户为 1、2,客户 1 同时属于两组。第二组使用 EXISTS,所以客户 1 虽然有两个已支付订单,在该输入分支中仍只出现一次;相关规则见 EXISTS 存在性查询教程。

比较 UNION 与 UNION ALL 的行数

SELECT customer_id FROM c30_customers WHERE city='서울'
UNION
SELECT c.customer_id FROM c30_customers c WHERE EXISTS
 (SELECT 1 FROM c30_orders o WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY customer_id;
SELECT customer_id FROM c30_customers WHERE city='서울'
UNION ALL
SELECT c.customer_id FROM c30_customers c WHERE EXISTS
 (SELECT 1 FROM c30_orders o WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY customer_id;

第一条 UNION 返回三行,编号为 1、2、3。第二条 UNION ALL 返回四行,编号为 1、1、2、3。重复的客户 1 表示它来自两个输入名单,不是源表里多插入了一位客户。

UNION 的去重也适用于单个输入内部已经重复的结果。例如若把 paid 分支改成客户 JOIN 订单,客户 1 会在该分支出现两次;UNION 仍能对最终所选值去重,UNION ALL 则保留所有出现次数。应先弄清每个分支的结果粒度,再决定最终是否需要唯一客户名单。

只把 ALL 去掉并比较结果,再恢复 ALL,可以观察同样两组输入下的差别。UNION ALL 可以省去某些去重工作,但不能为了速度丢掉业务要求的唯一性;同样,也不应在需要保留每次出现时使用 UNION 抹掉记录。

理解来源标签如何改变整行相等

SELECT customer_id,'서울 고객' AS source_label FROM c30_customers WHERE city='서울'
UNION
SELECT c.customer_id,'결제 고객' FROM c30_customers c WHERE EXISTS
 (SELECT 1 FROM c30_orders o WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY customer_id,source_label;

标签 서울 고객 表示“首尔客户”,결제 고객 表示“已支付订单客户”。这次虽然使用 UNION,客户 1 仍保留两行,因为两条记录的 source_label 不同。总共得到四个不同的“客户编号+标签”组合。

UNION 不会只盯着 customer_id 去重。若把两个分支的标签改成相同文本,相同客户的整行才会相同,结果会回到三个客户组合。核对后恢复各自标签,确认客户 1 的两种来源重新保留。

如果需要每位客户一行、同时显示多个来源,应另行设计来源汇总或展示方式。不能一边选择不同的来源标签,一边期待普通 UNION 自动把它们合成一个单元格。

对齐列数、类型与最终排序范围

各分支必须返回相同列数。第一列对第一列、第二列对第二列进行合并,因此名称看起来相同并不能弥补位置错误。类型也应表达同一意义;混合字符串与数字时,要检查最终转换、长度和比较规则,不要只确认语句能运行。

组合结果的列名通常来自第一个 SELECT,后面分支写另一个别名不会独立重命名最终列。最终 ORDER BY 应引用组合结果实际可用的列名或别名。本例最末尾的 ORDER BY 对完整 UNION 结果生效,不能仅靠各分支内部排序保证输出顺序。

若每个分支确实要先取自己的前 N 行,应使用括号明确各分支的 ORDER BY 与 LIMIT,再单独写全局排序。分支 LIMIT 决定哪些记录进入组合;全局 LIMIT 决定从组合结果中取多少行,两者不能互换。例如一支取最大编号、一支取最小编号,再全局升序,得到的是两个局部选择后的整体排序。

唯一客户数与出现次数应分别定义。合并时去重可能改变数量,所以不要把 UNION 的行数当成来源事件的总数;需要次数时,应保留相应记录和标识。

先审查各个输入,再组合结果

分别执行两个分支,核对每列含义、行数、重复与 NULL,再查看 UNION ALL,观察重叠来自哪里。确认业务是否要求删除完全相同的组合后,再选择 UNION。输出不符合预期时,优先检查额外的标签、时间或其他已选列是否使整行变得不同。

这些操作只改变查询结果,不会删除源记录,也不会把底层表永久合成一张表。需要保存组合结果,是另一项明确的数据写入任务,不能由一次 SELECT 的显示结果推断已经完成。

重练或清理前,检查四表定义与记录,并备份应保留的内容。下面仅删除已确认可丢弃的本节四表,先子表后父表;DROP TABLE 会删除定义和全部行,普通 ROLLBACK 无法恢复,应与事务实验分开。

DROP TABLE shopcourse.c30_items;
DROP TABLE shopcourse.c30_orders;
DROP TABLE shopcourse.c30_products;
DROP TABLE shopcourse.c30_customers;