LeetCode 数据库题库解析之 175. 组合两个表

大家好，我是只谈技术不剪发的 Tony 老师。今天给大家解析一下 LeetCode 数据库题库中的第 175 题：组合两个表。

首先，我们会对题目进行解析并给出 MySQL、Oracle 以及 SQL Server 三种数据库的实现方法；然后，我们还会针对相关的 SQL 知识进行扩展介绍。

题目来源：力扣（LeetCode）。

题目描述

表1：Person

+-------------+---------+
| 列名        | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2：Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

建表脚本和示例数据：

Create table Person (PersonId int, FirstName varchar(255), LastName varchar(255));
Create table Address (AddressId int, PersonId int, City varchar(255), State varchar(255));
Truncate table Person;
insert into Person (PersonId, LastName, FirstName) values ('1', 'Wang', 'Allen');
Truncate table Address;
insert into Address (AddressId, PersonId, City, State) values ('1', '2', 'New York City', 'New York');

题目解析

查询结果要求返回的字段中 FirstName 和 LastName 来自 Person 表，City 和 State 来自 Address 表；显然我们需要使用连接（Join）查询关联这两个表。

从表结构可以看出， Address 表中的 PersonId 字段对应的是 Person 表上的 PersonId；同时考虑到并不是每个人都有地址信息，所以我们应该使用外连接确保会返回所有的人员，而不是使用内连接。

MySQL 实现

MySQL 可以使用 left join 或者 right join 实现题目要求：

select p.FirstName, p.LastName, a.City, a.State
from Person p
left join Address a
on p.PersonId = a.PersonId;

FirstName|LastName|City|State|
---------|--------|----|-----|
Allen    |Wang    |    |     |

从查询结果可以看出，虽然 Allen.Wang 没有地址信息，仍然返回了姓名。当然，我们也可以使用 right join，只需要将两个表的顺序交换一下就可以了。

Oracle 实现

首先，Oracle 可以使用像 MySQL 一样的 SQL 语句实现题目要求。

其次，Oracle 还支持专有的外连接语法（ANSI SQL/86 标准）：

select p.FirstName, p.LastName, a.City, a.State
from Person p, Address a
where p.PersonId = a.PersonId(+);

FIRSTNAME|LASTNAME|CITY|STATE|
---------|--------|----|-----|
Allen    |Wang    |    |     |

WHERE 条件中等号右侧的 (+) 表示 Address 表中的数据为空时仍然返回 Person 中的记录，也就是左外连接。

⚠️这种 ANSI SQL/86 标准的外连接语法不具备移植性，不推荐使用。

SQL Server 实现

SQL Server 可以使用像 MySQL 一样的 SQL 语句实现题目要求。

知识扩展

SQL 连接查询可以从两个或多个表中获取关联数据，SQL 中的连接查询主要包括以下类型：

• INNER JOIN，内连接；
• LEFT OUTER JOIN，左外连接；
• RIGHT OUTER JOIN，右外连接；
• FULL OUTER JOIN，全外连接；
• CROSS JOIN，交叉连接；
• NATURAL JOIN，自然连接;
• Self Join，自连接；
• Anti Join，反连接；
• Semi Join，半连接。

其中，左外连接、右外连接以及全外连接统称为外连接（OUTER JOIN）。

连接查询中的 ON 子句与 WHERE 子句类似，可以支持各种条件运算符（=、>=、!=、BETWEEN 等）‘但最常用的是等值连接（=），我们主要介绍这种条件的连接查询。

内连接

内连接使用关键字INNER JOIN表示，也可以简写成JOIN； 内连接只返回两个表中匹配的数据行。参考以下示意图（基于两个表的 id 进行连接）：

其中，id = 1 和 id = 3 是两个表中匹配的数据，因此内连接返回了 2 行记录。

左外连接

左外连接使用关键字LEFT OUTER JOIN表示，也可以简写成LEFT JOIN； 左外连接返回左表中所有的数据行；对于右表中的数据，如果没有匹配的值就返回空值。参考以下示意图（基于两个表的 id 进行连接）：

