sql中的多表查询

在SQL中，多表查询用于从多个表中组合数据，常见的方法包括 ​连接查询（JOIN）​​ 和 ​子查询。以下是详细说明和示例：

一、连接查询（JOIN）

通过关联字段将多个表的数据合并，分为以下几种类型：

1. 内连接（INNER JOIN）

• ​作用​：只返回两表中匹配的行。
• ​语法​：

  SELECT 列名
  FROM 表1
  INNER JOIN 表2 ON 表1.关联字段 = 表2.关联字段;

• ​示例​：

  — 查询员工及其所属部门信息（仅匹配的记录）
  SELECT e.name, d.department_name
  FROM employees e
  INNER JOIN departments d ON e.dept_id = d.id;

2. 左外连接（LEFT JOIN / LEFT OUTER JOIN）

• ​作用​：返回左表所有行，右表无匹配则填充NULL。
• ​语法​：

  SELECT 列名
  FROM 表1
  LEFT JOIN 表2 ON 表1.关联字段 = 表2.关联字段;

• ​示例​：

  — 查询所有员工及部门信息（包括无部门的员工）
  SELECT e.name, d.department_name
  FROM employees e
  LEFT JOIN departments d ON e.dept_id = d.id;

3. 右外连接（RIGHT JOIN / RIGHT OUTER JOIN）

• ​作用​：返回右表所有行，左表无匹配则填充NULL。
• ​语法​：

  SELECT 列名
  FROM 表1
  RIGHT JOIN 表2 ON 表1.关联字段 = 表2.关联字段;

• ​示例​：

  — 查询所有部门及员工信息（包括无员工的部门）
  SELECT e.name, d.department_name
  FROM employees e
  RIGHT JOIN departments d ON e.dept_id = d.id;

4. 全外连接（FULL JOIN / FULL OUTER JOIN）

• ​作用​：返回两表所有行，无匹配则填充NULL（部分数据库如MySQL不直接支持，需用UNION模拟）。
• ​语法​（以MySQL为例）：

  SELECT 列名
  FROM 表1
  LEFT JOIN 表2 ON 表1.关联字段 = 表2.关联字段
  UNION
  SELECT 列名
  FROM 表1
  RIGHT JOIN 表2 ON 表1.关联字段 = 表2.关联字段;

5. 交叉连接（CROSS JOIN）

• ​作用​：返回两表的笛卡尔积（所有可能的组合）。
• ​语法​：

  SELECT 列名
  FROM 表1
  CROSS JOIN 表2;

• ​注意​：结果行数 = 表1行数 × 表2行数，需谨慎使用。

二、子查询

在查询中嵌套另一个查询，分为以下场景：

1. WHERE子句中的子查询

• ​示例​：查询工资高于平均工资的员工。

  SELECT name, salary
  FROM employees
  WHERE salary > (SELECT AVG(salary) FROM employees);

2. FROM子句中的子查询（派生表）

• ​示例​：将子查询结果作为临时表使用。

  SELECT dept_avg.dept_id, dept_avg.avg_salary
  FROM (SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id) AS dept_avg
  WHERE dept_avg.avg_salary > 5000;

3. SELECT子句中的子查询（标量子查询）

• ​示例​：查询员工姓名及其部门名称（通过子查询获取部门名）。

  SELECT
  e.name,
  (SELECT d.department_name FROM departments d WHERE d.id = e.dept_id) AS department_name
  FROM employees e;

三、多表连接的注意事项

四、实际案例

​场景​：查询“销售部门”的所有员工姓名及其职位。

— 方法1：内连接
SELECT e.name, e.position
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE d.department_name = '销售部';

— 方法2：子查询
SELECT name, position
FROM employees
WHERE dept_id = (SELECT id FROM departments WHERE department_name = '销售部');

通过灵活组合连接和子查询，可以高效处理复杂的数据关联需求。根据场景选择合适的方法，并注意性能优化！