175. Combine Two Tables

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

FirstName, LastName, City, State

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

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

JOIN Clause

Solution

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. Considering there might not be an address information for every person, we should use outer join instead of the default inner join.

SELECT
    p.FirstName,p.LastName,a.City,a.State
FROM 
    Person p
LEFT OUTER JOIN
    Address a
ON 
    p.PersonId=a.PersonId;