Combine Two Tables - SQL

When working with relational databases, a common task is to combine data from multiple tables. This concept is illustrated well in the LeetCode problem "Combine Two Tables". In this post, we will explore how to approach this problem using SQL. 

Problem Overview 

The problem presents us with two tables: 

Person Table: Contains personId (primary key), lastName, and firstName. 

Address Table: Contains addressId (primary key), personId, city, and state. 

Our objective is to write an SQL query that returns the first name, last name, city, and state of each person in the Person table. If a person does not have an address listed in the Address table, we should return null for their city and state. 

Understanding the Data Structure 

Here are the given tables with sample data: 

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

SQL Query Solution Approach 

The solution requires a LEFT JOIN operation. We'll join the Person table with the Address table based on the personId. This join ensures that all records from the Person table are included in the output, and if there's no corresponding personId in the Address table, the city and state will be null. 

MySQL Solution

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

PostgreSQL Solution 

PostgreSQL uses the same syntax as MySQL:

-- Write your PostgreSQL query statement below
SELECT
    p.firstName,
    p.lastName,
    a.city,
    a.state
FROM
    Person p
LEFT JOIN
    Address a ON p.personId = a.personId;

Oracle Solution 

Oracle SQL follows a similar syntax:

/* Write your PL/SQL query statement below */
SELECT
    p.firstName,
    p.lastName,
    a.city,
    a.state
FROM
    Person p
LEFT JOIN
    Address a ON p.personId = a.personId;

Conclusion 

Combining tables is a fundamental skill in SQL, and the "Combine Two Tables" problem on LeetCode offers a great scenario to practice this skill. The key takeaway is understanding how LEFT JOIN works and how it can be used to merge data from related tables while handling cases where some records do not have corresponding data in both tables.