175. Combine Two Tables


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

b'
\n
\n\n
\n

Solution

\n
\n

Approach: Using outer join [Accepted]

\n

Algorithm

\n

Since the PersonId in table Address is the foreign key of table Person, we can join this two table to get the address information of a person.

\n

Considering there might not be an address information for every person, we should use outer join instead of the default inner join.

\n

MySQL

\n
select FirstName, LastName, City, State\nfrom Person left join Address\non Person.PersonId = Address.PersonId\n;\n
\n
\n

Note: Using where clause to filter the records will fail if there is no address information for a person because it will not display the name information.

\n
\n
'