MySQL数据库中的多表查询/连接查询操作

-- 假设我们有两个表：employees 和 departments
-- 需求：查询所有员工及其所在部门的名称
 
-- 内连接查询
SELECT e.name AS '员工名字', d.name AS '部门名字'
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
 
-- 左连接查询
SELECT e.name AS '员工名字', d.name AS '部门名字'
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
 
-- 右连接查询
SELECT e.name AS '员工名字', d.name AS '部门名字'
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
 
-- 全外连接查询 (MySQL不直接支持全外连接，但可以通过左右连接的并集实现)
SELECT e.name AS '员工名字', d.name AS '部门名字'
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name AS '员工名字', d.name AS '部门名字'
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

这个例子展示了如何在MySQL中使用INNER JOIN, LEFT JOIN, RIGHT JOIN以及UNION来进行多表查询。每种连接查询都有其特定的应用场景，开发者需要根据实际需求选择合适的连接类型。