# 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:

```sql
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:

```sql
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:

```sql
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

```sql
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.

```sql
LEFT JOIN Address AS a
```

This attaches rows from the `Address` table when possible.

The alias `a` refers to the `Address` table.

```sql
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:

```sql
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.