Combine Two Tables SQL Challenge - PostgreSQL Solution

1. Introduction

This blog post demonstrates how to merge data from two tables in a PostgreSQL database. We'll be using the Person and Address tables as examples to illustrate this process.

Problem

Our aim is to combine data from the Person and Address tables to display each person's first name, last name, city, and state. When a person's address isn't available in the Address table, we need to show null values for the 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
2	3	Leetcode	California

3. PostgreSQL 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. A LEFT JOIN combines the Person and Address tables.

2. We select firstName and lastName from Person.

3. The city and state are taken from Address.

4. If there's no matching personId in Address, null is shown for city and state.