Combine Two Tables - Problem

You are given two tables: Person and Address.

The Person table contains:

  • personId (int): Primary key
  • firstName (varchar): Person's first name
  • lastName (varchar): Person's last name

The Address table contains:

  • addressId (int): Primary key
  • personId (int): Foreign key to Person table
  • city (varchar): City name
  • state (varchar): State name

Write a SQL query 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.

Table Schema

Person
Column Name Type Description
personId PK int Primary key, unique identifier for each person
firstName varchar Person's first name
lastName varchar Person's last name
Primary Key: personId
Address
Column Name Type Description
addressId PK int Primary key, unique identifier for each address
personId int Foreign key referencing Person.personId
city varchar City name where the person lives
state varchar State name where the person lives
Primary Key: addressId

Input & Output

Example 1 — Basic LEFT JOIN
Input Tables:
Person
personId firstName lastName
1 Wang Allen
2 Alice Bob
Address
addressId personId city state
1 2 New York City New York
Output:
firstName lastName city state
Wang Allen
Alice Bob New York City New York
💡 Note:

Wang Allen (personId=1) has no address record, so city and state are null. Alice Bob (personId=2) has an address record with city 'New York City' and state 'New York'.
Example 2 — All People Have Addresses
Input Tables:
Person
personId firstName lastName
1 John Doe
2 Jane Smith
Address
addressId personId city state
1 1 Los Angeles California
2 2 Chicago Illinois
Output:
firstName lastName city state
John Doe Los Angeles California
Jane Smith Chicago Illinois
💡 Note:

Both people have address records, so all city and state values are populated with actual data.
Example 3 — No Addresses
Input Tables:
Person
personId firstName lastName
1 Mike Johnson
Address
addressId personId city state
Output:
firstName lastName city state
Mike Johnson
💡 Note:

The Address table is empty, so the person's city and state are null values.

Constraints

  • 1 ≤ personId ≤ 1000
  • firstName and lastName are non-empty strings
  • city and state are non-empty strings when present

Visualization

Tap to expand
Combine Two Tables with LEFT JOINPerson TablepersonIdfirstNamelastName1WangAllen2AliceBobAddress TablepersonIdcitystate2NYCNYLEFT JOINON personIdCombined ResultfirstNamelastNamecitystateWangAllennullnullAliceBobNYCNY💡 LEFT JOIN preserves ALL Person recordsMissing addresses become NULL values
Understanding the Visualization
1
Person Table
Contains all people with their names
2
LEFT JOIN
Combines tables on personId
3
Result
All people with their address info (null if missing)
Key Takeaway
🎯 Key Insight: Use LEFT JOIN when you need to preserve all records from the left table, even when there's no match in the right table

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
85.0K Views
Very High Frequency
~8 min Avg. Time
2.8K Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen