8. Combine Two Tables

Here's the problem presented by the interviewer, which comes from LeetCode.

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

Define the Problem

The problem is a bit tricky, but it's been defined fairly well.

---

Think

Jonathan thinks about the problem for a bit, particularly about the Sequel syntax. Here's his pseudocode:

/* Perform outer join so that columns are retrieved, whether or not they contain information.*/

---

Write Code and Syntax

Now that the hard part is done, Jonathan just needs to write out the syntax to fill in the pseudocode. As Jonathan writes the code, he explains the syntax.

/* Perform outer join so that columns are retrieved, whether or not they contain information.*/
SELECT FirstName, LastName, City, State
FROM Person LEFT JOIN Address
ON Person.PersonId = Address.PersonId
;

Jonathan: I performed an outer left-join between the Person and Address tables in order to get all information, whether or not the data exists. The join is done on the primary key from Person matching the foreign key on Address.