SQL：子查询&&组合查询&&联结查询

短命女 2022-03-28 04:13 530阅读 0赞

# 1、子查询 #

*  多个select语句，可以查询一个表，也可以查询多个表

> 子查询就是指在一个select语句中嵌套另一个select语句。同时，子查询必须包含括号。  
> `MySQL 5.6.x` 版本之前，MySQL的子查询性能较差，但是从5.6开始，不存在性能差的问题。

select a from t1 where a > any(select a from t2);

1.  `select a from t1` 是外部查询(outer query)
2.  `(select a from t2)` 是子查询

一般说来，子查询嵌套于外部查询中，可以将两个或两个以上的子查询进行嵌套

## 1.1、 子查询的使用 ##

### (1) ANY / SOME ###

如果外部查询的列的结果和子查询的列的结果比较得到为True的话，则返回比较值为True的（外查询）的记录

mysql> create table t1(a int);
    mysql> create table t2(a int);
    
    mysql> insert into t1 values(10),(4);
    mysql> insert into t2 values(12),(13),(5);
    
    mysql> select a from t1 where a > any(select a from t2);
    +------+
    | a    |
    +------+
    |   10 |   -- 10 比 5 大为True，则返回该值，4比t2中所有的a值小，为False
    +------+
    mysql> select a from t1 where a > some(select a from t2);
    +------+
    | a    |
    +------+
    |   10 |
    +------+

推测，如果在t1中插入15，则15也会返回

> `ANY`关键词必须与一个`比较操作符`一起使用： `=`, `>`, `<`, `>=`, `<=`, `<>` *(这个是!=的意思)*

> **子查询中`SOME`和`ANY`是同一个意思**

### (2)IN ###

`in`是`ANY`的一种特殊情况：**`"in"`** `equals` **`"= any"`**

mysql> insert into t1 values(5);  -- 向t1中插入一个t2中存在的值 5
    
    mysql> select a from t1 where a = any(select a from t2); -- t1.a==t2.a 的只有5
    +------+
    | a    |
    +------+
    |    5 |
    +------+
    
    mysql> select a from t1 where a in (select a from t2); -- in的结果等同于 =any 的结果
    +------+
    | a    |
    +------+
    |    5 |
    +------+

> `select a from s1 where a in (select a in t2);`是用的比较多的一种语法

### (3)ALL ###

mysql> truncate t1;   -- 清空t1
    mysql> truncate t2;   -- 清空t2
    
    mysql> insert into t1 values(10),(4);
    mysql> insert into t2 values(3),(4),(5);
    
    mysql> select a from t1 where a > all(select a from t2);
    +------+
    | a    |
    +------+
    |   10 |  -- (10 > 5, 4, 3 为 True) 而 (4 >5, 4, 3 为 False)
    +------+

> `ALL`关键词必须与一个`比较操作符`一起使用  
> `NOT IN` 是 `<> ALL`的别名

## 1.2、子查询的分类 ##

*  **独立子查询**
    
     *  不依赖外部查询而运行的子查询

mysql> select a from t1 where a in (1,2,3,4,5);

*  **相关子查询**
    
     *  引用了外部查询列的子查询

-- 在这个例子中，子查询中使用到了外部的列t2.a 
    mysql> select a from t1 where a in (select * from t2 where t1.a = t2.a);

## 1.3、 子查询的优化 ##

*  **MySQL5.6之前**

在`MySQL5.6`之前，优化器会把子查询重写成`exists`的形式

select a from t1 where a in (select a from t2); -- 这个是一条独立的子查询，时间复杂度 O(M+N)
    --
    -- 经过优化器重写后
    --
    select a from t1 where exists (select 1 from t2 where t1.a = t2.a); -- 这是相关子查询，复杂度O(M*N + M)

所以在`MySQL 5.6`之前，部分的子查询需要重写成join的形式 *（注意表的大小）*

