【Leetcode】175. Combine Two Tables（Easy）

1.题目

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:

翻译：写一条SQL查询语句，提供在Person表中的每个人的以下信息，不管他们有没有地址。

FirstName, LastName, City, State

2.思路

 只要在Person表中即进行查询，使用左连接。条件是两个表中的PersonId一致。

3.算法

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

4.总结

 内连接（连接）：两个表中都有对应信息。

左连接：左侧的表中包含对应属性即可，右侧表中若无对应属性值用null填充。

右连接：右侧的表中包含对应属性即可，左侧表中若无对应属性值用null填充。

外连接：任意一张表包含要查询信息的属性值即可，没有的用null填充。

举个例子：

表1 person表

person_id	person_name
1	Li Ming
2	Wang Juan

表2 job表

person_id	job
1	Teacher
3	Chief

查询person_id, person_name 和 job三个信息

①内连接

person_id	person_name	job
1	Li Ming	Teacher

 ②左连接

person_id	person_name	job
1	Li Ming	Teacher
2	Wang Juan	null

 ③右连接

person_id	person_name	job
1	Li Ming	Teacher
3	null	Chief

 ④外连接

person_id	person_name	job
1	Li Ming	Teacher
2	Wang Juan	null
3	null	Chief

PS:吐槽博客的表格编辑功能太难用了，删除表格还半天。