SQL语法基础-多表查询（+索引简介）

一、多表查询简介

表与表之间的关系有3种：

• ①一对一；
• ②一对多；
• ③多对多。

其中一对多和多对一实际上是相同的，只是角度不同而已。
在SQL中，多表连接的方式主要有以下4种：

• 联合查询
• 内连接
• 外连接
• 笛卡尔积连接

二、联合查询

表的集合运算，跟数学中的集合运算是非常相似的。对于表的集合运算，主要包括以下3种。

1. 并集（union）
2. 交集（intersect）
3. 差集（except）

在Oracle中，我们可以使用union关键字来对两个表求并集。求两个表的并集，也就是对两个表进行加法运算。这种求并集的操作，也叫做“联合查询”。
语法：
select 列名 from 表A
union
select 列名 from 表B;

对于表的求并集（union），我们可以总结出以下重要的3点。

1. 对于求并集的两个表来说，它们的结构必须完全相同，包含列数相同、类型相同等。
2. union语句必须由2条或2条以上的select语句组成，然后彼此之间使用union关键字来分开。
3. 在union语句中，只能使用一条order by子句或限制子句，并且它们必须放在最后一条select语句之后。

P.S.：这里有需要注意的一点，有的时候我们会习惯使用union all，单独使用union的时候，是将上下的结果全部联合起来，并且会去重，也就是每一行数据是不会重复的。使用union all的时候，是将上下的数据全部联合起来，不做任何其他处理！

三、内连接

对于表的集合运算来说，它本质上是以“行”为单位进行操作的。而对于表的连接运算（包括内连接和外连接），则是以“列”为单位进行操作的

内连接返回两个或多个表中存在匹配关系的行。如果某行在连接的表中没有匹配，那么这些行就不会被返回。

1、基本语法

在SQL中，我们可以使用inner join关键字来实现内连接。所谓的内连接，指的是多个表通过“共享列”来进行连接。在实际使用中，inner经常省略掉
语法：
select 列名
from 表A
(inner) join 表B
on 表A.列名 = 表B.列名;

2、进一步了解

（1）单表查询：对于单表查询来说，列名前面的表名前缀是可以省略的，也就是“表名.列名”可以直接写成“列名”。

-- 简写方式
select name, species, breed
from pets;

-- 完整写法
select pets.name, pets.species, pets.breed
from pets;

（2）using（列名）：下面两种方式是等价的，也就是on pets.client_id=clients.client_id可以等价于using(client_id)。

-- 方式1
select *
from pets
join clients
on staff.client_id = market.client_id;

-- 方式2
select *
from pets
join clients
using(client_id);

P.S.：当使用方式2的时候，查询展示出来的所有字段中，关联字段只会出现一次，而使用方式1的时候，关联字段会全部展示出来。但是在实际开发应用中，为了查询速度更快，往往是select出需要的字段，这样的情况下，个人习惯或者喜欢使用哪种方式都是OK的！

（3）连接多个表：在SQL中，内连接（inner join）不仅可以连接两个表，还可以同时连接多个表（3个或3个以上）。如果想要连接多个表，我们只需要多次使用“inner join…on…”即可。

select 列名
from 表A
inner join 表B on 连接条件
inner join 表C on 连接条件
……
;

（4）查询条件：对于内连接的查询条件，我们并不一定要使用“=”。除了等值连接，我们还可以使用非等值连接。所谓的非等值连接，指的是on子句使用的是除了等号（=）的其他比较运算符（如>、>=、<、<=、<>等）进行的连接，比如：pets.client_id<> clients.client_id

SELECT p.name AS 宠物名称, c.client_name AS 主人名称
FROM pets p
JOIN clients c 
ON p.client_id = c.client_id;

四、外连接

在SQL中，根据连接时要提取的是哪个表的全部记录，外连接可以分为以下3种类型。

• 左外连接：根据左表来提取结果。
• 右外连接：根据右表来提取结果。
• 完全外连接：同时对左表和右表提取结果。

1、左外连接

左外连接，指的是根据“左表”来获取结果。在Oracle中，我们可以使用left outer join来实现左外连接。返回左表（表A）的所有行，即使右表（表B）中没有匹配的行。实际应用中，outer经常是省略的
语法：
select 列名
from 表A
left (outer) join 表B
on 表A.列名 = 表B.列名;

SELECT p.name AS 宠物名称, c.client_name AS 主人名称
FROM clients c
LEFT JOIN pets p 
ON c.client_id = p.client_id;

2、右外连接

右外连接，指的是根据“右表”来获取结果。在SQL中，我们可以使用right outer join来实现右外连接。返回右表（表B）的所有行，即使左表（表A）中没有匹配的行。
语法：
select 列名
from 表A
right (outer) join 表B
on 表A.列名 = 表B.列名;

