Combine Two Tables

You are given two tables containing information about people: one with their basic personal details and another with their address information. Your task is to combine these two tables to create a single output that lists each person along with their full name and address. This is a common operation in data management and analysis, where you need to join related information from different sources.

Problem Description

You are provided with two tables: Person and Address.

The Person table contains the following columns:

• personId: A unique identifier for each person (integer).
• firstName: The first name of the person (string).
• lastName: The last name of the person (string).

The Address table contains the following columns:

• addressId: A unique identifier for each address (integer).
• personId: The identifier of the person to whom this address belongs (integer). This is a foreign key referencing personId in the Person table.
• street: The street name of the address (string).
• city: The city of the address (string).

Your goal is to write a query that returns a table with the following columns:

• firstName: The first name of the person.
• lastName: The last name of the person.
• street: The street of the person's address.
• city: The city of the person's address.

You need to combine the information from both tables based on the personId.

Key Requirements:

• Retrieve the firstName, lastName from the Person table.
• Retrieve the street, city from the Address table.
• Join the tables on the personId column.
• Ensure that even if a person does not have an address listed in the Address table, they should still be included in the output, with their street and city fields being NULL.

Edge Cases to Consider:

• A person might exist in the Person table but not have a corresponding entry in the Address table.
• It is assumed that each personId in the Address table will have a corresponding personId in the Person table.

Examples

Example 1:

Input:

Person table:

Address table:

Output:

Explanation:

• John Doe (personId 1) has an address on Main St in Anytown.
• Jane Smith (personId 2) has an address on Oak Ave in Otherville.
• Peter Jones (personId 3) exists in the Person table but has no corresponding entry in the Address table, so their street and city are NULL.

Example 2:

Input:

Person table:

Address table:

Output:

Explanation:

• Alice Wonderland (personId 1) has two addresses listed. Both should be included in the output, duplicating her name and last name for each address.

Constraints

• personId in Person table is unique.
• personId in Address table is not necessarily unique (a person can have multiple addresses).
• The number of rows in Person table can be up to 1000.
• The number of rows in Address table can be up to 5000.
• firstName, lastName, street, and city are strings and can be empty.
• Performance is important; the solution should be efficient for larger datasets.

Notes

• Consider which type of join operation is most suitable for this problem to handle cases where a person might not have an address.
• You will be writing a query to retrieve data, not modifying it.