关于数据库多表查询内连接、外连接知识详解

最新推荐文章于 2025-03-03 18:55:34 发布

LaoYuanPython

最新推荐文章于 2025-03-03 18:55:34 发布

阅读量2.3k

点赞数 23

分类专栏：老猿Python 国产信创之光文章标签：数据库内连接外连接语法糖 JOIN

本文链接：https://blog.csdn.net/LaoYuanPython/article/details/143460918

版权

老猿Python 同时被 2 个专栏收录

1144 篇文章

订阅专栏

国产信创之光

32 篇文章

订阅专栏

☞ ░ 前往老猿Python博客 ░ https://blog.csdn.net/LaoYuanPython

在这里插入图片描述

一、前言

数据库多表查询的连接以前就知道两个或更多表连接之后关联查询数据，但没有系统的学习过，现在学习了下相关知识，发现自己以前了解的只是其中很小的一部分，今边学习边总结有关表之间连接查询的知识。

为了演示，相关查询用到了如下数据库示例的相关表：

雇员表EMPLOYEE
部门表DEPARTMENT

其中DEPARTMENT_ID、manager_id在表EMPLOYEE、DEPARTMENT中都存在。

二、连接概述

根据连接类型分为内连接（inner join）和外连接（outer join）。

内连接：返回两张表中满足连接条件的记录。
外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接；两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接称为全外连接。即 Left outer join, right outer join, full outer join

三、内连接示例

3.1、示例1：显示内连接和隐式内连接

如下两个SQL都使用了内连接：

 select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(*) as empnum
 from employee a,DEPARTMENT b
 where a.DEPARTMENT_ID = b.DEPARTMENT_ID
 group by a.DEPARTMENT_ID,b.DEPARTMENT_NAME
 having count(*) >=30
 order by empnum desc;

select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(*) as empnum
 from employee a join DEPARTMENT b
 on a.DEPARTMENT_ID = b.DEPARTMENT_ID
 group by a.DEPARTMENT_ID,b.DEPARTMENT_NAME
 having count(*) >=30
 order by empnum desc;

这两个SQL语句在功能上是相似的，它们都旨在查询每个部门的ID、名称以及员工数量，并按员工数量降序排列，同时只包括那些员工数量至少为30的部门。然而，它们在语法和性能上有一些关键的不同：

连接类型：
第1个SQL语句使用了隐式的内连接，通过在WHERE子句中指定连接条件（a.DEPARTMENT_ID = b.DEPARTMENT_ID）。这种写法在功能上等同于INNER JOIN，但在可读性和维护性上不如显式的JOIN语句。
第2个SQL语句使用了JOIN关键字，这是一个显式的内连接（INNER JOIN）。使用JOIN关键字的语句通常被认为更易于阅读和维护，因为它清晰地表明了连接的意图和类型。
性能：
在大多数现代数据库管理系统中，这两种写法在性能上应该是相似的，因为数据库优化器会将它们优化为相同的执行计划。然而，显式的JOIN语句有时可以提供更好的性能，尤其是在复杂的查询中，因为它们允许数据库优化器更有效地处理连接。
扩展性：
使用JOIN关键字的语句更容易扩展，例如，如你想要添加更多的连接条件或者更改连接类型（如LEFT JOIN、RIGHT JOIN等），使用JOIN关键字的写法会更加直观。
错误的可能性：
在使用WHERE子句进行连接的第1个SQL语句中，如果不小心在WHERE子句中添加了其他条件，可能会意外地改变查询的语义。而使用JOIN关键字的语句则更清晰地区分了连接条件和其他过滤条件。

总的来说，第2个SQL语句（使用JOIN关键字）在可读性、维护性和扩展性方面更胜一筹，而第1个SQL语句（使用WHERE子句进行连接）在功能上是等效的，但在现代SQL实践中，推荐使用显式的JOIN语句。

3.2、示例2：两个等价显示内连接

如下两个SQL都使用了内连接：

select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(*) as empnum
 from employee a  join DEPARTMENT b
 on a.DEPARTMENT_ID = b.DEPARTMENT_ID
 group by a.DEPARTMENT_ID,b.DEPARTMENT_NAME
 having count(*) >=30
 order by empnum desc;

select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(*) as empnum
from employee a inner join DEPARTMENT b
on a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by a.DEPARTMENT_ID,b.DEPARTMENT_NAME
having count(*) >=30
order by empnum desc;

这二者没什么本质区别，在 SQL 标准中，INNER JOIN 和 JOIN 是等价的，它们都表示内连接（inner join），即只返回两个表中匹配的行。这两个查询都会产生相同的结果。它们都从 employee 表和 DEPARTMENT 表中选择部门ID和部门名称，计算每个部门的员工数量，只包括那些员工数量至少为30的部门，并且按照员工数量降序排列。

在实际使用中，INNER JOIN 和 JOIN 可以互换使用，选择哪一个主要取决于个人偏好或者团队的编码规范。有些开发者可能更喜欢使用 INNER JOIN 来明确表示这是一个内连接操作，而有些开发者可能觉得 JOIN 更简洁。在功能上，它们是完全相同的。

