leetcode with MySQL:175. Combine Two Tables

刷leetcode遇到SQL的题目,那也只能换个语言了

题目:

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.

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.

给定两个table,结合两者
report People table里所有人的first name,last name,city,state(两table personId是对应的)
若该personId在Address没有资料,city,state格就填NULL

SELECT firstName,lastName,city,stateFROM Person LEFT JOIN Address ON Person.personId=Address.personId;

将两个table结合,条件订personId相等
记得用left join这样在Address没对应资料的人city,state才会填NULL
之后将firstName,lastName,city,state选出就好
最后执行时间320ms(faster than 95.44%)

那我们下题见