hive in查询 hive where in子查询

这里有三种查询的结构：单行单列、单行多列、多行单列

1.子查询返回单行单列数据

例如：查询出基本工资比ALLEN低的全部雇员信息。

步骤一：找出ALLEN的工资

步骤二：此时的查询返回的是单行单列的数据，这样子查询往往出现在where子句

      或having子句中，根据题目的要求在where子句中使用此查询。

2.子查返回单行多列数据

此时需要使用括号声明这个标量值列表，例如：('ALLEN',3000)

例如：查询与SCOTT从事同一个工作且工资相同的雇员信息。(与同个事物的两个属性有关)

步骤一：查询出SCOTT的工作及工资。

select job,sal
from emp
where ename='SCOTT';

步骤二：此时的查询结果返回的是单行多列数据，所以子查询只能够在WHERE子句中出现，   

      下面判断多个列的内容。

select *
from emp
where (job,sal)=(  
                                  select  job,sal
                                       from emp
                                       where ename='SCOTT';
                               );

步骤三：增加一个条件，消除数据查询中的SCOTT雇员数据。

select *
from emp
where (job,sal)=(  
                                  select  job,sal
                                       from emp
                                       where ename='SCOTT';
                               )   and   ename<>' SCOTT ';

3.子查询返回多行单列数据

    多行子查询一般表示的是一个数据的范围，指的是提供一个数据筛选的范围，这一点与之前讲解的IN操作符有些类似，唯一不同的是，IN操作用户是通过“()”指定数据的查询范围，而子询之中是通过返回我多行数据作为数据查询的范围。

    在使用多行子查询时，主要使用3种操作符，分别是IN、ANY、ALL操作符。

3.1 IN操作符

例如：查询出与每个部门中最低工资相同的全部雇员信息。

步骤一：查询每个部门的最低工资。

select min(sal)
     from emp
     group by deptno;

步骤二：此时的子查询返回的是多行单列的数据，所以将子查询作为限定条件，并在WHERE   

     子句中使用IN操作符。

select *
from emp
where sal in(
                select min(sal)
                from emp
                group by deptno
           );

  通过前面的学习可以知道，IN是指定范围查询，如果在前面加一个NOT，则表示不在查询范围中。所以我们可以查出不与每个部门中最低工资相同的全部雇员信息。 

  但必须注意的是，如果在子查询中有一个内容返回了null，则将不会有任何的查询结果。

 

3.2 ANY操作符

ANY操作符也是用于多行单列查询中，表示与子查询中返回的每个结果进行比较，ANY在使用中有如下3种使用形式。
=ANY: 表示与子查询中的每个元素进行比较，功能与IN类似(然而<>ANY不等价于NOT  IN).
       使用<>ANY将把表中的所有数据都返回来，没有任何意思。
>ANY：比子查询中返回的最小结果要大(还包括>=ANY  包含最小值)。
<ANY：比子查询中返回的最大结果要小(还包括<=ANY)。

提示：在子查询的操作符中，也存在一个SOME操作符，此操作符的作用与ANY操作符类似（ANY是早期SQL版本中使用的，后来的版本为了避免与英语中的ANY在语言上的混淆，所以添加一个SOME）;

3.3  ALL操作符

<>ALL：等价于NOT  IN(但是=ALL并等价于IN)。
>ALL：比子查询中最大值还要大(还包含>=ALL)。（大于任何一个）
<ALL：比子查询中紧小值还要小(还包含<=ALL)。（小于任何一个）
注意：=ALL并不表示与IN操作类似，这个操作将不会不任何数据返回。（与not in中有null   一样，也不返回任何数据）