LeetCode MySQL-175. Combine Two Tables

    AllenLi
    LeetCode MySQL

    Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.
    Return the result table in any order.

    寫一個 SQL Query來報告性名、城市和州。
如 Address table中 不存在對應 personId 的編號，則地址回傳NULL。
任意排序結果。

    Table: Person

    +-------------+---------+
| Column Name | Type    |
+-------------+---------+
| personId    | int     |
| lastName    | varchar |
| firstName   | varchar |
+-------------+---------+
personId is the primary key column for this table.
This table contains information about the ID of some persons and their first and last names.

    Table: Address

    +-------------+---------+
| Column Name | Type    |
+-------------+---------+
| addressId   | int     |
| personId    | int     |
| city        | varchar |
| state       | varchar |
+-------------+---------+
addressId is the primary key column for this table.
Each row of this table contains information about the city and state of one person with ID = PersonId.

    Example 1:

    Input: 
Person table:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Wang     | Allen     |
| 2        | Alice    | Bob       |
+----------+----------+-----------+
Address table:
+-----------+----------+---------------+------------+
| addressId | personId | city          | state      |
+-----------+----------+---------------+------------+
| 1         | 2        | New York City | New York   |
| 2         | 3        | Leetcode      | California |
+-----------+----------+---------------+------------+
Output: 
+-----------+----------+---------------+----------+
| firstName | lastName | city          | state    |
+-----------+----------+---------------+----------+
| Allen     | Wang     | Null          | Null     |
| Bob       | Alice    | New York City | New York |
+-----------+----------+---------------+----------+
Explanation: 
There is no address in the address table for the personId = 1 so we return null in their city and state.
addressId = 1 contains information about the address of personId = 2.

    Solution:
    1. 選擇標題 Person table 的 firstName, lastName。
    2. 選擇標題 Address table 的 city, state。
    3. 將符合的資料放在左側，而右側資料表沒有符合的值為空值。

    Code:

    SELECT 
    p.firstName, 
    p.lastName,
    a.city,
    a.state
    FROM Person AS p
    LEFT JOIN Address AS a
		ON p.personId = a.personId;

    newPerson

    Related Posts