【MySQL系列】MySQL复合查询的学习 _ 多表查询 | 自连接 | 子查询 | 合并查询

-- 多表查询
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
 
-- 自连接查询
SELECT m.last_name AS manager_name, e.last_name AS employee_name
FROM employees m
JOIN employees e ON m.employee_id = e.manager_id;
 
-- 子查询
SELECT employee_id, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');
 
-- 合并查询结果
SELECT last_name, department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT manager_name, NULL
FROM (SELECT manager_id, last_name AS manager_name FROM employees) AS managers
WHERE manager_id IS NOT NULL;

这个例子展示了如何在MySQL中执行多表查询、自连接查询、子查询以及如何合并查询结果的基本操作。这些操作在复杂数据分析和查询中非常有用。