【SQL练习】175. Combine Two Tables

最新推荐文章于 2024-06-13 13:38:57 发布

墨染百城

最新推荐文章于 2024-06-13 13:38:57 发布

阅读量678

点赞数

分类专栏： SQL练习文章标签： SQL练习

本文链接：https://blog.youkuaiyun.com/mrbcy/article/details/68483777

版权

SQL练习专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个SQL查询案例，通过左连接的方式将Person表与Address表进行联合查询，并对比了不同类型的左连接操作符（LEFT JOIN, LEFT JOIN USING, NATURAL LEFT JOIN）在执行效率上的差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题连接：https://leetcode.com/problems/combine-two-tables/#/description

问题描述

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

我的代码

思路很容易，就是用左连接把它们连起来而已。

    select FirstName,LastName,City,State
    from Person left join Address on (Person.PersonId = Address.PersonId)

讨论区

Comparative solution between LEFT JOIN, LEFT JOIN USING and NATURAL LEFT JOIN

链接地址：https://discuss.leetcode.com/topic/21917/comparative-solution-between-left-join-left-join-using-and-natural-left-join/3

比较了一下left join,left join using, natural left join直接的区别。

basic left join: 902ms.

SELECT FirstName, LastName, City, State
FROM Person
LEFT JOIN Address
ON Person.PersonId = Address.PersonId;

left join + using: 907ms

SELECT FirstName, LastName, City, State
FROM Person
LEFT JOIN Address
USING(PersonId);

natural left join: 940ms

SELECT FirstName, LastName, City, State
FROM Person
NATURAL LEFT JOIN Address;

left join is the fastest compare to the two others.