175. Combine Two Tables
Table: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| personId | int |
| lastName | varchar |
| firstName | varchar |
+-------------+---------+
personId is the primary key (column with unique values) 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 with unique values) for this table.
Each row of this table contains information about the city and state of one person with ID = PersonId.
Write a solution 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.
The result format is in the following example.
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:
# Write your MySQL query statement below
select firstName, lastName, city, state
from Person left join Address
on Person.personId = Address.personId;
FROM Person LEFT JOIN Address
这正是 LEFT JOIN 的语义：
以左表（Person）为主，右表（Address）匹配不上时，用 NULL 填充。
ON Person.personId = Address.personId
这是 连接条件（join condition）：
意思是：
-
用
personId把两张表关联起来 -
只有当：
时，这两行才会合并成一行结果
SELECT firstName, lastName, city, state
表示从连接后的结果中取出：
firstName（来自 Person 表）lastName（来自 Person 表）city（来自 Address 表）state（来自 Address 表）