/175. Left Join Two Tables

175. Left Join Two Tables

Easy

Databases & SQL79.3% acceptance

Given two lists of dictionaries representing two tables: users and locations. The users table contains unique user_id, surname, and given_name. The locations table contains unique location_id, user_id, city, and region. For each user in the users table, output their given_name, surname, city, and region. If a user does not have a corresponding entry in the locations table, output None for city and region. Return the result as a list of dictionaries, in any order.

Example 1

Input: users = [ {user_id: 10, surname: Smith, given_name: John}, {user_id: 20, surname: Doe, given_name: Jane} ] locations = [ {location_id: 1, user_id: 20, city: Boston, region: MA} ]

Output: [ {given_name: John, surname: Smith, city: None, region: None}, {given_name: Jane, surname: Doe, city: Boston, region: MA} ]

Explanation: User 10 has no location, so city and region are None. User 20 has a location.

Example 2

Input: users = [ {user_id: 1, surname: Lee, given_name: Anna} ] locations = []

Output: [ {given_name: Anna, surname: Lee, city: None, region: None} ]

Explanation: No locations, so city and region are None for all users.

Constraints

1 <= len(users) <= 1000
0 <= len(locations) <= 1000
Each user_id in users is unique
Each location_id in locations is unique
Each user_id in locations may or may not exist in users
All string fields are non-empty and at most 100 characters

Python (current runtime)

Case 1

Input: users = [ {'user_id': 100, 'surname': 'Brown', 'given_name': 'Charlie'}, {'user_id': 200, 'surname': 'Green', 'given_name': 'Olivia'} ] locations = [ {'location_id': 10, 'user_id': 100, 'city': 'Seattle', 'region': 'WA'}, {'location_id': 20, 'user_id': 300, 'city': 'Austin', 'region': 'TX'} ]

Expected: [ {'given_name': 'Charlie', 'surname': 'Brown', 'city': 'Seattle', 'region': 'WA'}, {'given_name': 'Olivia', 'surname': 'Green', 'city': None, 'region': None} ]

Case 2

Input: users = [ {'user_id': 5, 'surname': 'Kim', 'given_name': 'Soo'}, {'user_id': 6, 'surname': 'Park', 'given_name': 'Min'} ] locations = [ {'location_id': 1, 'user_id': 5, 'city': 'Seoul', 'region': 'KR'}, {'location_id': 2, 'user_id': 6, 'city': 'Busan', 'region': 'KR'} ]

Expected: [ {'given_name': 'Soo', 'surname': 'Kim', 'city': 'Seoul', 'region': 'KR'}, {'given_name': 'Min', 'surname': 'Park', 'city': 'Busan', 'region': 'KR'} ]

Case 3

Input: users = [ {'user_id': 7, 'surname': 'Nguyen', 'given_name': 'Linh'} ] locations = [ {'location_id': 3, 'user_id': 8, 'city': 'Hanoi', 'region': 'VN'} ]

Expected: [ {'given_name': 'Linh', 'surname': 'Nguyen', 'city': None, 'region': None} ]