Combine Two Tables SQL Challenge - Oracle Solution

1. Introduction

This blog post focuses on demonstrating how to join two tables in an Oracle database environment. We use the Person and Address tables to illustrate this common database operation.

Problem

The goal is to merge the Person and Address tables to report the first name, last name, city, and state of each individual in the Person table. If a person’s address is not listed in the Address table, the city and state should be reported as null.

Database Table:

Person Table:

personId	lastName	firstName
1	Wang	Allen
2	Alice	Bob

Address Table:

addressId	personId	city	state
1	2	New York City	New York

3. Oracle Solution

SELECT p.firstName, p.lastName, a.city, a.state
FROM Person p
LEFT JOIN Address a ON p.personId = a.personId;

Output:

firstName	lastName	city	state
Allen	Wang	Null	Null
Bob	Alice	New York City	New York

Explanation:

1. The query utilizes a LEFT JOIN to merge the Person and Address tables.

2. It selects firstName and lastName from the Person table.

3. The city and state are taken from the Address table.

4. When there is no corresponding personId in the Address table, null values are shown for city and state.