LeetCode Notes: Combine Two Tables
PersonId is the primary key column for this table.
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
- 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
- Here you need to get all the fields of the left table, so use the left join
- 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
# 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;
# 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;