阅读之前,请牛刀小试: https://leetcode-cn.com/problems/combine-two-tables/
本系列教程,基于19个案例来探索SQL编程的艺术。 SQL 是一门结构化查询语言,更是一门面向集合的语言。
一 、题目:组合两个表
表1: Person
列名 | 类型 |
---|---|
PersonId | int |
FirstName | varchar |
LastName | varchar |
PersonId 是上表主键
表2: Address
列名 | 类型 |
---|---|
AddressId | int |
PersonId | int |
City | varchar |
State | varchar |
AddressId 是上表主键
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
应用场景
两表组合,应用场景十分普遍,常常要求做为主表(外表),查询该表中所有数据,即要求主表中出现的数据时唯一的,例如人员列表等。
二、准备:创建表插入数据
CREATE TABLE IF NOT EXISTS Person (
PersonId INT PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255)
);
CREATE TABLE IF NOT EXISTS Address (
AddressId INT PRIMARY KEY,
PersonId INT,
City VARCHAR(255),
State VARCHAR(255)
);
三、解法:条条大路
通过多种解法来求取结果,每种解法都有最优和最糟的场景。如果优化,则需要根据执行计划去做调优。
3.1 解法一 、左外连接
思路: LEFT JOIN
SELECT FirstName,LastName,City,State
FROM Person p
LEFT JOIN Address a ON p.Personid = a.PersonId;
注:使用右外连接也可以
原理探索:
- JOINS 连接理解
- JOIN 后面的ON 条件和where后面的条件比较
连接思想
LEFT JOIN
两个表进行连接,保留外表所有数据,
只需要读懂下面一张图,各种连接信手拈来!
JOIN 后面的ON 条件和where后面的条件比较
1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。左表所有数据 拼接 右表符合on条件的数据。on后用and连接
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录),条件不为真的就全部过滤掉。
补充:使用关键字 USING,使用较少,要求字段一致。
SELECT p.FirstName,p.LastName,a.City,a.State
FROM Person p LEFT JOIN Address a USING(PersonId)
3.2 解法二 、标量子查询
思路: 子查询
SELECT p.FirstName,p.LastName,
(SELECT c.City FROM Address c WHERE c.PersonId = p.PersonId) AS City,
(SELECT s.State FROM Address s WHERE s.PersonId = p.PersonId) AS State
FROM Person P;
注意:
-
这种做法不适合输出不在主表中的字段过多,通常一个最适宜。
-
在使用标量子查询的时候需要保证不会出现多条记录,如何在可能出现多个记录的情况,可以使用limit 关键字限制记录数。
3.3 知识点简单扩展:执行计划
执行计划设计的影响面比较多,这两种写法的销量孰高孰低需要根据实际的执行计划来结论。 通过一两篇文章估计也很难阐述明白,系列文章中会穿插的讲解一些基础知识。
执行计划:
对执行计参数描述如下:简单理解。
执行计划比对
简单入门,对执行计划有一个简单认识,后续篇章也会涉及一些相关知识。两者执行计划如下:
四、举一反三
- 重点:join 连接理解,集合思想
- 收获:多表查询;标量子查询应用
- 展望:熟悉掌握执行计划,从而优化SQL。