mysql> select t1.a from t1 join t2 on t1.a = t2.a;
    +------+
    | a    |
    +------+
    |    4 |
    +------+

*  **MySQL 5.6之后**  
    在`MySQL 5.6`之后，优化器`不会`将子查询`重写`成`exists`的形式，而是自动优化，性能有了大幅提升

> 可通过`explain extended`来查看子查询优化的结果。

> 待续

> `EXISTS`不管返回值是什么，而是看是否有`行`返回，所以`EXISTS`中子查询都是`select *`、`select 1`等，因为只关心返回是否有行（结果集）

# 2、组合查询 #

1.  `UNION` 的作用是将两个查询的结果集进行合并。
2.  UNION必须由`两条或两条以上`的SELECT语句组成，语句之间用关键字`UNION`分隔。
3.  UNION中的每个查询必须包含相同的列（`类型相同或可以隐式转换`）、表达式或聚集函数。

mysql> create table test_union_1(a int, b int);
    Query OK, 0 rows affected (0.18 sec)
    
    mysql> create table test_union_2(a int, c int);
    Query OK, 0 rows affected (0.15 sec)
    
    mysql> insert into test_union_1 values(1, 2), (3, 4), (5, 6), (10, 20);
    Query OK, 4 rows affected (0.06 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    mysql> insert into test_union_2 values(10, 20), (30, 40), (50, 60);
    Query OK, 3 rows affected (0.03 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from test_union_1;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 |    2 |
    |    3 |    4 |
    |    5 |    6 |
    |   10 |   20 |  -- test_union_1 中的10, 20
    +------+------+
    4 rows in set (0.00 sec)
    
    mysql> select * from test_union_2;
    +------+------+
    | a    | c    |
    +------+------+
    |   10 |   20 | -- test_union_2 中的10, 20
    |   30 |   40 |
    |   50 |   60 |
    +------+------+
    3 rows in set (0.00 sec)
    
    mysql> select a, b as t from test_union_1
        -> union
        -> select * from test_union_2;
    +------+------+
    | a    | t    |
    +------+------+
    |    1 |    2 |
    |    3 |    4 |
    |    5 |    6 |
    |   10 |   20 | -- 只出现了一次 10, 20，union会去重
    |   30 |   40 |
    |   50 |   60 |
    +------+------+
    6 rows in set (0.00 sec)
    
    mysql> select a, b as t from test_union_1
        -> union all   -- 使用 union all 可以不去重
        -> select * from test_union_2;
    +------+------+
    | a    | t    |
    +------+------+
    |    1 |    2 |
    |    3 |    4 |
    |    5 |    6 |
    |   10 |   20 | -- test_union_1 中的10, 20
    |   10 |   20 | -- test_union_2 中的10, 20
    |   30 |   40 |
    |   50 |   60 |
    +------+------+
    7 rows in set (0.00 sec)
    
    mysql> select a, b as t from test_union_1 where a > 2
        -> union
        -> select * from test_union_2 where c > 50;  -- 使用where过滤也可以
    +------+------+
    | a    | t    |
    +------+------+
    |    3 |    4 |
    |    5 |    6 |
    |   10 |   20 |
    |   50 |   60 |
    +------+------+
    4 rows in set (0.00 sec)

*  规则：
    
     *  UNION从结果查询中默认自动去除了重复行。如果想不去除重复行，请用UNION ALL
     *  如果知道数据本身具有唯一性，没有重复，则建议使用`union all`，因为`union`会做`去重操作`，性能会比`union all`要低
     *  UNION ALL可以完成WHERE完成不了的工作：当需要每个条件匹配全部出现包括重复行，用UNION ALL而不是WHERE

# 3、多表查询 #

*  用单条select语句从多个表中检索出数据

### ①自联结\[关联子查询\] ###

> \#已经某产品\[`prod_id`为DTNTR\]出现问题，想知道供应这种产品的供应商供应的其他产品是否也有问题：  
> SELECT `prod_name` FROM `products` WHERE `vend_id` = (  
> SELECT `vend_id` FROM `products` WHERE `prod_id` = ‘DTNTR’); \#方法1：子查询  
> SELECT b.`prod_name` FROM `products` AS a, `products` AS b  
> WHERE a.`prod_id` = ‘DTNTR’ AND a.`vend_id` = b.`vend_id`; \#方法2：自联结

> SELECT o.`order_num`, o.`order_date` FROM `customers` AS c, `orders` AS o, `orderitems` AS oi  
> WHERE c.`cust_id` = o.`cust_id` AND oi.`order_num` = oi.`order_num` AND `prod_id` = ‘FB’;

### ②内联结 ###

#### 联结一个表 ####

#等值联结，也叫做内联结
    SELECT 
        e.emp_no,
        CONCAT(last_name, ' ', first_name) AS emp_name,
        title
    FROM
        employees AS e,
        titles AS t
    WHERE
        e.emp_no = t.emp_no
    LIMIT 5;

SELECT 
        e.emp_no,
        CONCAT(last_name, ' ', first_name) AS emp_name,
        title
    FROM
        employees AS e
            INNER JOIN
        titles AS t ON e.emp_no = t.emp_no
    LIMIT 5;
    
    -- 两种语句在效率上其实是一样的，只是语法上的区别,
    -- INNER可以省略

应该保证所有联结都有正确的where子句，否则会出现笛卡尔积现象。

*  笛卡尔乘积现象：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接。也就是表1有n行,表2有m行，结果=m\*n行

#### 联结多个表 ####

SELECT 
        gender, salary
    FROM
        employees,
        salaries,
        titles
    WHERE
        employees.emp_no = salaries.emp_no
            AND salaries.emp_no = titles.emp_no
            AND title = 'Senior Engineer';

注意：不要联结不必要的表，因为联结表越多，性能下降越厉害

总结：

SELECT column_name(s)
    	FROM table1
    	INNER JOIN table2
    	ON table1.column_name=table2.column_name;
    	或者
    	SELECT column_name(s)
    	FROM table1
    	JOIN table2
    	ON table1.column_name=table2.column_name;
    	注：inner可以省略

![在这里插入图片描述][20181125141324415.png]  
拿表1去匹配表2

### ③外联结 ###

>#检索下了订单的客户和相关订单
    SELECT `cust_name`, `order_num` FROM `customers` AS c INNER JOIN `orders` AS o ON c.`cust_id` = o.`cust_id`;
    > ***
    >#检索所有客户的订单，包括没有下订单的客户[左外联结&右外联结]
    SELECT `cust_name`, `order_num` 
    FROM `customers` AS c 
    LEFT OUTER JOIN `orders` AS o 
    ON c.`cust_id` = o.`cust_id`;
    >***
    >SELECT `cust_name`, `order_num` 
    >FROM `orders` AS o 
    >RIGHT OUTER JOIN `customers` AS c 
    >ON c.`cust_id` = o.`cust_id`;

总结：

#### 左外连接 ####

@应用场景：用于查询一个表中有另一个表中没有的记录  
@特点：外连接的查询结果为主表中的所有记录，  
如果从表中有和它的匹配的，则显示匹配的值  
如果从表中没有和它匹配的，则显示null

SELECT column_name(s)
    		FROM table1
    		LEFT JOIN table2
    		ON table1.column_name=table2.column_name;
    		或：
    		SELECT column_name(s)
    		FROM table1
    		LEFT OUTER JOIN table2
    		ON table1.column_name=table2.column_name;
    		注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

![在这里插入图片描述][20181115172119138.png]  
注释：LEFT JOIN 关键字从左表返回所有的行，即使右表中没有匹配。没有匹配的用null填充。

#### 右外连接 ####

SELECT column_name(s)
    		FROM table1
    		RIGHT JOIN table2
    		ON table1.column_name=table2.column_name;
    		或：
    		SELECT column_name(s)
    		FROM table1
    		RIGHT OUTER JOIN table2
    		ON table1.column_name=table2.column_name;	
    		注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

![在这里插入图片描述][2018111517242591.png]  
注释：RIGHT JOIN 关键字从右表返回所有的行，即使左表中没有匹配。没有匹配的用null填充。

#### 全外连接 ####

SELECT column_name(s)
    		FROM table1
    		FULL OUTER JOIN table2
    		ON table1.column_name=table2.column_name;

![在这里插入图片描述][2018111517273389.png]

注：

SELECT column_name(s)
    	FROM table1
    	LEFT JOIN table2
    	ON table1.key = table2.key
    	WHERE table2.key IS NULL;

![在这里插入图片描述][20181125142451954.png]

SELECT column_name(s)
    	FROM table1
    	FULL JOIN table2
    	ON table1.key = table2.key
    	WHERE table1.key IS NULL OR table2.key IS NULL;

![在这里插入图片描述][20181125142625302.png]

### ④带聚集函数的联结 ###

>#检索所有客户以及每个客户下的订单数
       SELECT COUNT(o.`order_num`), c.`cust_name` 
    	FROM `customers` AS c 
    	LEFT OUTER JOIN `orders` AS o
    	 ON c.`cust_id` = o.`cust_id` 
    	WHERE c.`cust_id` IS NOT NULL 
    	GROUP BY c.`cust_id`;

总结：

*  语法：  
    select 查询列表  
    from 表1 别名 【连接查询】  
    join 表2 别名  
    on 连接条件  
    【where 筛选条件】  
    【groud by 分组】  
    【having by 排序列表】
 *  join含义：sql join子句用于把来自两个或者多个表的行结合起来，基于这些表之间的共同字段。
 *  分类：  
    内连接： inner  
    外连接：  
    左外：left【outer】  
    右外：right【outer】  
    全外：full 【outer】  
    交叉连接：cross【就是一个笛卡尔乘积】
 *  连接的结果可以再逻辑上看作是由select语句指定的列组成的新表  
    左连接和右连接的左右指定是以两张表中的哪一张为基准，他们都是外连接.  
    外连接就好像为非基准添加了一行全为空值的万能行，用来与基准表中找不到匹配的行进行匹配。假设两个没有空值的表进行左连接，左边是基准表，左边的所有行都出现在结果中，右表则可能因为无法与基准表匹配而出现是空值的字段。)
 *  总结分类：  
    按照年代分类：
    
        sql92标准【仅仅支持内连接,也就是等值,非等值,自连接】
         sql99标准【推荐】
    
    按照功能分类：
    
        内连接：
         	等值连接
         	非等值连接
         	自连接
         外连接：
         	左外连接
         	右外连接
         	全外连接
         交叉连接：
 *  注意：
    
        SELECT girl.`name`, boy.`name` FROM girl, boy;
        
         SELECT `name`, `name` FROM girl, boy;
         ERROR 1052 (23000): Column 'name' in field list is ambiguous
         原因：列'ID'在字段列表中重复，其实就是两张表有相同的字段，但是使用时表字段的名称前没有加表名，导致指代不明
         解决：前面加上前缀就没问题了。

[20181125141324415.png]: /images/20220328/febf23aa1f854fc895266b523007f4ad.png
[20181115172119138.png]: /images/20220328/dcda63e3461b4f398b777158c41099f5.png
[2018111517242591.png]: /images/20220328/55f8291dfc3e428c861af6890bcb98a0.png
[2018111517273389.png]: /images/20220328/caf44939743d41e18b9981142ffbd434.png
[20181125142451954.png]: /images/20220328/a525420c8cd74bea884073406591f98d.png
[20181125142625302.png]: /images/20220328/0ec621ff0f82497dac1fa3f30f51f17c.png