题目:
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’)
表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
思路:
找到关键字person进行合并
提示:
有join 和left join和right join
1.inner join ,默认,可省略inner关键字
2.left /outer join ,左外连接,表中除了匹配行外,还包括左表有而右表中不匹配的行,对于这样的行,右表选择列置为null
3.right/outer join ,右外连接,表中除了匹配行外,还包括右表有而左表中不匹配的行,对于这样的行,左表选择列置为null
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId;