Question

175. Combine Two Tables

中文文档

Description

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.

Solutions

Solution 1: LEFT JOIN

We can use a left join to join the Person table with the Address table on the condition Person.personId = Address.personId, which will give us the first name, last name, city, and state of each person. If the address of a personId is not in the Address table, it will be reported as null.

import pandas as pd


def combine_two_tables(person: pd.DataFrame, address: pd.DataFrame) -> pd.DataFrame:
  return pd.merge(left=person, right=address, how="left", on="personId")[
    ["firstName", "lastName", "city", "state"]
  ]

# Write your MySQL query statement below
SELECT firstName, lastName, city, state
FROM
  Person
  LEFT JOIN Address USING (personId);