LeetCode’s Combine Two Tables problem is a good way to get comfortable with how databases join information. Two tables are given, Person and Address, and the goal is to return every person, adding address details where they exist. The query is short, but it teaches how joins pull related data and what happens when a record doesn’t have a match on the other side.
Solving this type of problem in SQL Server is about thinking through how data connects and moves between tables. SQL Server gives you tools to describe those relationships precisely, and it handles the heavy lifting behind the scenes. The best mindset is to focus on how rows in one table relate to those in another, and how the database retrieves, matches, or skips them based on those links. When you start thinking in terms of how SQL Server processes those relationships, building queries becomes less about syntax and more about structuring information in a way that makes sense to both you and the database engine.
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| personId | int |
| lastName | varchar |
| firstName | varchar |
+-------------+---------+
personId is the primary key (column with unique values) for this table.
This table contains information about the ID of some persons and their first and last names.
Table: Address
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| addressId | int |
| personId | int |
| city | varchar |
| state | varchar |
+-------------+---------+
addressId is the primary key (column with unique values) for this table.
Each row of this table contains information about the city and state of one person with ID = PersonId.
Write a solution to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Person table:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1 | Wang | Allen |
| 2 | Alice | Bob |
+----------+----------+-----------+
Address table:
+-----------+----------+---------------+------------+
| addressId | personId | city | state |
+-----------+----------+---------------+------------+
| 1 | 2 | New York City | New York |
| 2 | 3 | Leetcode | California |
+-----------+----------+---------------+------------+
Output:
+-----------+----------+---------------+----------+
| firstName | lastName | city | state |
+-----------+----------+---------------+----------+
| Allen | Wang | Null | Null |
| Bob | Alice | New York City | New York |
+-----------+----------+---------------+----------+
Explanation:
There is no address in the address table for the personId = 1 so we return null in their city and state.
addressId = 1 contains information about the address of personId = 2.
Solution 1: LEFT JOIN
This solution starts from Person and brings in address data through a LEFT JOIN so every person shows up, even when there’s no address row.
SELECT
p.FirstName,
p.LastName,
a.City,
a.State
FROM Person AS p
LEFT JOIN Address AS a
ON a.PersonId = p.PersonId;
Let’s walk through what each part does step by step:
FROM Person AS p
That line tells SQL Server where to begin. It starts with the Person table and reads through every row one by one. The alias p is just a shorter name to make later references easier to follow. Every person is included at this point because no filtering has been applied yet.
LEFT JOIN Address AS a
ON a.PersonId = p.PersonId
This is where both tables connect. The LEFT JOIN keyword makes sure that all rows from Person appear in the result, even when no matching record exists in the Address table.
The condition ON a.PersonId = p.PersonId explains how both tables relate to each other. When SQL Server finds a match, the address information is added to that person’s row. When no match is found, the person still appears in the output, but City and State are returned as NULL.
That behavior gives the correct outcome for the problem because every person must appear, no matter if they have an address or not.
SELECT
p.FirstName,
p.LastName,
a.City,
a.State
This part decides which columns to show in the final result. The prefix p. means the column belongs to the Person table, and a. means it belongs to the Address table. Every person’s first and last name always appear because they come from the Person table. The city and state values only appear when an address is found. When there is no match, those fields stay as NULL.
You can think of SQL Server checking each person, finding a matching address if one exists, and then moving on to the next record. That process repeats until everyone in the Person table is listed.
This solution is easy to read and matches exactly what the problem is asking for. With an index on Address(PersonId), SQL Server can complete the join in about O(P log A). Without that index, the cost grows closer to O(P × A) because the database scans the address table for every person. For larger datasets, SQL Server may decide to use a hash join, which can improve the time closer to O(P + A) but will need about O(A) extra memory for the hash table. A nested-loops plan keeps extra memory near O(1) because rows are streamed as they are produced. This method is practical for beginners and still performs well enough to be a preferred answer during an interview.
Solution 2: OUTER APPLY Single-Row Lookup
This version pulls the matching address row per person through OUTER APPLY. It behaves like a LEFT JOIN for one-to-one data and can shine when Address has multiple rows per person and you want one row chosen predictably.
SELECT
p.FirstName,
p.LastName,
x.City,
x.State
FROM Person AS p
OUTER APPLY (
SELECT TOP (1) a.City, a.State
FROM Address AS a
WHERE a.PersonId = p.PersonId
ORDER BY a.AddressId
) AS x;
Let’s walk through what each part does step by step for this one:
FROM Person AS p
This line tells SQL Server to read from the Person table. The alias p is used for quick reference later in the query. SQL Server goes through every row of Person so that every individual appears in the result.
OUTER APPLY (
SELECT TOP (1) a.City, a.State
FROM Address AS a
WHERE a.PersonId = p.PersonId
ORDER BY a.AddressId
) AS x
This is where the query gets interesting. OUTER APPLY acts like a loop that runs the subquery for each row in the outer table. For every person in Person, SQL Server runs the inner query against Address.
The inner query looks for addresses where a.PersonId matches the current person being processed. The TOP (1) keyword means only the first matching row will be returned. That’s helpful when a person has multiple addresses. The ORDER BY a.AddressId keeps the result consistent by picking the first address record in order.
When no match is found, SQL Server still keeps that person in the output, with NULL in place of the address fields. That’s the main difference between OUTER APPLY and CROSS APPLY. The outer version keeps everyone, just like a LEFT JOIN does.
SELECT
p.FirstName,
p.LastName,
x.City,
x.State
These columns form the final result. The first two come directly from Person. The next two are pulled from the subquery output labeled as x. When the subquery finds a match, City and State show real values. When no record exists in Address, they show as NULL. SQL Server is effectively combining one person’s data with one address at a time. Each row that comes out of the OUTER APPLY represents that connection.
This version is great when you want control over how many matching records to include or when you need a specific ordering rule. With an index on Address(PersonId), SQL Server can seek through the address table efficiently for every person, which brings the time close to O(P log A). On large tables, the optimizer keeps this as a nested-loops apply, so extra memory stays near O(1). Without that index, SQL Server scans Address for each person and must sort by AddressId per person to honor TOP (1) ORDER BY, which raises cost and adds per-row sort memory.
For best performance with TOP (1) and ORDER BY a.AddressId, it helps to have an index such as:
CREATE INDEX IX_Address_PersonId_AddressId ON Address(PersonId, AddressId)
That index lets SQL Server seek to a person’s rows and stop at the first in order, matching the near O(P log A) behavior. For this LeetCode problem, the data is small enough that it won’t matter, but it’s still worth mentioning.
While the first solution fits most interview expectations for clarity and speed, this one shows a deeper command of SQL Server features. OUTER APPLY is less common in basic discussions, but knowing it sets you apart because it handles row-by-row lookups with more control. It shows that you understand how SQL Server executes correlated subqueries efficiently and that you can choose the right tool for the data pattern. Interviewers usually prefer the first query for its simplicity, yet bringing up this version during follow-up questions shows you can handle more complex data relationships without changing the logic of the main query.