SELECT p.name AS 宠物名称, c.client_name AS 主人名称
FROM clients c
PRGHT JOIN pets p 
ON c.client_id = p.client_id;

3、完全外连接

像SQL Server等DBMS中，我们可以使用full outer join来实现完全外连接。所谓完全外连接，指的是连接之后同时保留左表和右表的所有记录，它相当于左外连接和右外连接的并集。
不过Oracle并没有提供full outer join这样的方式，如果想要在Oracle中实现完全外连接，我们可以使用一种“曲线救国”的方式：首先获取左外连接的结果，然后获取右外连接的结果，最后使用union求并集即可。

在实际开发中，我们应该清楚以下两点：

1. 对于多表连接来说，最常用的是内连接，外连接用得比较少。
2. 如果使用外连接，一般只会用到左外连接，个别情况会用到完全外连接。

五、笛卡尔积连接

笛卡儿积连接，也叫做“交叉连接”，它指的是同时从多个表中查询数据，然后组合返回数据。返回第一个表中的每一行与第二个表中每一行的笛卡尔积。如果第一个表有X行，第二个表有Y行，那么结果集将会有X*Y行。笛卡儿积连接的特殊之处在于，如果它不使用where子句指定查询条件，那么它就返回多个表的全部记录。
语法：
select 列名
from 表名1
CROSS JOIN 表名2;

P.S.：或者省略CROSS JOIN关键词，仅通过逗号分隔表名。

在Oracle中，笛卡儿积连接有两种写法：一种是使用英文逗号（,）隔开，另一种是使用cross join关键字。下面两种写法是等价的。

-- 写法1
select 列名
from 表名1, 表名2;

-- 写法2
select 列名
from 表名1 cross join 表名2;

笛卡尔积连接返回左表的每一行与右表的每一行的所有可能组合，通常不推荐使用，除非你确实需要这种类型的数据组合。在实际应用中，笛卡尔积很少直接使用，因为它会生成大量数据，
在Oracle中，笛卡儿积连接中经常是“犯错”的一个结果，一般情况下，要求的数据是一对一或是一对多，结果由于条件的不足，导致了查询结果的错误（也就是数据量更多了）

SELECT c.client_name AS 主人名称, p.name AS 宠物名称
FROM clients c
CROSS JOIN pets p 

SELECT c.client_name AS 主人名称, p.name AS 宠物名称
FROM clients c,pets p 

SELECT c.client_name AS 主人名称, p.name AS 宠物名称
FROM clients c,pets p 
WHERE c.client_id = ‘1’

六、自连接

在SQL中，还有一种很特殊的多表连接方式——自连接。在自连接时，连接的两张表是同一张表，然后我们一般需要通过起一个别名来进行区分。
语法：select 列名 from 表名1 as 别名1, 表名2 as 别名2;

注意事项

1.在执行连接操作时，合适的索引和连接条件的选择对于查询性能至关重要。
2.对于外连接，如果在一个表中没有找到匹配的行，那么这个表中的列将返回NULL值。
3.在使用连接时，明确指定连接的类型可以帮助提高SQL语句的可读性和维护性。

七、索引简介

索引是建立在数据表中列上的一个数据库对象，在一个表中可以给一列或者多列设置索引。如果在查询数据时，使用了设置的索引列作为查询列，那么就会大大提高查询速度。

1.创建索引

在SQL中，我们可以使用create index语句来创建一个索引。需要注意的是，我们只能对表进行创建索引，而不能对视图进行创建索引。
语法：create index 索引名 on 表名(列名);

【常见问题】

1、针对某一列建立索引，可以提高该列的查询速度。那么是不是意味着我们给所有列都建立索引，这样更好呢？
虽然索引可以提高列的查询速度，但是过多地使用索引，却会降低SQL本身的系统性能，主要包括以下两点:
- 过多的索引，会降低修改表数据的速度。
- 过多的索引，会增加存储空间。

2、对于使用索引，有什么好的建议吗？ 
虽然索引可以提高列的查询速度，但是过多地使用索引，却会降低SQL本身的系统性能，主要包括以下两点:
 - 数据量较小的表，最好不要建立索引。
 - 在有较多不同值的字段上建立索引。

2.查看索引

在SQL中，我们可以使用show index语句来查看一个索引的基本信息。
语法：show index from 表名;

3.删除索引

在SQL中，我们可以使用drop index语句来删除一个索引。
语法：drop index 索引名 on 表名;

对于索引的其他内容，后续将会在提高篇中再次提及。作为一般查询的工作者是比较少设计的，只要针对“查询条件”的字段给予索引一般使用就够了！