3.3、示例3：NATURAL JOIN

NATURAL JOIN 是 SQL 中的一种连接类型，它是一种特殊的内连接（INNER JOIN），用于自动匹配两个表中具有相同名称的列。当使用 NATURAL JOIN 时，数据库会自动寻找两个表中名称相同的列，并使用这些列作为连接条件。

下面是一个NATURAL JOIN示例：

select department_id deptid, b.DEPARTMENT_NAME,
 count(a.employee_id) empnum,
 sum(a.salary) sum_salary,
 min(a.salary) min_salary,
 max(a.salary) max_salary,
 avg(a.salary) avg_salary
 from employee a NATURAL JOIN DEPARTMENT b
 where (DEPARTMENT_ID BETWEEN 101 and 201 ) 
group by DEPARTMENT_ID, b.DEPARTMENT_NAME
having avg(a.salary) >=10000
order by empnum desc;

NATURAL JOIN 会自动使用 employee 表和 DEPARTMENT 表中名称相同的列作为连接条件。在这个例子中，employee 表和 DEPARTMENT 表都有DEPARTMENT_ID、manager_id 的列，NATURAL JOIN 就会使用这2个列来连接两个表，且在select后面对查询的department_id不用指定表名，而在不是NATURAL JOIN的连接时，select后面对于查询的连接字段必须显示指定表名。

使用 NATURAL JOIN 的优点是代码更简洁，不需要显式指定连接条件。但是，它也有一些缺点：

不明确：NATURAL JOIN 会自动匹配所有相同名称的列，这可能会导致一些不期望的连接条件，特别是当两个表中有多个相同名称的列时。
可维护性差：当表结构发生变化时，NATURAL JOIN 可能会导致连接条件发生变化，这可能会影响查询结果。
性能：在某些情况下，NATURAL JOIN 可能不如显式指定连接条件的查询性能好，因为数据库优化器可能需要更多的工作来确定连接条件。

因此，尽管 NATURAL JOIN 可以简化代码，但在实际开发中，推荐使用显式的 JOIN 语句（如 INNER JOIN）并明确指定连接条件，以提高代码的可读性、可维护性和性能。

3.4、示例4：连接字段名相同的高效写法

内连接使用join…on来指定两个表连接相等的条件，这儿允许两个表之间的字段名含义相同但列名不同，对于列名相同的方式，还有中简单写法，就是使用join…using语句。示例：

select department_id deptid, b.DEPARTMENT_NAME,
 count(a.employee_id) empnum,
 sum(a.salary) sum_salary,
 min(a.salary) min_salary,
 max(a.salary) max_salary,
 avg(a.salary) avg_salary
from employee a JOIN DEPARTMENT b 
using (department_id, manager_id)
 where (DEPARTMENT_ID BETWEEN 101 and 201 ) --and a.SALARY >= 10000
group by DEPARTMENT_ID, b.DEPARTMENT_NAME
having avg(a.salary) >=10000
order by empnum desc;

在 SQL 中，JOIN … USING … 子句是一种特殊的连接方式，它用于在两个表之间进行连接，基于两个表中具有相同名称和数据类型的列。当使用 USING 子句时，不需要在 ON 子句中显式指定连接条件，因为 USING 子句会自动匹配两个表中同名的列。

上述语句中的USING (department_id, manager_id) 表示 employee 表和 DEPARTMENT 表将通过 department_id 和 manager_id 这两个列进行连接。这意味着两个表中都必须有名为 department_id 和 manager_id 的列，并且它们的数据类型必须相同。

使用 USING 子句的优点是：

简化语法：不需要在 ON 子句中重复列名，使 SQL 语句更简洁。
避免歧义：当两个表中有多个同名列时，USING 子句可以明确指定哪些列用于连接。

然而，需要注意的是，如果两个表中的列名不完全相同，或者列的数据类型不匹配，USING 子句将无法工作，并且会抛出错误。在这种情况下，需要使用 ON 子句并显式指定连接条件。

四、左外连接

左外连接（LEFT OUTER JOIN）是 SQL 中的一种连接操作，用于结合两个表的数据。在左外连接中，结果集会包括左表（指定LEFT OUTER JOIN时的第一个表）的所有行，即使右表（第二个表）中没有匹配的行。如果左表的一行在右表中没有匹配的行，则结果集的右表部分会包含 NULL 值。

语法：

SELECT column_list
FROM table1
LEFT OUTER JOIN table2
ON table1.common_field = table2.common_field;

4.1、示例1：使用标准左外关联语法left join

构建一个左表中的数据在右表中无关联数据的记录，这个DEPARTMENT_ID为NULL的记录将在DEPARTMENT没有对应记录：


update employee a 
 set a.DEPARTMENT_ID = null
 where a.DEPARTMENT_ID = 101;
 
 commit;

进行左外连接查询：

select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(a.employee_id) as empnum
from employee a LEFT OUTER JOIN  DEPARTMENT b
on a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by a.DEPARTMENT_ID, b.DEPARTMENT_NAME
order by a.DEPARTMENT_ID, b.DEPARTMENT_NAME

