leetcode-MySQL 联合查询

背景

详情可查看 https://leetcode.com/problems/combine-two-tables/
这道题的考察点就是多表联合查询，MySQL 实现多表联合查询使用inner join，left join和right join。

实现

这道题比较简单，查询Person表中每个人的地址信息，无地址显示该用户的地址为空

SELECT Person.FirstName,Person.LastName,Address.City,Address.State FROM Person LEFT JOIN Address ON Address.PersonId=Person.PersonId;
/*等价于*/
SELECT Person.FirstName,Person.LastName,Address.City,Address.State FROM Address RIGHT JOIN Person ON Address.PersonId=Person.PersonId;

扩展思路

假设 查询Address表中每个地址对应的人的信息，该地址无相关人员则显示地址对应的用户为空

SELECT Person.FirstName,Person.LastName,Address.City,Address.State FROM Address LEFT JOIN Person ON Address.PersonId=Person.PersonId;
/*等价于*/
SELECT Person.FirstName,Person.LastName,Address.City,Address.State FROM Person RIGHT JOIN Address ON Address.PersonId=Person.PersonId;

假设 查询Person表中每个人的地址信息，无地址不显示该用户，使用 inner join 即可实现

SELECT Person.FirstName,Person.LastName,Address.City,Address.State FROM Person INNER JOIN Address ON Address.PersonId=Person.PersonId;
/*等价于*/
SELECT Person.FirstName,Person.LastName,Address.City,Address.State FROM Person,Address WHERE Address.PersonId=Person.PersonId;