leecode-database 175. Combine Two Tables - Left Join

leecode地址链接
https://leetcode.com/problems/combine-two-tables/description/

Table: Person
表：Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.
PersonId是Person这张表的主键

Table: Address
表：Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.
AddressId是Address这张表的主键

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

为这张报表写SQL查询语句，为Person表中的每个人提供以下信息，不管这些人是否有地址：FirstName, LastName, City, State

Write your MySQL query statement below
在下面写你的MySQL查询语句

SELECT Person.FirstName, person.LastName, Address.City, Address.State from Person Left JOIN Address on Person.PersonId = Address.PersonId;

Custom Testcase
自定义测试用例

{
  "headers": {
    "Person": [
      "PersonId",
      "LastName",
      "FirstName"
    ],
    "Address": [
      "AddressId",
      "PersonId",
      "City",
      "State"
    ]
  },
  "rows": {
    "Person": [
      [
        1,
        "Wang",
        "Allen"
      ]
    ],
    "Address": [
      [
        1,
        2,
        "New York City",
        "New York"
      ]
    ]
  }
}

Expected answer
预期答案

{
  "headers": [
    "FirstName",
    "LastName",
    "City",
    "State"
  ],
  "values": [
    [
      "Allen",
      "Wang",
      null,
      null
    ]
  ]
}

Key Point：Left Join http://www.w3school.com.cn/sql/sql_join_left.asp
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN，RIGHT JOIN 称为 RIGHT OUTER JOIN，FULL JOIN 称为 FULL OUTER JOIN。

Related Points：Join Inner Join Right Join Full Join
JOIN/INNER JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

INNER JOIN 与 JOIN 是相同的。

//JOIN
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P

//INNER JOIN和其他语法类似
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName