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