175. Combine Two Tables

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;