LeetCode Notes: Combine Two Tables

Question

Table: Person

PersonId is the primary key column for this table.

Table: Address

AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

Solution

Analysis:

1. Associated query of two tables, there are four cases
   • Left join, take all the fields of the left table
   • Right join, take all the fields of the right table
   • Inner join, take the common fields of the left and right tables
   • full join, take all the fields of the two tables
2. Here you need to get all the fields of the left table, so use the left join
3. In order to find out the required fields, the primary key of the left table must be equal to the foreign key of the left table in the right table

Code one:

# Write your MySQL query statement below

-- Rename the two tables, you can clearly see which table the four fields that need to be queried come from
select p.FirstName,p.LastName,a.City,a.State
from Person as p left join Address as a
on p.PersonId = a.PersonId;

Code two:

# Write your MySQL query statement below

-- Concise code, directly obtain the fields in the table
select FirstName,LastName,City,State
from Person left join Address
on Person.PersonId = Address.PersonId;

Reference

• 175. Combine two tables
• SQL Joins
• What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?