Combine Two Tables
Asked at Google
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.
Asked At
| Company | Difficulty | |
|---|---|---|
| Easy | View all Google questions → |
How to Think About It
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.
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.
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).
Visual walkthrough: Person has [1, Alice], [2, Bob], [3, Charlie]. Address has [1, 1, NY, NY], [3, 3, SF, CA]. LEFT JOIN pairs: (Alice, NY, NY), (Bob, NULL, NULL), (Charlie, SF, CA). Bob has no address so city and state are NULL.
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.
What Trips People Up in Real Interviews
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.
Solution Code
SELECT p.firstName, p.lastName, a.city, a.state FROM Person p LEFT JOIN Address a ON p.personId = a.personId;
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 is described in detail above, including step-by-step walkthroughs, complexity analysis, and solution code in Python. Scroll up to the "Optimal Approach" section.
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.