Abstract / Summary

Retrieve all rows from one table and matching rows from another using their shared key.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 80% (2+ million submissions).

Typical patterns to solve this problem:

• Database

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The query retrieves personal identifiers together with location details, preserving every person record even when a matching address is absent. It combines data from two tables, Person and Address, using a left outer join to ensure that the result set contains all rows from the Person table while optionally attaching address information.

Key Steps

Column Selection

The SELECT clause enumerates the fields that will appear in the output. The chosen columns are the first name and last name from the Person table and the city and state from the Address table.

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

Join Operation

The LEFT JOIN clause links each person to an address based on a shared identifier. The join condition matches the personId column from both tables, allowing rows from Person that lack a corresponding entry in Address to still appear with NULL values for the address fields.

FROM Person p LEFT JOIN Address a ON (p.personId = a.personId)

Result Construction

After the join, the query assembles the selected columns into a single result set. Because the join is left-outer, any person without an address will have NULL for a.city and a.state, while persons with one or more matching address rows will produce a row for each address match.

Edge Cases Handled

When a person does not have a related address record, the join condition fails to find a match, yet the left-outer semantics guarantee that the person’s name fields are still returned with NULL placeholders for city and state. This behavior prevents loss of person data in scenarios where address information is incomplete or optional.

If the Address table contains multiple rows for the same personId, the join will generate multiple result rows for that person, each paired with a distinct address. This multiplicity is inherent to the relational model and is correctly reflected in the output without causing errors.

The query also tolerates NULL values in the firstName or lastName columns of the Person table; such rows are included unchanged, and the presence of NULL in these fields does not affect the join operation because the join predicate references only the personId columns.

Performance Optimizations

The join predicate relies on equality between p.personId and a.personId. When both tables have indexes on the personId column, the database engine can perform an index-nested-loop or hash join efficiently, reducing the amount of data scanned.

A covering index on Address that includes personId, city, and state allows the join to be satisfied entirely from the index without accessing the full table rows, minimizing I/O and improving throughput.

Because the query selects only a subset of columns, the optimizer can apply a projection early in the execution plan, trimming unnecessary data before the join phase. This early projection reduces the volume of intermediate rows that must be processed.

The left-outer join is appropriate when address data is optional; using it avoids the need for a separate query to fetch persons without addresses, thereby eliminating an extra round-trip and consolidating the logic into a single execution path.

Overall, the combination of indexed join keys, potential covering indexes, and early column projection contributes to a streamlined execution that scales well with larger data volumes.

Imagine there is a big collection of index cards that hold information about all the people in a giant city. Some of these cards have their names written on the front, and some of the cards have their home addresses attached to them. This code is like a magical robot helper that walks over to the cabinet to find this information. It looks at the pile of name cards first. Then, it carefully checks if each person has a matching address card that belongs to them based on a special secret number printed on both. It pulls the first name, last name, city, and state off these cards and lays them out side by side on a table so everything is easy to see at once. Even if a person does not have an address card in the pile, the robot still brings over their name so no one gets left out of the final list.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

not analyzed yet

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		Python	C++	Java	TS
	WalkCCC	SQL
	leetcode.ca	SQL	Python
	LeetcodeHelp		Python	C++	Java
	Progiez		Python	C++	Java
github	everthis	SQL
	fsq	SQL
	gaosq0604		Python
	keineahnung2345	SQL
	lzl124631x	SQL
	muhammadfarhankt	SQL
	suxrobGM	SQL
	trung-hn	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	Python, SQL
LeetcodeHelp	C++, Java, Python
Progiez	C++, Java, Python
everthis	SQL
fsq	SQL
gaosq0604	Python
keineahnung2345	SQL
lzl124631x	SQL
muhammadfarhankt	SQL
suxrobGM	SQL
trung-hn	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality