175. Combine Two Tables.md

175. Combine Two Tables

Question

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:

FirstName, LastName, City, State

Thinking:

• Method:
  • 使用两张表的数据需要使用外链接，而我们需要使用人的信息找到Addresss表的信息。
  • 所以我们需要使用左外连接。

# Write your MySQL query statement below
select
    FirstName, LastName, City, State
from Person		#选取Person表作为主表
left join
    Address
on
    Person.PersonId = Address.PersonId;		//以id作为连接点。

二刷

1. 我们应该使用左外连接。
2. 我们使用Person作为左表，以左表为基准，获得所有的信息。

# Write your MySQL query statement below
SELECT
    p.FirstName, p.LastName, a.City, a.State
FROM Person p
LEFT OUTER JOIN
Address a
ON p.PersonId = a.PersonId;