LeetCode 175: Combine Two Tables

Problem Restatement

We are given two tables: Person and Address.

The Person table stores each person’s ID, first name, and last name.

The Address table stores address records. Each address belongs to a person through personId.

We need to report this information for every person:

firstName, lastName, city, state

The important part is that every person must appear in the result, even if that person has no address.

If a person has no matching address row, city and state should be NULL.

Table Structure

Person table:

Column	Type	Meaning
`personId`	int	Primary key for each person
`lastName`	varchar	Person’s last name
`firstName`	varchar	Person’s first name

Address table:

Column	Type	Meaning
`addressId`	int	Primary key for each address
`personId`	int	ID of the person who owns this address
`city`	varchar	City name
`state`	varchar	State name

Example

Suppose Person contains:

personId	lastName	firstName
1	Wang	Allen
2	Alice	Bob

And Address contains:

addressId	personId	city	state
1	2	New York City	New York
2	3	Leetcode	California

Person 2 has an address.

Person 1 does not have an address.

The result should be:

firstName	lastName	city	state
Allen	Wang	NULL	NULL
Bob	Alice	New York City	New York

Notice that the address with personId = 3 does not appear because there is no matching person in the Person table.

First Thought

We need data from both tables.

The names come from Person.

The city and state come from Address.

So we need a join between the two tables using personId.

The matching condition is:

Person.personId = Address.personId

But the problem asks for every person, even when there is no address.

That means an INNER JOIN is not enough, because INNER JOIN only keeps rows where both tables match.

We need LEFT JOIN.

Key Insight

Start from the Person table because every person must appear in the output.

Then attach address information when it exists.

This is exactly what LEFT JOIN does.

A LEFT JOIN keeps every row from the left table. If the right table has a matching row, SQL fills in the right table columns. If there is no match, SQL returns NULL for the right table columns.

Here, the left table should be Person.

The right table should be Address.

Algorithm

Use Person as the base table.

Join Address on the shared personId.

Select the four required columns:

Person.firstName
Person.lastName
Address.city
Address.state

Because this is a LEFT JOIN, all people remain in the result.

Correctness

The query begins from the Person table. Therefore, every row in Person is considered for the final result.

For each person, the join condition checks whether there is an address row with the same personId.

If such an address exists, the query returns that person’s firstName, lastName, city, and state.

If no address exists, the LEFT JOIN still keeps the person row. Since there is no matching row from Address, SQL fills city and state with NULL.

Therefore, the query returns exactly one report row for each person, with address data when available and NULL address fields when unavailable.

Complexity

Let P be the number of rows in Person.

Let A be the number of rows in Address.

Metric	Value	Why
Time	Depends on the database engine and indexes	The database must join rows from `Person` and `Address`
Space	Depends on the execution plan	The database may use hash tables, indexes, or nested loops

In practice, with an index on Address.personId, the join can be efficient.

For LeetCode, the main point is choosing the correct join type.

Implementation

SELECT
    p.firstName,
    p.lastName,
    a.city,
    a.state
FROM Person AS p
LEFT JOIN Address AS a
    ON p.personId = a.personId;

Code Explanation

FROM Person AS p means we start with the Person table.

The alias p makes the query shorter and easier to read.

LEFT JOIN Address AS a

This attaches rows from the Address table when possible.

The alias a refers to the Address table.

ON p.personId = a.personId

This tells SQL how the two tables are related.

A person matches an address when both rows have the same personId.

The SELECT clause chooses the exact columns requested by the problem:

p.firstName,
p.lastName,
a.city,
a.state

If a person has no matching address, a.city and a.state become NULL.

Testing

Test with a person who has an address:

personId	lastName	firstName
2	Alice	Bob

addressId	personId	city	state
1	2	New York City	New York

Expected output:

firstName	lastName	city	state
Bob	Alice	New York City	New York

Test with a person who has no address:

personId	lastName	firstName
1	Wang	Allen

No matching row exists in Address.

Expected output:

firstName	lastName	city	state
Allen	Wang	NULL	NULL

Test with an address that has no matching person:

addressId	personId	city	state
2	3	Leetcode	California

If Person has no personId = 3, this row should not appear in the result.

That is why Person must be the left table.