175.组合两个表 (Easy)*

题目描述*

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')

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

代码*

考察连接，使用左外连接 left join 保留左表的全部行。

可以在 join 之前去除 Address 表中的重复项

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

```mysql tab="预处理"
select A.FirstName, A.LastName, B.City, B.State 
from Person A left join (
    select distinct PersonId, City, State 
    from Address) B
on A.PersonId = B.PersonId;

最后更新: July 23, 2022