LeetCode 175: Combine Two Tables Solution in SQL Explained

Problem Statement

In LeetCode 175, you’re given two tables:

• Person:
• personId (int, primary key)
• firstName (varchar)
• lastName (varchar)
• Address:
• addressId (int, primary key)
• personId (int, foreign key to Person)
• city (varchar)
• state (varchar)

Your task is to write an SQL query to return a result table with columns firstName, lastName, city, state for all persons, including those without address records (where city and state should be null). This differs from algorithmic challenges like LeetCode 174: Dungeon Game, focusing on database querying rather than path optimization.

Constraints

• Tables may have any number of rows.
• Address.personId is a foreign key referencing Person.personId.
• Some persons may not have address records.

Example

Let’s see a case:

Copy codePerson table:
+----------+-----------+----------+
| personId | firstName | lastName |
+----------+-----------+----------+
| 1        | John      | Doe      |
| 2        | Jane      | Smith    |
| 3        | Alice     | Brown    |
+----------+-----------+----------+

Address table:
+-----------+----------+---------+-------+
| addressId | personId | city    | state |
+-----------+----------+---------+-------+
| 1         | 1        | New York| NY    |
| 2         | 2        | Boston  | MA    |
+-----------+----------+---------+-------+

Output:
+-----------+----------+---------+-------+
| firstName | lastName | city    | state |
+-----------+----------+---------+-------+
| John      | Doe      | New York| NY    |
| Jane      | Smith    | Boston  | MA    |
| Alice     | Brown    | null    | null  |
+-----------+----------+---------+-------+
Explanation:
<ul>
<li>John (1) has address New York, NY.</li>
<li>Jane (2) has address Boston, MA.</li>
<li>Alice (3) has no address, so city and state are null.</li>
</ul>

This shows we’re combining tables with all persons included.

Understanding the Problem

How do you solve LeetCode 175: Combine Two Tables in SQL? We need to:

• Select firstName, lastName from Person.
• Include city, state from Address where available.
• Ensure all persons appear, even without addresses (city, state as null).

For the example tables, a join is needed, but an inner join would exclude Alice (no address). A left join ensures all Person rows are included, with Address data if it exists. This isn’t a Python coding task like LeetCode 167: Two Sum II - Input Array Is Sorted; it’s about SQL table merging. We’ll use: 1. Left Join: Simple, efficient—our best solution. 2. Inner Join with Union: Alternative approach.

Let’s dive into the best solution.

Best Solution: Left Join Approach

Explanation

Left Join combines the tables by:

• Starting with all rows from Person (left table).
• Matching with Address (right table) on personId.
• Including null for city and state where no match exists.

This ensures every person is included, regardless of address data, with a straightforward query that runs efficiently in O(n) time (database-dependent, typically linear with indexing).

For the example:

• Join Person and Address on personId.
• Select required columns, null for unmatched addresses.

Step-by-Step Example

Example: Person and Address tables as above

Goal: Return table with firstName, lastName, city, state.

• Step 1: Start with Person table (all rows).
• Rows: (1, John, Doe), (2, Jane, Smith), (3, Alice, Brown).
• Step 2: Left join with Address on personId.
• Match 1: (1, John, Doe) → (1, New York, NY).
• Match 2: (2, Jane, Smith) → (2, Boston, MA).
• No match 3: (3, Alice, Brown) → (null, null, null).
• Step 3: Select columns.
• Result:
• John, Doe, New York, NY
• Jane, Smith, Boston, MA
• Alice, Brown, null, null
• Finish: Return the combined table.

How the Code Works (Left Join) – Detailed Line-by-Line Explanation

Here’s the SQL query with a thorough breakdown:

Copy code-- Line 1: Select required columns
SELECT 
    p.firstName, 
    p.lastName, 
    a.city, 
    a.state
-- Columns from Person (p) and Address (a) (e.g., firstName, lastName, city, state)

-- Line 2: Specify Person table as left table
FROM 
    Person p
-- Base table with all persons (e.g., John, Jane, Alice)

-- Line 3: Left join with Address table
LEFT JOIN 
    Address a
-- Include all Person rows, match Address where possible (e.g., New York for John)

-- Line 4: Join condition
ON 
    p.personId = a.personId
-- Match on personId (e.g., 1=1, 2=2, 3 unmatched)

This detailed breakdown clarifies how the left join efficiently combines the tables.

Alternative: Inner Join with Union Approach

Explanation

Inner Join with Union combines tables in two steps:

• Inner Join: Get persons with addresses.
• Union with Left Anti-Join: Add persons without addresses (using a subquery or NOT IN).

It’s a practical alternative, ensuring all persons are included, but more complex and potentially less efficient due to multiple operations, still O(n) time with proper indexing, O(n) space for intermediate results.

For the example:

• Inner join for John, Jane.
• Union with Alice (no address).

Step-by-Step Example (Alternative)

For the same tables:

• Inner Join:
• John, Doe, New York, NY
• Jane, Smith, Boston, MA
• Anti-Join:
• Alice, Brown, null, null (personId 3 not in Address)
• Union: Combine results.

How the Code Works (Inner Join with Union)

Copy codeSELECT 
    p.firstName, 
    p.lastName, 
    a.city, 
    a.state
FROM 
    Person p
INNER JOIN 
    Address a
ON 
    p.personId = a.personId
UNION
SELECT 
    p.firstName, 
    p.lastName, 
    NULL AS city, 
    NULL AS state
FROM 
    Person p
WHERE 
    p.personId NOT IN (SELECT personId FROM Address)

Complexity

• Left Join:
• Time: O(n) – single join operation (database-dependent).
• Space: O(1) – minimal extra space beyond result.
• Inner Join with Union:
• Time: O(n) – join plus subquery (potentially less efficient).
• Space: O(n) – intermediate results for union.

Efficiency Notes

Left Join is the best solution with O(n) time and O(1) extra space, offering simplicity and efficiency—Inner Join with Union matches time complexity but involves more operations (join, subquery, union), making it less straightforward and potentially slower in practice, with O(n) space for intermediates.

Key Insights

• Left Join: All-inclusive, simple.
• Union: Combines matched and unmatched.
• Null: Represents missing data.

Additional Example

Copy codePerson: 
| 1 | Bob  | Lee   |
| 2 | Ann  | Kim   |

Address: 
| 1 | 1 | Seattle | WA |

Output:
| Bob | Lee | Seattle | WA |
| Ann | Kim | null    | null |

• Left Join: Matches Bob, includes Ann with nulls.

Edge Cases

• No Addresses: All city, state null.
• All Matched: No nulls.
• Empty Person: Empty result.

Both solutions handle these well.

Final Thoughts

LeetCode 175: Combine Two Tables in SQL is a foundational database challenge. The Left Join solution excels with its efficiency and clarity, while Inner Join with Union offers an alternative perspective. Want more SQL? Try LeetCode 176: Second Highest Salary for aggregation or explore Python Basics for coding skills. Ready to practice? Solve LeetCode 175 on LeetCode with the example tables, aiming for the combined result—test your skills now!