Combine Two Tables

SQL Leet Code Series — Easy

Source: https://unsplash.com/photos/1K6IQsQbizI

Combine Two Tables - LeetCode

SQL Schema

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

Approach :

From the title, it's pretty clear that we are expected to get data from multiple tables from a database.

From the given information PearsonId and AddressId are Primary keys

We are asked to create a SQL Query that will provide FirstName, LastName, City, State for each person in the Person table regardless if there is an address for each of those people.

Using Left Join would be the best option to go for since we want every row from the first table, regardless of whether there is a matching row from the second table.

Solution:

Note: The solution might slightly differ from DataBase to DataBase the concept and the approach are often the same. Solution for Oracle Sql 11.2.

SELECT P.FirstName,
       P.LastName,
       A.City,
       A.State
FROM Person P
LEFT JOIN Address A 
ON P.PersonId = A.PersonId;