欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

leecode-database 175. Combine Two Tables - Left Join

程序员文章站 2024-03-03 16:45:16
...

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
相关标签: Join