SQL学习(二)：子查询，谓词

            IN谓词的通用表达式：expr in (Subquery)｜expr in (val {,val...})

           例： 检索由住在Duluth的顾客和住在New York的代理商组成的所有订货记录的ordno值

                 select ordno from orders

                           where (cid,aid) in

                            (select cid,aid from customers c,agents a where c.city='Duluth' and a.city='New York');

     NOT IN谓词为当且仅当求解出的expr值不在子查询返回的集合中[expr NOT IN (Subquery)]

2. 量化比较谓词

       通用形式 expr θ{SOME｜ANY｜ALL} (Subquery) where θ is some operator in the set {<,<=,=,<>,>,>=}

该形式中的SOME与ANY含义相同，θ包含：{<,<=,=,<>,>,>=}  <>是不等于

         例：找出佣金百分率最小的代理商的aid值

          select aid from agents where percent <=all (select percent from agents);

注：谓词=SOME与谓词IN具有完全相同的效果

      谓词<>all与NOT IN等价

      谓词<>SOME不与 NOT IN等价

3. 谓词EXISTS

       通用形式 [NOT] EXISTS (Subquery)  注：EXISTS (Subquery) 为真当且仅当子查询返回一个非空集合

          例：求出订购了产品P01又订购了产品P07的顾客的cid值

             select distinct cid from orders x

                       where pid='p01' and exists

                        (select * from orders where cid=x.cid and pid='p07');

       NOT EXISTS 查询效果可用 NOT IN和等价谓词<>ALL替代

4. SQL查询会有很多等价形式

      例：检索订购了产品p01的顾客所在的city名这一请求的表达式，有四种主要的Select语句表达式

       select distinct city from customers where cid in

               (select cid from orders where pid='p01');

       select distinct city from customers where cid =any

               (select cid from orders where pid='p01');

       select distinct city from customers c where exists

                 (select * from oredrs o where c.cid=o.cid and o.pid='p01');

       select  distinct city from customers c, orders o

                 where c.cid=o.cid and o.pid='p01';