其中，id = 2 在 table1 中存在，在 table2 中不存在；左外连接仍然会返回该记录，只是对于 table2 中的列，返回的是空值。

右外连接

右外连接使用关键字RIGHT OUTER JOIN表示，也可以简写成RIGHT JOIN； 右外连接返回右表中所有的数据行；对于左表中的数据，如果没有匹配的值就返回空值。参考以下示意图（基于两个表的 id 进行连接）：

右外连接和左外连接可以互相替换，以下语句是等价的：

table1 RIGHT JOIN table2
table2 LEFT JOIN table1

全外连接

全外连接使用关键字FULL OUTER JOIN表示，也可以简写成FULL JOIN。全外连接等效于左外连接加上右外连接，返回左表和右表中所有的数据行；对于左表和右表中的数据，如果没有匹配的值就返回空值。参考以下示意图（基于两个表的 id 进行连接）：

需要注意的是，对于重复的行（id = 1 和 id = 3），只返回一次记录。

⚠️MySQL 目前不支持全外连接。

对于 Oracle 和 SQL Server 而言，Person 表和 Address 表全外连接查询的结果如下：

select p.FirstName, p.LastName, a.City, a.State
from Person p
full join Address a
on p.PersonId = a.PersonId;

FIRSTNAME|LASTNAME|CITY         |STATE   |
---------|--------|-------------|--------|
         |        |New York City|New York|
Allen    |Wang    |             |        |

交叉连接

交叉连接也称为笛卡尔积（Cartesian product），使用关键字CROSS JOIN表示。两个表的笛卡尔积相当于一个表的所有行和另一个表的所有行两两组合，结果的数量为两个表的行数相乘。假如第一个表有 100 行，第二个表有 200 行，它们的交叉连接将会产生 100 × 200 = 20000 行结果。参考以下示意图（基于两个表的 id 进行连接）：

自然连接

自然连接并不是一种新的连接方式，而是特殊情况下的简写语法。如果我们使用两个表中的所有同名字段进行等值连接，可以使用NATURAL JOIN简化查询语句。例如：

select *
from Person p
natural join Address a;

Person 表和 Address 表拥有 1 个同名字段 PersonId，因此上面的查询等价于使用 PersonId 进行等值连接的内查询。

⚠️SQL Server 目前不支持自然连接语法。

自连接

自连接（Self join）是一种特殊的连接，它是指连接操作符的两边都是同一个表，即把一个表和它自己进行连接。

自连接本质上并没有什么特殊之处，主要用于处理那些对自己进行了引用的表。例如员工表中的经理字段指向了员工表自身的其他员工编号，通过自连接可以获取上下级的管理关系。

半连接

半连接用于返回左表中与右表至少匹配一次的数据行，通常体现为 EXISTS 或者 IN 子查询。半连接的示意图如下：

半连接只会返回左表中的数据，右表只用于条件判断。另外，即使右表中存在多个匹配的数据，左边中的数据只返回一次。半连接通常用于存在性判断，例如哪些顾客购买了产品，而不需要知道他们购买的具体产品。

以下语句用于查找拥有地址信息的人员：

select *
from Person p
where exists(
  select 1
  from Address a 
  where a.PersonId = p.PersonId
  );

PersonId|FirstName|LastName|
--------|---------|--------|

反连接

反连接用于返回左表中与右表不匹配的数据行，通常体现为 NOT EXISTS 或者 NOT IN 子查询。反连接的示意图如下：

反连接只会返回左表中的数据，右表只用于条件判断。反查询常见的应用包括：查找没有员工的部门信息，或者没有购买任何产品的顾客信息等。例如，以下语句返回了没有地址信息的人员：

select *
from Person p
where not exists(
  select 1
  from Address a 
  where a.PersonId = p.PersonId
  );

PersonId|FirstName|LastName|
--------|---------|--------|
       1|Allen    |Wang    |

如果觉得文章对你有用，请不要白嫖！欢迎关注❤️、评论📝、点赞👍！