Problem 175: 组合两个表
Categories: LeetCode-Database
175. 组合两个表
表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
sql脚本：
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');
解答
解法一：左联结（LEFT JOIN）
SELECT
FirstName,
LastName,
City,
State
FROM
Person
LEFT JOIN
Address ON (Person.PersonId = Address.PersonId);