SQL 多表联查

目录

1. 内联接（INNER JOIN）

2. 左外联接（LEFT JOIN）

3. 右外联接（RIGHT JOIN）

4. 全外联接（FULL JOIN）

5. 交叉联接（CROSS JOIN）

6. 自联接（SELF JOIN）

7. 左外连接排除内连接

8. 右外连接排除内连接

9. 全外连接排除内连接

总结

三表联查 

四表联查

1. 内连接（INNER JOIN）

语法：

SELECT 列名
FROM 表1
INNER JOIN 表2 ON 表1.列名 = 表2.列名;

示例：

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

2. 左外连接（LEFT JOIN）

语法：

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名;

示例：

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

3. 右外连接（RIGHT JOIN）

语法：

SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名;

示例：

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

4. 全外连接（FULL JOIN）

语法：

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名
UNION
SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名;

示例：

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
UNION
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

5. 交叉连接（CROSS JOIN）

语法：

SELECT 列名
FROM 表1
CROSS JOIN 表2;

示例：

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

6. 自连接（SELF JOIN）

自联接（Self Join）是指一个表与自身进行联接。这种操作通常用于表中记录之间的比较或关联。自联接可以帮助解决例如员工与其经理的关系、产品与产品之间的关系等问题。

自连接查询，可以是内连接查询，也可以是外连接查询。

语法：

SELECT 列名1, 列名2, ...
FROM 表名 AS 别名1
JOIN 表名 AS 别名2
ON 别名1.列名 = 别名2.列名
WHERE 条件;

示例：员工与经理自联接

假设我们有一个 employees 表，如下：

employees 表结构：

查询员工及其经理的姓名：

SELECT employees.name AS employee_name, manager.name AS manager_name
FROM employees
LEFT JOIN employees AS manager
ON employees.manager_id = manager.id;

7. 左外连接排除内连接

定义：返回左表中所有记录和右表中匹配的记录，但排除那些在右表中也有匹配的记录。

语法：

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名
WHERE 表2.列名 IS NULL;

示例：

SELECT employees.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.id IS NULL;

8. 右外连接排除内连接

定义：返回右表中所有记录和左表中匹配的记录，但排除那些在左表中也有匹配的记录。

语法：

SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名
WHERE 表1.列名 IS NULL;

示例：

SELECT departments.department_name
FROM departments
RIGHT JOIN employees ON departments.id = employees.department_id
WHERE employees.id IS NULL;

9. 全外连接排除内连接

定义：返回两个表的所有记录，但排除那些在两个表中都匹配的记录。

语法：

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名
WHERE 表2.列名 IS NULL
UNION
SELECT 列名
FROM 表2
RIGHT JOIN 表1 ON 表2.列名 = 表1.列名
WHERE 表1.列名 IS NULL;

示例：

SELECT employees.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.id IS NULL
UNION
SELECT departments.department_name
FROM departments
RIGHT JOIN employees ON departments.id = employees.department_id
WHERE employees.id IS NULL;

总结

• 内连接：仅返回两个表中匹配的记录。
• 左外连接：返回左表的所有记录和右表中匹配的记录，右表中没有匹配的记录显示为 NULL。
• 右外连接：返回右表的所有记录和左表中匹配的记录，左表中没有匹配的记录显示为 NULL。
• 全外连接：返回两个表的所有记录，通过 UNION 模拟。
• 交叉连接：返回两个表的笛卡尔积。
• 自连接：表与自身的联接，常用于记录间的比较。
• 左外连接排除内连接：返回左表中的记录，这些记录在右表中没有匹配项。
• 右外连接排除内连接：返回右表中的记录，这些记录在左表中没有匹配项。
• 全外连接排除内连接：返回两个表的所有记录，排除那些在两个表中都有匹配的记录。

联合查询 

联合查询（也称为集合操作）用于将多个 SELECT 查询的结果集合并在一起。

1.  UNION

• 功能：将两个或多个 SELECT 查询的结果集合并为一个结果集，并去除重复行。
• 语法：

  SELECT column1, column2 FROM table1
  UNION
  SELECT column1, column2 FROM table2;
  

  注意：所有 SELECT 查询必须具有相同数量的列，并且对应列的类型应兼容。

