当前位置：首页 > article >正文

mysql学习教程，从入门到精通，SQL 联表查询(Join)（21）

article 2024/11/14 9:43:19

1、SQL 联表查询(Join)

在编写SQL联表查询（Join）时，我们首先要明确查询的目的，即我们需要从哪些表中提取数据，以及这些表之间如何关联。以下是一个简单的示例，假设我们有两个表：employees（员工表）和departments（部门表）。我们的目标是查询出每个员工的姓名、部门ID以及他们所属的部门名称。

1.1、表结构

employees 表

employee_id INT：员工ID
name VARCHAR：员工姓名
department_id INT：部门ID
departments 表
department_id INT：部门ID
department_name VARCHAR：部门名称

1.2、SQL 联表查询

为了实现我们的目标，我们可以使用 INNER JOIN（内连接），因为它会返回两个表中匹配的记录。如果员工所属的部门在 departments 表中存在，则该员工的信息及其部门名称将被返回。

SELECT  
    e.name AS employee_name,  
    e.department_id,  
    d.department_name  
FROM  
    employees e  
INNER JOIN departments d ON e.department_id = d.department_id;

1.3、解释

SELECT 语句：我们选择了 employees 表中的 name 列（重命名为 employee_name），department_id 列，以及 departments 表中的 department_name 列。
FROM 语句：指定了查询的起始表为 employees，并且我们为这个表设置了一个别名 e，以便在后续的查询中简化引用。
INNER JOIN 语句：通过 INNER JOIN 语句将 employees 表与 departments 表连接起来。连接条件是 e.department_id = d.department_id，即 employees 表中的 department_id 列的值必须等于 departments 表中的 department_id 列的值。同样，我们也为 departments 表设置了一个别名 d。
ON 子句：指定了 JOIN 的条件，即如何连接这两个表。在这个例子中，条件是员工所属的部门ID与部门表中的部门ID相匹配。
通过这种方式，我们可以得到每个员工及其所属部门的名称，结果集将只包含那些在 departments 表中有对应部门ID的 employees 表中的记录。如果你想要包括那些在 departments 表中没有对应部门ID的 employees 表中的记录（例如，那些尚未分配部门的员工），你可以使用 LEFT JOIN 替代 INNER JOIN。
当然可以，以下是一些具体的SQL联表查询（Join）案例，这些案例将展示不同类型的JOIN操作（如INNER JOIN、LEFT JOIN、RIGHT JOIN等）在实际应用中的使用。

1.4、案例一：INNER JOIN（内连接）

场景：查询所有已分配部门的员工及其部门名称。
表结构：

employees 表：包含员工ID、员工姓名和部门ID。
departments 表：包含部门ID和部门名称。
SQL查询：

SELECT  
    e.employee_id,  
    e.name AS employee_name,  
    d.department_name  
FROM  
    employees e  
INNER JOIN departments d ON e.department_id = d.department_id;

结果：返回所有在 departments 表中有对应部门ID的 employees 表中的记录，包括员工ID、员工姓名和部门名称。

1.5、案例二：LEFT JOIN（左连接）

场景：查询所有员工及其部门名称，包括那些尚未分配部门的员工。

SQL查询：

SELECT  
    e.employee_id,  
    e.name AS employee_name,  
    d.department_name  
FROM  
    employees e  
LEFT JOIN departments d ON e.department_id = d.department_id;

结果：返回 employees 表中的所有记录，对于在 departments 表中没有对应部门ID的员工，其部门名称将显示为NULL。

1.6、案例三：RIGHT JOIN（右连接）

注意：在实际应用中，RIGHT JOIN 较少使用，因为可以通过交换表的位置和使用LEFT JOIN来达到相同的效果。但为了完整性，这里还是给出一个例子。
场景：查询所有部门及其对应的员工姓名（如果有的话）。
SQL查询：

SELECT  
    d.department_id,  
    d.department_name,  
    e.name AS employee_name  
FROM  
    departments d  
RIGHT JOIN employees e ON d.department_id = e.department_id;

结果：返回 departments 表中的所有记录，对于在 employees 表中没有对应员工的部门，其员工姓名将显示为NULL。但请注意，这个查询在逻辑上与将 employees 和 departments 表的位置互换并使用LEFT JOIN是等效的。

1.7、案例四：FULL JOIN（全连接）

注意：并非所有数据库系统都支持FULL JOIN，但可以通过UNION操作结合LEFT JOIN和RIGHT JOIN来模拟。
场景：查询所有员工及其部门名称，包括那些尚未分配部门的员工和那些没有员工的部门。
模拟FULL JOIN的SQL查询：

SELECT  
    e.employee_id,  
    e.name AS employee_name,  
    d.department_name  
FROM  
    employees e  
LEFT JOIN departments d ON e.department_id = d.department_id  
UNION  
SELECT  
    e.employee_id,  
    e.name AS employee_name,  
    d.department_name  
FROM  
    employees e  
RIGHT JOIN departments d ON e.department_id = d.department_id  
WHERE e.employee_id IS NULL; -- 排除LEFT JOIN中已经包含的记录

但请注意，上面的查询实际上并不完全正确，因为它会尝试从RIGHT JOIN中选择NULL的 employee_id，这在大多数情况下并不是我们想要的。一个更准确的模拟FULL JOIN的方法是分别执行LEFT JOIN和RIGHT JOIN，并确保RIGHT JOIN部分只选择那些在LEFT JOIN中未出现的记录（这通常涉及到子查询或临时表）。然而，为了简洁起见，这里不展示完整的模拟过程。
在实际应用中，如果数据库支持FULL JOIN，可以直接使用它，如下所示：

SELECT  
    e.employee_id,  
    e.name AS employee_name,  
    d.department_name  
FROM  
    employees e  
FULL JOIN departments d ON e.department_id = d.department_id;