下面红框标记的NULL开头的行就是左外连接比内连接多出的结果记录：
在这里插入图片描述

4.2、示例2：使用语法糖形式的左外连接

语法糖（Syntactic sugar）是计算机科学中的一个术语，指的是一种语法结构，它允许程序员以一种更便捷、更易读或者更符合直觉的方式来表达代码，而这种语法结构在编译或解释后可以被转换成另一种等效的表达形式。

在SQL语法中，可以使用 (+) 来表示单向外连接：

左外连接（LEFT OUTER JOIN）：(+) 放在右表的列上，表示如果左表的行在右表中没有匹配的行，则结果集中对应的右表部分会包含 NULL 值
右外连接（RIGHT OUTER JOIN）：可以通过将 (+) 放在左表的列上来实现类似的效果

之所以左外连接“(+)” 放在右表的列上，表示右表比实际数据多出了NULL的数据，右外连接则反之。

左外连接（LEFT OUTER JOIN）的语法糖：(+) 放在右表的列上，表示如果左表的行在右表中没有匹配的行，则结果集中对应的右表部分会包含 NULL 值。

 select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(a.employee_id) as empnum
 from employee a, DEPARTMENT b
where a.DEPARTMENT_ID = b.DEPARTMENT_ID(+)
 group by a.DEPARTMENT_ID, b.DEPARTMENT_NAME
 order by a.DEPARTMENT_ID, b.DEPARTMENT_NAME

执行结果：
在这里插入图片描述

在 SQL 中，(+) 符号用于指定外连接条件，在连接条件中使用 (+) 时，它表示一个单向的外连接，这意味着查询将返回左表（在本例中是 mployee 表，别名为 a）的所有行，即使右表（DEPARTMENT 表，别名为 b）中没有匹配的行。

如果去掉表示左连接的“(+)”，则查询结果会变成如下：
在这里插入图片描述

四、右外连接

右外连接（RIGHT OUTER JOIN）返回两个表中与连接条件匹配的所有行，包括右表中不匹配的行。在右外连接中，结果集会包含右表（指定RIGHT OUTER JOIN时的第二个表）的所有行，即使左表中没有匹配的行。如果右表的一行在左表中没有找到匹配的行，则结果集中对应的左表部分会包含 NULL 值。

语法：

SELECT column_list
FROM table1
RIGHT OUTER JOIN table2
ON table1.common_field = table2.common_field;

4.1、示例1：使用标准右外连接语法right join

构建一个左表中的数据在右表中无关联数据的记录，这个DEPARTMENT_ID为888888的记录将在employee没有对应记录：

insert into DEPARTMENT(DEPARTMENT.DEPARTMENT_ID,
DEPARTMENT.DEPARTMENT_NAME)
values(888888,'Test');
commit;

进行右外关联查询：

 select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(a.employee_id) as empnum
 from employee a right join DEPARTMENT b
 on a.DEPARTMENT_ID = b.DEPARTMENT_ID
group by a.DEPARTMENT_ID, b.DEPARTMENT_NAME
order by  a.DEPARTMENT_ID;

执行截图：
在这里插入图片描述
可以看到，右表中DEPARTMENT有2条记录在左表employee中无对应记录，第一条记录是因为做左外连接update示例时将左表数据更新为NULL导致的，其对应的右表记录当然在左表中也不存在，如果select中显示是右表的值，则该条查询结果的第一个字段不为NULL。

4.2、示例1：使用语法糖形式

右外连接（RIGHT OUTER JOIN）的语法糖：(+) 放在左表的列上，表示如果右表的行在左表中没有匹配的行，则结果集中对应的左表部分会包含 NULL 值。

select a.DEPARTMENT_ID, b.DEPARTMENT_id, b.DEPARTMENT_NAME,
count(a.employee_id) as empnum
 from employee a, DEPARTMENT b
where a.DEPARTMENT_ID(+) = b.DEPARTMENT_ID
 group by a.DEPARTMENT_ID, b.DEPARTMENT_id, b.DEPARTMENT_NAME
 order by a.DEPARTMENT_id,b.DEPARTMENT_ID;

在这里插入图片描述

五、全外连接

全外连接（FULL OUTER JOIN）结合了左外连接和右外连接的功能，返回两个表中的所有记录，无论它们是否匹配。如果某条记录在其中一个表中没有匹配的记录，那么结果集中相应的字段将显示为 NULL。即全外连接会展示左表的所有数据加上右表的所有数据，同时包括两个表中匹配的关联数据。如果左表或右表中的数据在另一表中没有匹配项，则对应的字段会用 NULL 填充。

全外连接语法：

SELECT column_list
FROM table1
FULL  JOIN table2
ON table1.common_field = table2.common_field;

示例：

select a.DEPARTMENT_ID, b.DEPARTMENT_NAME, count(a.employee_id) as empnum
 from employee a full  join DEPARTMENT b
 on a.DEPARTMENT_ID = b.DEPARTMENT_ID
 where a.DEPARTMENT_ID<200 or a.DEPARTMENT_ID is null
group by a.DEPARTMENT_ID, b.DEPARTMENT_NAME;