2.  UNION ALL

• 功能：将两个或多个 SELECT 查询的结果集合并为一个结果集，包括所有重复行。
• 语法：

  SELECT column1, column2 FROM table1
  UNION ALL
  SELECT column1, column2 FROM table2;
  

  注意：比 UNION 更高效，因为它不去重。

3. INTERSECT

• 功能：返回两个 SELECT 查询结果中的交集，即两个查询中都存在的行。
• 语法：

  SELECT column1, column2 FROM table1
  INTERSECT
  SELECT column1, column2 FROM table2;
  

• 注意：MySQL 8.0 及之前的版本不直接支持 INTERSECT，可以使用 INNER JOIN 来实现类似功能。

4. EXCEPT (或 MINUS)

• 功能：返回在第一个 SELECT 查询中存在但在第二个 SELECT 查询中不存在的行。
• 语法：

  SELECT column1, column2 FROM table1
  EXCEPT
  SELECT column1, column2 FROM table2;
  

  ​​​​​​​​​​​​​​注意：MySQL 8.0 及之前的版本不直接支持 EXCEPT。可以使用 LEFT JOIN 和 IS NULL 实现类似功能。

三表联查 

定义：将三个表通过指定的连接条件联接在一起，通常用于从多个表中获取相关数据。

语法格式：

SELECT 列名1, 列名2, ...
FROM 表1
JOIN 表2 ON 表1.列名 = 表2.列名
JOIN 表3 ON 表2.列名 = 表3.列名
WHERE 条件
GROUP BY 列名
HAVING 条件
ORDER BY 列名 [ASC|DESC]
LIMIT n OFFSET m;

假设我们有以下三个表：

• employees（员工表）：包含员工的基本信息。
• departments（部门表）：包含部门的信息。
• salaries（薪资表）：包含员工的薪资信息。

表结构示例：

employees 表：

departments 表：

salaries 表：

查询语句：

SELECT employees.name, departments.department_name, salaries.salary
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN salaries ON employees.id = salaries.employee_id;

/*
解释：

INNER JOIN departments ON employees.department_id = departments.id：

将 employees 表与 departments 表通过 department_id 和 id 列进行连接，提取部门名称。
INNER JOIN salaries ON employees.id = salaries.employee_id：

将 employees 表与 salaries 表通过 id 和 employee_id 列进行连接，提取薪资信息。
SELECT employees.name, departments.department_name, salaries.salary：

从连接后的结果中选择员工姓名、部门名称和薪资信息。
*/

四表联查

定义：将四个表通过指定的连接条件联接在一起，用于从多个表中获取更复杂的数据。

语法格式：

SELECT 列名1, 列名2, ...
FROM 表1
JOIN 表2 ON 表1.列名 = 表2.列名
JOIN 表3 ON 表2.列名 = 表3.列名
JOIN 表4 ON 表3.列名 = 表4.列名
WHERE 条件
GROUP BY 列名
HAVING 条件
ORDER BY 列名 [ASC|DESC]
LIMIT n OFFSET m;

四表联查示例

假设我们有以下四个表：

• employees（员工表）：包含员工的基本信息。
• departments（部门表）：包含部门的信息。
• salaries（薪资表）：包含员工的薪资信息。
• projects（项目表）：包含项目的信息。

表结构示例：

employees 表：

departments 表：

salaries 表：

projects 表：

查询语句：

SELECT employees.name, departments.department_name, salaries.salary, projects.project_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN salaries ON employees.id = salaries.employee_id
INNER JOIN projects ON departments.id = projects.department_id;

/*
解释：

INNER JOIN departments ON employees.department_id = departments.id：

将 employees 表与 departments 表通过 department_id 和 id 列进行连接，提取部门名称。
INNER JOIN salaries ON employees.id = salaries.employee_id：

将 employees 表与 salaries 表通过 id 和 employee_id 列进行连接，提取薪资信息。
INNER JOIN projects ON departments.id = projects.department_id：

将 departments 表与 projects 表通过 department_id 和 department_id 列进行连接，提取项目名称。
SELECT employees.name, departments.department_name, salaries.salary, projects.project_name：

从连接后的结果中选择员工姓名、部门名称、薪资信息和项目名称。
*/