多表查询（MYSQL）

1. 概念

多表查询是指从多张表中查询数据。通过连接多个表，可以获取跨表的数据信息，从而满足复杂的查询需求。

2. 笛卡尔积

在多表查询中，笛卡尔积是指两个表的所有组合情况。例如，表A有m行，表B有n行，则笛卡尔积的结果会有m×n行。笛卡尔积通常是无效的，需要通过条件过滤来避免。

3. 多表查询的分类

多表查询主要分为以下几种类型：

3.1 内连接（INNER JOIN）

内连接查询返回两个表的交集部分，即满足连接条件的记录。

SELECT 字段列表
FROM 表1
INNER JOIN 表2
ON 条件;

3.2 左外连接（LEFT JOIN）

左外连接返回左表的所有记录，以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则返回NULL。

SELECT 字段列表
FROM 表1
LEFT JOIN 表2
ON 条件;

3.3 右外连接（RIGHT JOIN）

右外连接返回右表的所有记录，以及左表中满足连接条件的记录。如果左表中没有匹配的记录，则返回NULL。

SELECT 字段列表
FROM 表1
RIGHT JOIN 表2
ON 条件;

3.4 子查询（Subquery）

子查询是指在一个查询语句中嵌套另一个查询语句。子查询可以分为单行子查询和多行子查询。

• 单行子查询：返回单行单列的结果。

• 多行子查询：返回多行单列或多行多列的结果。

-- 单行子查询示例
SELECT 字段列表
FROM 表1
WHERE 字段 = (SELECT 字段 FROM 表2 WHERE 条件);

-- 多行子查询示例
SELECT 字段列表
FROM 表1
WHERE 字段 IN (SELECT 字段 FROM 表2 WHERE 条件);

4. 示例

假设我们有两个表：employees（员工表）和departments（部门表），表结构如下：

• employees 表：

  • id（员工ID）

  • name（员工姓名）

  • department_id（部门ID）

• departments 表：

  • id（部门ID）

  • department_name（部门名称）

4.1 内连接查询

查询员工及其所在部门的名称：

SELECT e.name AS 员工姓名, d.department_name AS 部门名称
FROM employees e
INNER JOIN departments d
ON e.department_id = d.id;

4.2 左外连接查询

查询所有员工及其所在部门的名称（即使某些员工没有分配部门）：

SELECT e.name AS 员工姓名, d.department_name AS 部门名称
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;

4.3 子查询查询

查询部门名称为“技术部”的员工信息：

SELECT *
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE department_name = '技术部');