子查询 in exist

什么是子查询

1.子查询是嵌套在其他查询中的常规的T-SQL查询（用括号括起来），当需要一个SELECT语句作为数据部分的基础或另一个查询中的条件的要素时，就是创建子查询。 
  
2.子查询满足的需求： 
  （1）.把一个查询分解成一系列的逻辑步骤。 
  （2）.提供一个列表作为WHERE子句和 [ IN / EXISTS / ANY / ALL] 的目标对象。 
  （3）.提供由父查询中每一条记录驱动的查找。 

一.嵌套的子查询 
1.嵌套子查询只朝一个方向进行——要么返回一个而用于外部查询的值，要么返回一个与IN运算符一起使用的的值的列表。 
2.嵌套子查询中， 内部查询只处理一次。 
3.与NULL比较总会得到NULL。如果允许NULL在子查询中出现，则在与NOT IN相比较时，外部查询中的所有行都会被确定为错误的——这将返回空的列表。 

二.关联（相关）子查询 
1.执行步骤： 
  （1） 外部查询得到一条记录并将其传入到内部查询。 
  （2）内部查询基于传入的值执行。 
  （3）内部查询从其结果中把值传回到外部查询，外部查询使用这些值来完成其处理。

 

注：

    ①在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。

②在关联子查询中是信息流是双向的。

③外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录，外部查询根据返回的记录做出决策。

 

 

例：

         SELECT   title

         FROM   Movies   Old

         WHERE year < ANY

                 ( SELECT   year

                  FROM   Movies

                  WHERE   title =   Old.title

                  );

 

 

SQL 中 EXISTS 的用法

比如在Northwind数据库中

有一个查询为

SELECT   c. CustomerID, CompanyName

FROM    Customers c

WHERE   EXISTS (

                 SELECT OrderID

                 FROM   Orders   0

                 WHERE 0.CustomerID = c.CustomerID )  ；

 

这里面的EXISTS是如何运作呢？子查询返回的是OrderID字段，可是外面的查询要找的是CustomerID和CompanyName字段，这两个字段肯定不在OrderID里面啊，这是如何匹配的呢？EXISTS用于检查子查询是否至少会返回一行数据?

 

①该子查询实际上并不返回任何数据，而是返回值True或False

→他所带的子查询一般直接用 ‘SELECT *’ 因为给出列名也没多少意义。

②EXISTS指定一个子查询，检测行的存在。 （详见D ①）

③对于 EXISTS subquery 中的子查询subquery

是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。

④结果类型Boolean 。如果子查询包含行，则返回 TRUE。

 

A、其实 :EXISTS属于相关子查询，也就是说子查询的条件依赖于外层父查询的查个属性值。比如：
SELECT  Sname
FROM     Student
WHERE     EXISTS   (
                    SELECT  *
                   FROM C            /*SC 为课程表， Student 为学生表 */
                   WHERE Sno=Student.Sno AND Cno ='1');
依赖也就是这一句“ Sno=Student.Sno”。 这个查询过程不是一般的自下而上执行，他与外查询依赖，执行的时候是先从父查询中取一个元组（该元组是全属性的），然后根据条件 Sno=Student.Sno 处理内查询，得到结果。（详见 D ①）然后进行父查询中取第二个元组，如此反复。

EXISTS 的子查询，有一些是不能被其它形式的子查询等价替换的，但是所有 IN、比较运算符、ANY 和ALL 的子查询都能用带有EXISTS 的子查询等价替换。但是在做的时候，请考虑效率问题，哪种高效用啊种，这是优化问题。
EXISTS只要知道内查询是否为空值就可以了，所以效率要比用IN的查询要高效一些。

 

 

 

 

 

B、在子查询中使用  NULL 仍然返回结果集:

这个例子在子查询中指定 NULL，并返回结果集，通过使用 EXISTS 仍取值为 TRUE。

 

SELECT CategoryName

FROM    Categories

WHERE EXISTS (SELECT NULL) ；

 

C、比较使用  EXISTS 和 IN 的查询:

这个例子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息。

例 1 ：

用 EXISTS :

SELECT    DISTINCT   pub_name

FROM      publishers

WHERE EXISTS(

                SELECT *

                FROM titles

                WHERE pub_id = publishers.pub_id

                         AND type = \'business\')；

 

用 IN :

SELECT    DISTINCT   pub_name

FROM      publishers

WHERE     pub_id IN (

                      SELECT pub_id

                      FROM    titles

                      WHERE type = \'business\');

 

任一查询的结果集：

pub_name

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

Algodata Infosystems

New Moon Books

 

(2 row(s) affected)

 

例 2 ：  本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名：

用 EXISTS :

SELECT    title

FROM     titles

WHERE EXISTS (

                  SELECT *

                  FROM publishers

                  WHERE pub_id = titles.pub_id

                           AND city LIKE \'B%\')；

 

用 IN :

SELECT    title

FROM     titles

WHERE    pub_id IN (

                     SELECT pub_id

                     FROM   publishers

                     WHERE   city LIKE \'B%\')；

 

任一查询的结果集：

title                                                                            

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

The Busy Executive\'s Database Guide                                              

Cooking with Computers: Surreptitious Balance Sheets                             

You Can Combat Computer Stress!                                                  

Straight Talk About Computers                                                    

But Is It User Friendly?                                                         

Secrets of Silicon Valley                                                        

Net Etiquette                                                                    

Is Anger the Enemy?                                                              

Life Without Fear                                                                

Prolonged Data Deprivation: Four Case Studies                                    

Emotional Security: A New Algorithm                                              

 

(11 row(s) affected)

 

D、进一步细究 EXISTS和IN:

若要判断某班是否存在一个名为"小明"的学生

用 IN :

" 小明" IN (

SELECT SNAME

FROM   STUDENT);

 

其中 (select sname from student)

返回的是一个全班姓名的集合 ,in用于判断"小明"是否为此集合中的一个数据;

 

 

 

 

用 EXISTS :

EXISTS

(SELECT *

FROM   STUDENT

WHERE SNAME=" 小明");

 

这两个函数是差不多的 , 但是由于优化方案的不同, 通常NOT EXISTS要比NOT IN 要快。因为NOT EXISTS可以使用结合算法而NOT IN　就不行了，而EXISTS则不如IN快 , 因为这时候IN可能更多的使用结合算法。

 

SELECT *

FROM  表A

WHERE EXISTS(

SELECT *

FROM  表B

WHERE  表B.id =  表A.id );

 

相当于

 

SELECT *

FROM  表A

WHERE id IN (

SELECT id

FROM    表B );

 

①对于表 A的每一条数据 ,都执行 select * from  表B where  表B.id= 表A.id的存在性判断 ,如果表 B中存在与表 A当前行相同的id ,即 exists为真 ,则该行显示；否则不显示

② exits适合内小外大的查询，in适合内大外小的查询

③ IN 确定给定的值是否与子查询或列表中的值相匹配。

④ EXISTS 指定一个子查询，检测行的存在。