LeetCode – Combine two tables

Question

Write a SQL query to report the first name, last name, city, and state of each person in the Person table.

• If a person’s address is not present in the Address table, return NULL for city and state.

• Return the result in any order.

Below are the tables: Person and Address and scripits to create tables.

Table: Person

• personId is the primary key (unique for each person).

• This table contains the ID, first name, and last name of each person.

Table: Address

• addressId is the primary key (unique for each address).

• Each row contains the city and state of the person with ID = personId.

-- Person table
CREATE TABLE Person (
personId INT PRIMARY KEY,
lastName VARCHAR(255),
firstName VARCHAR(255)
);

-- Address table
CREATE TABLE Address (
addressId INT PRIMARY KEY,
personId INT,
city VARCHAR(255),
state VARCHAR(255),
CONSTRAINT fk_person FOREIGN KEY (personId) REFERENCES Person(personId)
);

-- Insert sample data
INSERT INTO Person (personId, lastName, firstName) VALUES
(1, 'Wang', 'Allen'),
(2, 'Alice', 'Bob'),
(3, 'Johnson', 'Ibrahim');

INSERT INTO Address (addressId, personId, city, state) VALUES
(1, 2, 'New York City', 'New York'),
(2, 3, 'Leetcode', 'California');

Solution

We use LEFT JOIN as we want all persons even if they do not have an address.

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