EasyDatabaseUpdated Sep 2026

Combine Two Tables

Asked at Google

Difficulty

Easy

Time

O(n + m)

Space

O(n + m)

Pattern

Database

Companies

1

Problem

You are given two database tables. Person holds one row per person, with columns personId, lastName and firstName. Address holds one row per known address, with columns addressId, personId, city and state. The personId column in Address points back to a row in Person.

Write a SQL query that returns firstName, lastName, city and state for every person in the Person table. If a person has no matching row in Address, their city and state must still appear in the result, filled with NULL.

In other words, no person may be dropped from the output just because they have no address. The rows can come back in any order.

Every person must appear

PersonpersonIdlastNamefirstName1WangAllen2AliceBobTwo people, keyed by personIdAddressaddressIdpersonIdcitystate12New York CityNew York23LeetcodeCaliforniaOnly personId 2 has an address; personId 3 has no personOutputfirstNamelastNamecitystateAllenWangNULLNULLBobAliceNew York CityNew YorkPerson 1 is kept, with NULLs where the address would go
Person 1 has no address row, yet they still need a line in the output with NULL city and state.

Constraints

  • ●personId is the primary key of the Person table
  • ●addressId is the primary key of the Address table
  • ●personId in Address is a foreign key referencing Person.personId
  • ●Each person has at most one address row
  • ●Result rows may be returned in any order

Examples

Example 1
InputPerson = [[1, "Wang", "Allen"], [2, "Alice", "Bob"]] Address = [[1, 2, "New York City", "New York"], [2, 3, "Leetcode", "California"]]
Output[["Allen", "Wang", null, null], ["Bob", "Alice", "New York City", "New York"]]
WhyPerson 1 has no row in Address, so their city and state are NULL. Person 2 matches addressId = 1, so their city and state are filled in. The address for personId = 3 is ignored because no such person exists.
Example 2
InputPerson = [[1, "Martin", null], [2, null, null]] Address = [[1, 2, null, null]]
Output[[null, "Martin", null, null], [null, null, null, null]]
WhyPerson 1 has no address at all, so both address columns are NULL. Person 2 does match an address row, but that row's city and state are themselves NULL, so the result looks the same.

Asked At

1 companies
CompanyDifficulty
GoogleEasyView all Google questions →

How to Think About It

1

Left join is required because some people may not have an address. FROM Person LEFT JOIN Address ON Person.personId = Address.personId ensures all people appear even without a matching address row.

2

No self-join needed here. The two tables are joined by personId (foreign key). LEFT JOIN returns all rows from Person and matched rows from Address; unmatched columns are NULL.

3

SELECT specific columns: p.firstName, p.lastName, a.city, a.state. Use table aliases p and a to disambiguate if both tables had overlapping column names (they do not here, but it is best practice).

4

Why not INNER JOIN: INNER JOIN would exclude Bob (no matching address). LEFT JOIN preserves all people regardless of whether they have an address.

Optimal Approach

Step 1: FROM Person p LEFT JOIN Address a ON p.personId = a.personId.
Step 2: SELECT p.firstName, p.lastName, a.city, a.state.

Walkthrough: Person = [[1, Martin, null], [2, null, null]]. Address = [[1, 2, null, null]].

  • Person 1: no matching address -> (Martin, null, NULL, NULL)
  • Person 2: matching address -> (null, null, null, null)

Time: O(n + m) for the join. Space: O(n + m) for the result set.

LEFT JOIN keeps unmatched people

FROM Person pLEFT JOIN Address aON p.personId = a.personIdSELECT p.firstName, p.lastName, a.city, a.stLEFT JOIN is the key: it keeps every Person row even when Addresshas no matchjoined rowsfirstNamelastNamecitystateAllenWangNULLNULLBobAliceNew York CityNew YorkRow 1 found no partner so it is padded with NULL; row 2 matchedaddressId = 1INNER JOIN would drop AllenLEFT JOIN keeps all peopleunmatched address columns -> NULL
The join walks Person and pulls in any matching Address row; when nothing matches, the address columns stay NULL.
ComplexityTime O(n + m)Space O(n + m)n is the number of Person rows and m is the number of Address rows; the join scans both tables once and the result holds one row per person.

What Trips People Up in Real Interviews

1

Using INNER JOIN instead of LEFT JOIN. This drops people without addresses. The problem requires all people to appear, so LEFT JOIN is mandatory.

2

Joining on the wrong column. The foreign key is personId in both tables, but you must join Person.personId = Address.personId, not addressId.

3

Selecting addressId when the problem asks for city and state. Read the problem statement carefully for the exact output columns.

4

Forgetting that LEFT JOIN produces NULL for unmatched columns. The result will have NULL city and state for people without addresses. This is correct behavior, not an error.

5

Adding DISTINCT or GROUP BY unnecessarily. Since personId is a primary key in Person, each person appears at most once. No deduplication needed.

Solution Code

O(n + m) time
SELECT p.firstName, p.lastName, a.city, a.state
FROM Person p
LEFT JOIN Address a ON p.personId = a.personId;

Pro at DSA?

Test your skills with a real FAANG-style mock interview.

Start a Mock Interview →

Frequently Asked Questions

What is the Combine Two Tables problem?

Write a SQL query to report the first name, last name, city, and state of each person. The Person table contains personId, lastName, and firstName. The Address table contains addressId, personId, city, and state. A person may not have an address, so use a LEFT JOIN.

How do you solve Combine Two Tables?

The optimal approach runs in O(n + m) time and O(n + m) space. Scroll up to "Optimal Approach" for the step-by-step walkthrough, diagrams, and code in Python, C++ and Java.

What companies ask Combine Two Tables?

Combine Two Tables is asked at Google. It is a easy difficulty problem.

What are common mistakes on Combine Two Tables?
  • Using INNER JOIN instead of LEFT JOIN. This drops people without addresses. The problem requires all people to appear, so LEFT JOIN is mandatory.
  • Joining on the wrong column. The foreign key is personId in both tables, but you must join Person.personId = Address.personId, not addressId.
  • Selecting addressId when the problem asks for city and state. Read the problem statement carefully for the exact output columns.
  • Forgetting that LEFT JOIN produces NULL for unmatched columns. The result will have NULL city and state for people without addresses. This is correct behavior, not an error.
  • Adding DISTINCT or GROUP BY unnecessarily. Since personId is a primary key in Person, each person appears at most once. No deduplication needed.