Oracle中的数据过滤方法（二）-子查询

客官°小女子只卖身不卖艺 2024-03-22 10:14 37阅读 0赞

> 子查询过滤：通过在SELECT语句中嵌入子查询来筛选出需要的数据。子查询可以在FROM子句、SELECT子句和WHERE子句中使用，可以使用比较运算符、逻辑运算符和通配符等来过滤数据。子查询可以嵌套多层，但要注意性能问题。

在这里继续讲解第二种数据过滤的办法！！！

**目录**

一、子查询分类：

1.相关子查询

2.非相关子查询

2.1单行单列子查询

2.2单列多行子查询

2.3--多列子查询

2、子查询的用法包括：

1. 在WHERE子句中使用子查询作为条件

2. 在FROM子句中使用子查询作为表格

3. 在SELECT子句中使用子查询作为计算字段

3、常用的子查询包括：

--------------------

子查询是指在一个查询语句中嵌套另一个查询语句，子查询可以作为主查询语句的条件、过滤条件、聚合条件等。子查询可以返回单个值、多个值、表格等结果。简单理解为在一个sql语句中再嵌套多个sql。（即查询里套着查询）

## 一、子查询分类： ##

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">类型</td> 
   <td></td> 
   <td></td> 
   <td style="text-align:center;">返回情况</td> 
  </tr> 
  <tr> 
   <td>相关子查询</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td colspan="1">非相关子查询</td> 
   <td><span style="background-color:#fbd4d0;">单行单列</span></td> 
   <td></td> 
   <td>一个值</td> 
  </tr> 
  <tr> 
   <td><span style="background-color:#fbd4d0;">单列多行</span></td> 
   <td></td> 
   <td>一个列</td> 
  </tr> 
  <tr> 
   <td colspan="1"><span style="background-color:#fbd4d0;">多列</span></td> 
   <td>多列单行子查询</td> 
   <td>一个表</td> 
  </tr> 
  <tr> 
   <td>多列多行子查询</td> 
   <td>一张表</td> 
  </tr> 
 </tbody> 
</table>

首先对相关子查询和非相关子查询有个定义的理解！！！

Ⅰ、子查询

子查询是在主查询中嵌入的查询语句，用于提供更详细的信息或限制结果集。子查询可以是相关的或非相关的。

Ⅱ、非相关子查询

非相关子查询是独立于主查询的子查询，它不依赖于主查询的结果。非相关子查询在主查询之前执行一次，并返回一个结果集，然后主查询使用这个结果集进行进一步的操作。因为非相关子查询独立于主查询，所以它的执行速度通常比相关子查询快。

Ⅲ、相关子查询

相关子查询是依赖于主查询的子查询，它的结果集是根据主查询的每一行而变化的。相关子查询在主查询每一行被处理时都会执行一次。因为相关子查询需要根据主查询的每一行而变化，所以它的执行速度通常比非相关子查询慢。因此，非相关子查询更适合用于需要返回相对较少结果的情况，而相关子查询更适合用于需要返回相对较多结果的情况。

接下来描述下子查询的几种分类使用：

### 1.相关子查询 ###

其实相关子查询就是指子查询中包含了对主查询表格的引用，子查询的结果会根据主查询的每一行而产生不同的结果。相关子查询通常用于需要根据主查询结果进行条件过滤或计算的情况。相关子查询的语法格式与普通子查询相同，只是在子查询中需要引用主查询的列或表格。

比如：

SELECT column1, column2
    FROM table1
    WHERE column1 IN( 
                     SELECT column3 
                     FROM table2 
                     WHERE table2.column4 = table1.column5);

分析：

在上面的查询语句中，子查询中引用了主查询表格table1的列column5，子查询的结果会根据主查询的每一行而产生不同的结果。如果主查询表格table1中的某一行的column5值与子查询中的column4值匹配，那么该行就会被选中。

相关子查询可以用于复杂的数据分析和查询，但是由于需要对主查询表格进行多次查询和计算，所以效率较低，应该尽量避免过度使用。

**同时还有另外一种相关子查询：**

放在SELECT后面的SELECT语句通常被称为内部查询或内嵌查询，也可以被称为相关子查询，因为它们可以引用主查询的列或表格。内部查询通常用于返回计算字段或作为过滤条件，以便在主查询中进行进一步的计算或过滤。

比如：

--查询语句使用内部查询来计算每个部门的平均工资：
    SELECT department_name, 
           (SELECT AVG(salary) 
            FROM employees e 
            WHERE e.department_id = d.department_id) AS avg_salary
    FROM departments d;

