sql，DML ~ 复杂查询

原创于 2025-01-30 19:42:27 发布 · 979 阅读

22 ·

CC 4.0 BY-SA版权

本文可以自由转载，但请注明出处和作者来源，禁止商业目的。

文章标签：

#sql #数据库

数据库同时被 2 个专栏收录

31 篇文章

订阅专栏

sql

16 篇文章

订阅专栏

综述

分为三类

第一类，子查询。

第二类，表连接，Join。

第三类，多个结果集之间的集合操作，并集，交集，差集等。

子查询

格式：

SELECT

column1, column2, ... (scalar_subquery)

FROM table1, ... (nested_table_subquery) AS subquery_table_name]

WHERE foo = (scalar_subquery) OR foo IN (table_subquery)

说明：

scalar_subquery：标量子查询，结果集返回单一列，单一行的值，会造成N + 1查询。通常子查询的表与外部查询的表有主外键关系。

table_subquery：临时结果集子查询，返回单列多行，通常会作为IN, ANY, SOME, ALL等的条件。

nested_table_subquery：嵌套表子查询，返回结果集的行和列没有限制。

应用场景：

子查询核心点在于它返回的结果集，分三种情况，单值，单列，多行多列（没限制）。

select：要求单值，所以只能是scalarsubquery

from：join部分，通常是嵌套表子查询，通常单列没有join的意义。

where：等值条件时，使用单值。in，any, some, all 时使用单列。exist 通常为结果集。

group: 通常是从select部分中取某列作为分组依据，所以通常要求单值。

Having：与where类似。

Order：与group类似。

示例：

-- 查询角色名称, 标量子查询
select 
    (select role_name from role where id =user.role_id) as role_name

table_subquery,这类子查询通常作为条件

select
    * 
from students 
where score > (select avg(score) from students)

nested_table_query,嵌套子查询。通常可以用Join代替，实际使用比较少，理论上所有嵌套子查询都可以转换为Join。

连接

Join有以下三种类型，

笛卡尔积，cross join：结果集中任意行与任意行的组合。假设A有4行，B有3行，那么会产生12行，A cross join B 与 B cross join A是一样的。

left &right join：其中一个结果集为主，其余为辅，辅表结果集中的行不满足条件时，会匹配一行null。通常由主表决定最终Join结果集的行数。

inner join：结果集中都需要满足条件，不满足条件的行会被排除掉。

通用格式：

FROM table[AS alias] {[join_type] JOIN [LATERAL] joined_table [[AS] alias]

{ ON join_condition1 [{AND | OR}join_condition2] [...] |

USING (column1[, ...]) }}

说明：

table [asalias]：参加连接的结果集，通常是表名称。

Join type：连接的类型。cross join笛卡尔积；left & right & full，左连接，右连接，全连接；inner join，内连接；natural join，自然连接，本质是inner join，条件为结果集中所有相同名称的列，例如A(id, name, age), B(id,name, date)，自然连接会自动生成条件a.id = b.idand a.name = b.name。

Lateral：后面跟随结果集，可以使用其他结果集的列名称，例如A(id, name, age), lateral (select statement)，这个statement中可以使用A的列名，类似于关联子查询。

Join condition：1到多个Join条件，使用and或or进行连接。

Using(column1): 当结果集中的列名称相等，且使用等值条件时，使用using可以简化。例如a.id = b.id，使用using(id)可以简化。在natural join中可以指定列。

2.1 笛卡尔积

概念：

Cartesian products are used fairly frequently by accident but are not so common otherwise.If, however, you do intend to generate the Cartesian product of two tables, you should specify a cross join

Cross join，笛卡尔积。

注意：通常不使用，因为性能非常低，而且返回的结果集重复行数太多。

2.2 left & right & full join

概念：

left outer join. The keyword left indicates that the table on the left side of the join isresponsible for determining the number of rows in the result set, whereas thetable on the right side is used to provide column values whenever a match is found.

左连接，右连接类似，full连接不区分主，辅表，不匹配的行会生成一行null。

说明：假设A(id, name, age), B(id, name, date)

a left join b on a.id = b.id // a为主，b为辅，当b中找不到a的id时，会生成一行null数据，即b.id, b.name,b.date都为null。

right join，改变方向，a right join b，则b为主，a为辅。

full join，不分主，辅，不匹配的行都会生成一行null。

注意：结果集的行数通常是由主表决定的，所以减少主表的数据量可以提升性能。