Combine Two Tables — Day 79(SQL)

Photo by Michael Dziedzic on Unsplash

Today’s question is based on SQL. A very easy question. Let us look into the problem statement.

175. Combine Two Tables

Table:

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

Table:

+-------------+---------+
| 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

In the problem statement, we have two tables i.e. Person and Address. We want all the information from the Person table. Address table can or cannot have all the information. The Address table is linked to the Person table through PersonId, which means PersonId is a foreign key in the Address table.

It looks like we need to be using Outer Join. We have three types of Outer Joins available, Full, Left, and Right outer join. How do we decide on which outer join to use? Since we need all the information from the Person table, we should either be using Left or Right outer join.

Let us look into the SQL statement using the left outer join.

select p.FirstName, p.LastName, a.City, a.State from Person p Left Join Address a on p.PersonId = a.PersonId

Software Engineer. Find me @ www.linkedin.com/in/annamariya-jt

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store