在这个查询语句中，内部查询 \`(SELECT AVG(salary) FROM employees e WHERE e.department\_id = d.department\_id)\` 引用了主查询表格 \`departments\` 的 \`department\_id\` 列，并且根据每个部门的 \`department\_id\` 值计算了平均工资。内部查询的结果作为计算字段\`avg\_salary\` 返回给主查询。因此，放在SELECT后面的SELECT语句可以是相关子查询，也可以是其他类型的内部查询。

> **注意：**
> 
> 相关子查询放在select后面时，
> 
> 这里只执行子查询会报错,必须和主查询一起执行，
> 
> 一般会将标量子查询改写成表连接。
> 
> 相关子查询又称为性能杀手，它的执行速度会很慢！

### 2.非相关子查询 ###

结合定义的理解，要清楚相关子查询和非相关子查询的区别：

Ⅰ.非相关子查询的子查询的结果供主查询使用,子查询先于主查询执行,子查询可以单独执行

Ⅱ.相关子查询子查询和主查询相互依赖，子查询不可以单独执行，主查询先于子查询执行

比如：

select *
    from emp
    where ename=(
                 select ename 
                 from emp 
                 where ename='KING');
    --这里的子查询是可以单独执行的哦！

由于非相关子查询是很容易理解的，所以下面就直接放实例说明啦！！！

#### 2.1单行单列子查询 ####

--查询SMITH同部门的员工信息
    select *
    from emp
    where deptno=(select deptno
                 from emp
                 where ename='SMITH');
    
    
    --查询和SCOTT工资一样的员工信息
    select *
    from emp
    where sal=(select sal 
               from emp 
               where ename='SCOTT') 
    and ename != 'SCOTT';
    
    
    --查询和7788号员工名字首字母相同的员工信息
    select *
    from emp
    where substr(ename,1,1)=(select substr(ename,1,1) 
                             from emp 
                             where empno=7788) 
    and empno!=7788;
    
    
    --查询工资大于全表平均工资的员工信息
    select *
    from emp
    where sal>(select avg(sal) 
               from emp)

#### 2.2单列多行子查询 ####

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td colspan="2" style="text-align:center;">常用的运算符</td> 
  </tr> 
  <tr> 
   <td>单行比较运算符</td> 
   <td>&gt; &lt; &gt;= &lt;= = &lt;&gt; !=</td> 
  </tr> 
  <tr> 
   <td>多行比较运算符</td> 
   <td>any all in exists</td> 
  </tr> 
  <tr> 
   <td colspan="2"><span style="color:#fe2c24;">注：any 和 all 必须和单行比较运算符配合使用</span></td> 
  </tr> 
  <tr> 
   <td>&gt;any</td> 
   <td>大于最小的</td> 
  </tr> 
  <tr> 
   <td>&lt;any</td> 
   <td>小于最大的</td> 
  </tr> 
  <tr> 
   <td>=any</td> 
   <td>相当于in</td> 
  </tr> 
  <tr> 
   <td>&gt;all</td> 
   <td>大于最大的</td> 
  </tr> 
  <tr> 
   <td>&lt;all</td> 
   <td>小于最小的</td> 
  </tr> 
  <tr> 
   <td>=all</td> 
   <td>空（几乎不用）</td> 
  </tr> 
 </tbody> 
</table>

> \--提到任意一个或者某一个就用any
> 
> \--提到所有的全部的就用all

比如：

--查询比20部门所有人的工资高的员工信息
    select *
    from emp
    where sal>all(select sal 
                  from emp
                  where deptno=20);
    
    
    --查询和工资是3000的员工同部门的员工信息
    select *
    from emp
    where deptno in (select deptno 
                     from emp 
                     where sal=3000);
    
    
    --查询比JONES所在部门的任意一个人的工资低的员工信息
    select *
    from emp
    where sal <any (select sal 
                    from emp 
                    where deptno=(select deptno 
                                 from emp 
                                 where ename='JONES'));
    
    
    --查询比7654号员工所在部门的所有人工资低的员工信息
    select *
    from emp
    where sal<all(select sal 
                  from emp 
                  where deptno=(select deptno
                                from emp
                                where empno=7654));
    
    
    --查询和任意一个名字包含O的员工同部门的员工信息
    select *
    from emp
    where deptno in (select deptno
                     from emp
                     where ename like '%O%');
    
    
    --查询和KING的下属同工作的员工信息
    select *
    from emp
    where  job in (select job
                  from emp
                  where mgr=(select empno
                             from emp
                             where ename='KING'));
    
    
    --查询10部门员工中工资大于3000的员工信息
    select *
    from (select * 
          from emp 
          where deptno=10)
    where sal > 3000;

#### 2.3--多列子查询 ####

经常被当做临时表来用!!!

Ⅰ、多列单行子查询：

比如：

假设有一个订单表(order\_table)，包含了订单号(order\_id)、订单日期(order\_date)和订单总金额(total\_amount)三列，现在要查询最新的订单号、日期和总金额，可以使用多列单行子查询实现：

SELECT order_id, order_date, total_amount
    FROM order_table
    WHERE (order_date, order_id) IN(SELECT MAX(order_date), order_id
                                    FROM order_table);

Ⅱ、多列多行子查询：

假设有两个表，一个是订单表(order\_table)，另一个是订单明细表(order\_detail\_table)，订单表包含了订单号(order\_id)、订单日期(order\_date)和订单总金额(total\_amount)三列，订单明细表包含了订单号(order\_id)、商品名称(product\_name)和商品数量(quantity)三列，现在要查询每个订单的商品名称和数量，可以使用多列多行子查询实现：

SELECT order_id, product_name, quantity
    FROM order_detail_table
    WHERE order_id IN(SELECT order_id
                      FROM order_table
                      WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31');

分析：这个查询会返回在2021年1月1日到2021年12月31日之间的所有订单的商品名称和数量。

Ⅲ、exists的多列子查询：

EXISTS是一个用于子查询的逻辑运算符，在Oracle数据库中，它可以用于判断一个子查询返回的结果集是否为空。EXISTS返回TRUE或FALSE，TRUE表示子查询返回的结果集不为空，FALSE表示子查询返回的结果集为空，即判断是否存在满足条件的记录。

EXISTS的语法如下：

SELECT column1, column2, ...
    FROM table_name
    WHERE EXISTS (subquery);

其中，subquery是一个子查询，它返回一个结果集。

下面是一个使用EXISTS的示例：

假设有两个表，一个是订单表(order\_table)，另一个是订单明细表(order\_detail\_table)，订单表包含了订单号(order\_id)、订单日期(order\_date)和订单总金额(total\_amount)三列，订单明细表包含了订单号(order\_id)、商品名称(product\_name)和商品数量(quantity)三列，现在要查询有哪些订单包含了商品名称为'iPhone'的订单明细：

SELECT order_id
    FROM order_table
    WHERE EXISTS (SELECT order_id
                  FROM order_detail_table
                  WHERE order_table.order_id = order_detail_table.order_id
                  AND product_name = 'iPhone');

这个查询会返回包含了商品名称为'iPhone'的订单明细的所有订单号。在这个查询中，子查询会返回包含了商品名称为'iPhone'的订单明细的订单号，而主查询使用EXISTS判断子查询返回的结果集是否为空，如果不为空，则返回相应的订单号。

再比如我们已知员工表emp和部门表dept，查询部门所在地在NEW YORK 的员工信息：

select *
    from dept
    where loc='NEW YORK'
    
    
    select *
    from emp e
    where exists(select 1 
                 from dept d
                 where loc='NEW YORK' 
                 and e.deptno=d.deptno) --属于相关子查询

> 注意：
> 
> 当exists后括号里的查询条件成立时，就会输出其内容结果否则输出空；
> 
> exists引导非相关子查询时当子查询有返回值执行主查询；

接下来再熟练运用下in 和 exists

已知员工表emp，查询经理是SCOTT或者KING的员工信息：

--用in
    select *
    from emp
    where mgr in (select empno 
                  from emp
                  where ename in('SCOTT','KING'));
    
    
    --用exists
    select *
    from emp e
    where EXISTS (select 1 
                  from emp d
                  where ename in('SCOTT','KING')
                  and e.mgr=d.empno);

## 2、子查询的用法包括： ##

### 1. 在WHERE子句中使用子查询作为条件 ###

例如：

SELECT * 
    FROM table1 
    WHERE column1 = 
                   (SELECT column2 
                    FROM table2 
                    WHERE condition);

### 2. 在FROM子句中使用子查询作为表格 ###

例如：

SELECT * 
    FROM (SELECT column1, column2 
          FROM table1 
          WHERE condition) t 
    WHERE t.column1 = value;

### 3. 在SELECT子句中使用子查询作为计算字段 ###

例如：

SELECT column1, 
           (SELECT COUNT(*) FROM table2 WHERE condition) AS count 
    FROM table1 
    WHERE condition;

## 3、常用的子查询包括： ##

1. 单行子查询：返回单个值

例如：

SELECT column1 
    FROM table1 
    WHERE column2 = (SELECT column3 
                     FROM table2 
                     WHERE condition);

2. 多行子查询：返回多个值

例如：

SELECT column1 
    FROM table1 
    WHERE column2 IN (SELECT column3 
                      FROM table2 
                      WHERE condition);

3. EXISTS子查询：判断是否存在满足条件的记录

例如：

SELECT column1 
    FROM table1 
    WHERE EXISTS (SELECT * 
                  FROM table2 
                  WHERE condition);

4. NOT EXISTS子查询：判断是否不存在满足条件的记录

例如：

SELECT column1 
    FROM table1 
    WHERE NOT EXISTS (SELECT * 
                      FROM table2 
                      WHERE condition);

5. 聚合子查询：返回聚合函数的结果

例如：

SELECT SUM(column1) 
    FROM table1 
    WHERE column2 = (SELECT column3 
                     FROM table2 
                     WHERE condition);

6. 带ANY或ALL的子查询：比较主查询和子查询的结果

例如：

SELECT column1 
    FROM table1 
    WHERE column2 > ANY (SELECT column3 
                         FROM table2 
                         WHERE condition);

这几种常用的子查询上面也有仔细的描述，慢慢理解和运用！！！

最后数据过滤的方法也整理完成！！！