175 Combine Two Tables

Description

Table: Person

• personId is the primary key column for this table.
• This table contains information about the ID of some persons and their first and last names.

Table: Address

• addressId is the primary key column for this table.
• Each row of this table contains information about the city and state of one person with ID = PersonId.

Problem Statement:

Write an SQL query 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 query result format is in the following example.

Example 1:

Input:

• Person table:

• Address table:

Output:

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

In this case person and address information are stored in two separate normalized tables. This is quite common design in the real world. In this question, we need information from both tables (i.e. firstName, lastName from the Person table and city, state from the Address table). Therefore, it is clear that we need a join between these two tables.

Now, the problem states that even if the person does not have an address, we still need to output the person information. This indicates that we need a LEFT OUTER JOIN with the Address table. This will ensure that you include the person information even if they do not have an address.

Below is one way to solve this problem.

1SELECT firstName, lastName, city, state FROM Person
2    LEFT OUTER JOIN Address ON Person.personId = Address.personId;