SQL实现LeetCode(175.联合两表)

[leetcode] 175.combine two tables 联合两表

table: person

+-------------+---------+
| column name | type    |
+-------------+---------+
| personid    | int     |
| firstname   | varchar |
| lastname    | varchar |
+-------------+---------+
personid is the primary key column for this table.

table: address

+-------------+---------+
| column name | type    |
+-------------+---------+
| addressid   | int     |
| personid    | int     |
| city        | varchar |
| state       | varchar |
+-------------+---------+
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

leetcode还出了是来到数据库的题，来那么也来做做吧，这道题是第一道，相对来说比较简单，是一道两表联合查找的问题，我们需要用到join操作，关于一些join操作可以看我之前的博客sql left join, right join, inner join, and natural join 各种join小结，最直接的方法就是用left join来做，根据personid这项来把两个表联合起来：

解法一：

select person.firstname, person.lastname, address.city, address.state from person left join address on person.personid = address.personid;

在使用left join时，我们也可以使用关键using来声明我们相用哪个列名来进行联合：

解法二：

select person.firstname, person.lastname, address.city, address.state from person left join address using(personid);

或者我们可以加上natural关键字，这样我们就不用声明具体的列，mysql可以自行搜索相同的列：

解法三：

select person.firstname, person.lastname, address.city, address.state from person natural left join address;

参考资料：

到此这篇关于sql实现leetcode(175.联合两表)的文章就介绍到这了,更多相关sql实现联合两表内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！