Combine Two Tables

Combine Two Tables

What is this problem about?

The Combine Two Tables interview question is a fundamental database query problem. You are given two tables: Person (containing personId, firstName, and lastName) and Address (containing addressId, personId, city, and state). You need to write a SQL query to report the first name, last name, city, and state for each person in the Person table. If the address of a personId is not present in the Address table, you must still show the person's name and report the city and state as null.

Why is this asked in interviews?

This is a standard Database interview pattern used by companies like Microsoft, Amazon, and Meta to test basic SQL knowledge. It specifically evaluates whether a candidate understands the difference between an INNER JOIN and a LEFT JOIN. Since the requirement specifies that people without addresses should still be included, a simple join is not enough; a left outer join is required.

Algorithmic pattern used

The core pattern is a LEFT JOIN. In SQL, a LEFT JOIN returns all records from the left table (Person), and the matched records from the right table (Address). If there is no match, the result is NULL on the right side.

• Selection: firstName, lastName, city, state
• From: Person
• Join Type: LEFT JOIN Address
• Join Condition: Person.personId = Address.personId

Example explanation

Person Table:

• 1, John, Doe
• 2, Jane, Smith

Address Table:

• 101, 1, New York, NY

Result:

1. For John (ID 1), there is a match in Address (New York, NY).
2. For Jane (ID 2), there is NO match in Address. The output will be:

• John, Doe, New York, NY
• Jane, Smith, null, null

Common mistakes candidates make

• Using INNER JOIN: Using a standard JOIN or WHERE clause join, which would exclude Jane Smith entirely from the result.
• Incorrect Select: Forgetting to handle the nulls or selecting columns that don't exist in the specified format.
• Join Condition: Joining on the wrong column (e.g., trying to join personId with addressId).

Interview preparation tip

Always look for keywords like "all records from table A regardless of table B" or "if not present, report null." These are immediate signals that you need a LEFT JOIN or RIGHT JOIN instead of an INNER JOIN.