LeetCode刷题笔记 - 175.Combine Two Tables_自然联结描述正确的是排除多次出现

2018-10-09

在LeetCode上刷MySQL数据库的题。以下序号与LeetCode上的题目序号保持一致，方便以后自己查看。

根据每道题考察的内容，来扩展知识点。

每天记录刷题内容、笔记、心得。​​​​

目录

175.Combine Two Tables

Description：

Solution:

总结：

（1）inner join 内联结（等值联结）：

（2）outer join 外联结

（3）self-join 自然结

 使用联结和联结条件

---

175.Combine Two Tables

---

Description：

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

 

---

Solution:

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

注意：如果一个人没有地址信息，那么使用where子句来过滤记录将会失败，因为它不会显示姓名信息。

此题目考察的是SQL联结（join）问题。联结是一种机制，用来在一条SELECT语句中关联表。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的的行。

总结：

联结共包括：内联结inner join，外联结outer join（左/右联结left join/right join、全外联结 full outer join），自然结self-join，自然联结natural join

（1）inner join 内联结（等值联结）：

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

    目前为止使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）。两个表之间的关系是以inner join指定的部分FROM子句，在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句。传递给ON的实际条件与传递给WHERE的相同

注释：INNER JOIN 与 JOIN 是相同的。

联结多个表

SQL不限制一条SELECT语句中可以联结的表的数目。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

注意：DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降的越厉害。虽然SQL本身不限制每个联结的约束中表的数目，但实际上许多DBMS都有限制。

（2）outer join 外联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。例如，可能需要使用联结完成以下工作：

• 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
• 列出所有产品以及订购数量，包括没有人订购的产品；
• 计算平均销售规模，包括那些至今尚未下订单的顾客。

在上述例子中，联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。

注意：用来创建外联结的语法在不同的 SQL 实现中可能稍有不同。

left  join 左联结

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

同理RIGHT JOIN

注释：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN， RIGHT JOIN 称为 RIGHT OUTER JOIN。

下面的 SELECT 语句给出了一个简单的内联结。它检索所有顾客及其订单：

SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

外联结语法类似。要检索包括没有订单顾客在内的所有顾客，可如下进行：

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

提示：外联结的类型SELECT 语句使用了关键字 OUTER JOIN 来指定联结类型（而不是在 WHERE 子句中指定）。但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（ RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）。上面的例子使用 LEFT OUTER JOIN 从 FROM 子句左边的表（ Customers 表）中选择所有行。

要记住，总是有两种基本的外联结形式：左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整 FROM 或 WHERE 子句中表的顺序，左外联结可以转换为右外联结。因此，这两种外联结可以互换使用，哪个方便就用哪个。

full  join 全外联结

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

注释：在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN。

还存在另一种外联结，就是全外联结（ full outer join ），它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。全外联结的语法如下：

SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
ON Orders.cust_id = Customers.cust_id;

警告： FULL OUTER JOIN 的支持
Access 、 MariaDB 、 MySQL 、 Open Office Base 或 SQLite 不支持 FULL OUTER JOIN 语法。

（3）self-join 自然结

此查询中需要的两个表实际上是相同的表。自然结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多。

现在有个需求,需要给Tom同一公司的所有会员发送一条邮件。

分析下基本思路,首先根据Tom找到其所在的公司名,在根据公司名找到其公司民下的所有会员。下面是解决代码:

使用子查询：

select * 
from Customers 
where Company in(select Company from Customers where Name='Tom')

查询虽然方便,但是大多数DBMS的处理子查询的速度比处理联结要慢的多，如果数据量少的话,几乎没什么影响,但是如果后期数据会很大的话,就应该使用自联结！

使用自然结：

select *
from Customers c1,Customers c2
where c1.Company=c2.Company
and c2.Name='Tom'

联结通过联结的机制,将所有公司名相同的记录联结到一起,即每一个公司的会员,都会和同一公司的其他会员联结一次,因为他们的公司名相同,这是时候指定c2.name='Tom'，就能拿到Tom和其公司所有会员的资料，这个时候就可以发邮件了！

（4）natural join 自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结（内联结）返回所有数据，相同的
列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。
怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（ SELECT * ），而对其他表的列使用明确的子集来完成。下面举一个例子：

SELECT C.*, O.order_num, O.order_date,OI.prod_id, OI.quantity,OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
    AND OI.order_num = O.order_num
    AND prod_id = 'RGAN01';

事实上，我们迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结。通配符只对一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。

 使用联结和联结条件

• 注意所使用的联结类型。一般我们使用内联结（INNER JOIN），但使用外联结（OUTER JOIN）也有效。
• 关于确切的联结语法，应该查看具体的文档，看相应的 DBMS 支持何种语法。
• 保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡儿积。
• 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。