Combine Two Tables SQL Challenge - MySQL Solution

1. Introduction

In this blog post, we will demonstrate how to join data from two MySQL tables. The Person and Address tables serve as our examples, showcasing a practical scenario in database management where related data is merged for comprehensive insights.

Problem

Our task is to report the first name, last name, city, and state of each individual in the Person table. If an individual’s address isn’t available in the Address table, null values should be shown for both city and state.

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. MySQL Solution

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

Output:

Output Table:

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

Explanation:

1. A LEFT JOIN is used to merge the Person and Address tables.

2. The query selects firstName and lastName from Person.

3. city and state are sourced from Address.

4. If there's no corresponding personId in Address, null values are shown for city and state.