【示例】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不直接支持FULL JOIN)
-- 使用UNION来模拟全外连接
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;
 
-- 子查询示例
-- 需求：查询所有员工及其所在部门的员工数量
SELECT e.name AS '员工名字', (
  SELECT COUNT(*)
  FROM employees e2
  WHERE e2.department_id = d.id
) AS '部门员工数量'
FROM employees e
JOIN departments d ON e.department_id = d.id;

这个例子展示了如何在MySQL中使用多表查询，包括内连接、左连接、右连接以及全外连接（通过UNION模拟），并包括了一个子查询的例子。这些查询可以帮助开发者理解和应用多表查询的复杂性，以及如何在实际应用中解决特定的数据库问题。