SQL学习(二)
1. IN谓词
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';