leetcode-175-Combine-Two-Tables

描述


Table: Person

1
2
3
4
5
6
7
8
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

1
2
3
4
5
6
7
8
9
+-------------+---------+
| 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:

1
FirstName, LastName, City, State

分析


题目要求是列出 Person 表里的所有人,不管这个人是否在 Address 表里存在,也就是说如果这个 PersonId 在 Person 表里是存在的,但在 Address 表里不存在的,那么也应该被列出来,只是地址信息为空而已。所以这是一个左外连接的问题,要包含 Person 表里的所有数据,尽管这个数据在 Address 表里不一定存在。

解决方案(MySQL)


1
2
# Write your MySQL query statement below
SELECT FirstName, LastName, City, State FROM Person NATURAL LEFT JOIN Address

题目来源