[MySQL][LeetCode][Easy] 175. Combine Two Tables

心得:

題目要求找出Person表內所有人員的地址,就算是空的也無所謂,所以不用理會Address表內是否有Person表的資料,這題必須使用LEFT JOIN來關聯。

問題:

Table: Person

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

+-------------+---------+
>
> | Column Name | Type |
>
> +-------------+---------+
>
> | PersonId | int |
>
> | FirstName | varchar |
>
> | LastName | varchar |
>
> +-------------+---------+
>
> PersonId is the primary key column for this table.
>
>

Table: Address

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

+-------------+---------+
>
> | 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:

1
2
3
4

FirstName, LastName, City, State
>
>

答案:

1
2
3
4
5
6

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

參考:

  1. [SQL] 一張圖片解釋 JOIN