本章节主要介绍在 DM 数据库中如何进行多表联合检索。
一、适用场景
| 软件 | 版本 |
|---|---|
| 操作系统 | Redhat 7 及以上版本 |
| DM 数据库 | DM 8.0 及以上版本 |
| CPU 架构 | x86、ARM、龙芯、飞腾等国内外主流 CPU |
二、操作方法
2.1 union all 与空字符串
使用 union 或者 union all 关键字合并多个结果集时，对应的列数必须一致，列的数据类型必须匹配。当其中一个结果集的列数不满足要求时，可以使用 NULL 或者 空字符串 填充。示例语句如下所示：
--使用 NULL 填充
SELECT employee_name, department_id
FROM dmhr.employee
WHERE ROWNUM < 5
UNION ALL
SELECT 'DM2021', NULL FROM DUAL;
---使用空字符串填充
SELECT employee_name, department_id
FROM dmhr.employee
WHERE ROWNUM < 5
UNION ALL
SELECT 'DM2021', '' FROM DUAL;
上述两条 SQL 输出结果一致。
注意如果存在 order by 子句，必须添加到 union 和 union all 的最后。
2.2 union all 与 or
union all 用于合并两个结果集。比如姓名为常鹏程的员工编号是 2002，所有 or 查询是正确的，但 union all 却重复了。示例语句如下所示：
SELECT * FROM dmhr.employee WHERE employee_id = 2002 OR employee_name = '常鹏程';
等同于：
SELECT *
FROM dmhr.employee
WHERE employee_id = 2002
UNION ALL
SELECT *
FROM dmhr.employee
WHERE employee_name = '常鹏程';
输出结果如下图所示：
建议使用 union all 替换 or，执行计划更高效，出现重复行时，使用 union 去重。
2.3 union 与去重
union 也用于合并两个结果集，同时还有去重的功能。union 相当于对 union all 的输出结果再执行一次 DISTINCT 操作。示例语句如下所示：
SELECT *
FROM dmhr.employee
WHERE employee_id = 2002
UNION
SELECT *
FROM dmhr.employee
WHERE employee_name = '常鹏程';
输出结果：
2.4 差集函数
EXCEPT 用于从一个表（原表）里查找出某个目标表里不存在的值。
创建 dept 表，示例语句如下所示：
CREATE TABLE dmhr.dept AS SELECT department_id FROM dmhr.employee;
插入 employee 表中不存在的部门号，示例语句如下所示：
INSERT INTO dmhr.dept VALUES (888),(999),(3004); COMMIT;
得出 department 表不存在的部门号，示例语句如下所示：
SELECT department_id FROM dmhr.dept EXCEPT SELECT department_id FROM dmhr.employee;
输出结果如下图所示：
EXCEPT 获取第一个结果集的数据，然后排除第二个结果集的数据，类似减法运算。
2.5 IN、NOT IN、EXISTS
查询含有 null 值的行时，如果包含 IN、NOT IN 要注意两者的区别。IN 相当于 OR, 而 NOT IN 相当于 AND。示例语句如下所示：
SELECT * FROM dmhr.employee WHERE employee_id IN (1002, 1005, NULL);
返回 2 行数据记录，示例语句如下所示：
SELECT * FROM dmhr.employee WHERE employee_id NOT IN (1002, 1005, NULL);
返回记录为空，因为 NOT IN 的逻辑是 1002 AND 1005 AND NULL。当 NOT IN 后面跟的子查询返回的列存在 NULL 值，可能得不到正确的结果。
例如，查找员工所在部门编号，在部门表中不存在的数据。
- 数据准备，示例语句如下所示：
CREATE TABLE dmhr.emp
AS
SELECT employee_id,
employee_name,
identity_card,
salary,
department_id
FROM dmhr.employee
WHERE employee_id IN (1109,1110,1111,1112,1113);
UPDATE dmhr.emp
SET department_id = 999
WHERE employee_id = 1112;
UPDATE dmhr.emp
SET department_id = NULL
WHERE employee_id = 1113;
COMMIT;
- 员工表
执行如下 SQL，返回结果为空。
SELECT *
FROM dmhr.emp t WHERE t.department_id NOT IN (SELECT department_id FROM dmhr.dept);
执行如下 SQL，返回结果不为空。
SELECT *
FROM dmhr.emp t
WHERE NOT EXISTS
(SELECT 1
FROM dmhr.dept t1
WHERE t.department_id = t1.department_id);
输出结果如下图所示：
2.6 连接类型
连接包括：内连接、左连接、右连接、全连接、自连接 5 种类型，以上连接类型 DM 数据库都支持。
- 数据准备，示例语句如下所示：
CREATE TABLE dmhr.join_emp
AS
SELECT employee_name, department_id
FROM dmhr.employee
WHERE employee_id IN ( SELECT MIN (employee_id)
FROM dmhr.employee
GROUP BY department_id)
AND ROWNUM < 10;
INSERT INTO dmhr.join_emp
VALUES ('DM2020', 999);
COMMIT;
SELECT * FROM dmhr.join_emp;
输出结果如下图所示：
红框内标识为后续插入的记录。
- 内连接
结果完全满足连接条件的记录。例如，如需显示员工名称和对应的部门名称。
方法一：
SELECT je.employee_name, d.department_name
FROM dmhr.join_emp je, dmhr.department d
WHERE je.department_id = d.department_id;
方法二：
SELECT je.employee_name, d.department_name
FROM dmhr.join_emp je
JOIN dmhr.department d ON (je.department_id = d.department_id)
输出结果如下图所示：
- 左外连接
结果不仅包含满足条件的记录，还包含位于左表中不满足条件的记录，此时右表的记录显示为 NULL。示例语句如下所示：
SELECT je.employee_name, d.department_name
FROM dmhr.join_emp je
LEFT OUTER JOIN dmhr.department d
ON (je.department_id = d.department_id);
输出结果如下图所示：
join_emp 表中名为 DM202 的员工对应的部门在 department 表中不存在与之匹配的记录。
- 右外连接
结果不仅包含满足条件的记录，还包含位于右表中不满足条件的记录，对应的左表的记录显示为 NULL。示例语句如下所示：
SELECT je.employee_name, d.department_name
FROM dmhr.join_emp je
RIGHT OUTER JOIN dmhr.department d
ON (je.department_id = d.department_id);
输出结果如下图所示：
- 全外连接
结果不仅包含满足条件的记录，还会包含位于两边表中所有不满足条件的记录，对应的两边表的记录显示为 NULL。示例语句如下所示：
SELECT je.employee_name, d.department_name
FROM dmhr.join_emp je
FULL OUTER JOIN dmhr.department d
ON (je.department_id = d.department_id);
输出结果如下图所示：
- 自连接
表和自身进行连接。例如，需查询显示所有员工的部门经理的名字。示例语句如下所示：
SELECT je.employee_name, je.department_id, d.employee_name
FROM dmhr.employee je, dmhr.employee d
WHERE (je.manager_id = d.employee_id);
输出结果如下图所示：
注意自连接查询至少要对一张表起别名，否则，服务器无法识别要处理的是哪张表。
2.7 聚集与内连接
首先建立案例用表，示例语句如下所示：
CREATE TABLE dmhr.emp_bonus
(
employee_id NUMBER,
received DATE,
TYPE NUMBER
);
INSERT INTO dmhr.emp_bonus
VALUES (1137, '2020-1-1 8:00', 1);
INSERT INTO dmhr.emp_bonus
VALUES (1137, '2020-3-1 8:00', 2);
INSERT INTO dmhr.emp_bonus
VALUES (1138, '2020-1-1 8:00', 3);
INSERT INTO dmhr.emp_bonus
VALUES (1139, '2020-1-1 8:00', 1);
INSERT INTO dmhr.emp_bonus
VALUES (1140, '2020-1-1 8:00', 1);
COMMIT;
以上是员工奖金发放表，type 列决定了奖金的数额。若 type=1，则奖金是工资的 10%；若 type=2，则奖金是工资的 20%; type=3，则奖金是工资的 30%。要求返回上述（部门编号是 105）员工工资和奖金的总额。
- 先关联再聚合，示例语句如下所示：
SELECT e.employee_id,e.employee_name,e.salary,e.department_id,e.salary
* CASE WHEN eb.TYPE = 1 THEN .1 WHEN eb.TYPE = 2 THEN .2 ELSE .3 END
AS bonus
FROM dmhr.employee e, dmhr.emp_bonus eb
WHERE e.employee_id = eb.employee_id
输出结果如下图所示：
- 聚合后，示例语句如下所示：
SELECT department_id, SUM (salary) AS total_sal, SUM (bonus) AS total_bonus
FROM (SELECT e.employee_id,e.employee_name,e.salary,e.department_id,e.salary
* CASE
WHEN eb.TYPE = 1 THEN .1
WHEN eb.TYPE = 2 THEN .2
ELSE .3
END
AS bonus
FROM dmhr.employee e, dmhr.emp_bonus eb
WHERE e.employee_id = eb.employee_id) y
GROUP BY y.department_id
输出结果如下图所示：
聚合后奖金总额正确，工资总额不对，应该为 38560。示例语句如下所示：
SELECT SUM (SALARY)
FROM dmhr.employee
WHERE employee_id IN (1137,1138,1139,1140);
因员工陈国红有两次奖励，其工资重复计算了两次。正确的做法是先把奖金按员工汇总（先聚合），再与员工表关联。示例语句如下所示：
SELECT department_id,
SUM (salary) AS total_sal,
SUM (bonus * salary) AS total_bonus
FROM dmhr.employee e,
( SELECT employee_id,
SUM (
CASE
WHEN TYPE = 1 THEN .1
WHEN TYPE = 2 THEN .2
ELSE .3
END)
AS bonus
FROM dmhr.emp_bonus
GROUP BY employee_id) eb
WHERE e.employee_id = eb.employee_id
GROUP BY e.department_id
输出结果如下图所示：
2.8 聚集与外连接
若要按部门返回员工工资总和与奖金总和，且奖金数据中只包含部门号为 105 的数据，使用 LEFT JOIN 可以实现。示例语句如下所示：
SELECT department_id,
SUM (salary) AS total_sal,
SUM (bonus * salary) AS total_bonus
FROM dmhr.employee e
LEFT JOIN
( SELECT employee_id,
SUM (
CASE
WHEN TYPE = 1 THEN .1
WHEN TYPE = 2 THEN .2
ELSE .3
END)
AS bonus
FROM dmhr.emp_bonus
GROUP BY employee_id) eb
ON e.employee_id = eb.employee_id
GROUP BY e.department_id
ORDER BY 1;
输出结果如下图所示：
2.9 比较两个表差异的全外连接
准备两张表，表一：水果表 (fruits)；表二：颜色表 (colors)。示例语句如下所示：
--创建水果表
CREATE TABLE dmhr.fruits
(
fruit VARCHAR (12),
f_num NUMBER
);
INSERT INTO dmhr.fruits
VALUES ('APPLE', 1)
,('BANANA',2),('CHERRY',3),
('GRAPE',4),('ORANGE', 5),('STRAWBERRY',1);
COMMIT;
--创建颜色表
CREATE TABLE dmhr.colors
(
c_num NUMBER,
color VARCHAR (12)
);
INSERT INTO dmhr.colors
VALUES (1, 'RED')
,(2,'YELLOW'),(1,'GREEN'),(5,'ORANGE'),
(6,'WHITE');
COMMIT;
两张表按 f_num=c_cum 的条件做全外连接。示例语句如下所示：
SELECT f.*, c.*
FROM dmhr.fruits f FULL OUTER JOIN dmhr.colors c ON (f.f_num = c.c_num);
结果会显示哪些水果的颜色在颜色表里不存在，哪些颜色不存在对应同色的水果。
三、参考文档
更多 SQL 语言使用说明，请参考《DM8_SQL 语言使用手册》，手册位于数据库安装路径 /dmdbms/doc 文件夹下。如有其他问题，请在社区内咨询。
