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 |
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.
Comments
Post a Comment