ORACLE 自学中。
我看的教材里面没有<>all 和 <>any,
但是有些网站上的例子中有出现<>all。所以我想,有没有 <>any 这个比较运算符?
如果有的话,它们有什么区别,还有,跟 not exists 用法一样吗?请各位高手指点。~~
我看的教材里面没有<>all 和 <>any,
但是有些网站上的例子中有出现<>all。所以我想,有没有 <>any 这个比较运算符?
如果有的话,它们有什么区别,还有,跟 not exists 用法一样吗?请各位高手指点。~~
select a.col from a
where a.qty >= all(select qty from b where b.id = a.id)
All等价于N个And语句;
Any等价于N个or语句。当然会有<>All、<>Any、>All、。
not in差不多
SQL> select * from t1; ID
----------
1
2
3
SQL> select * from t1 where id not in(1,2); ID
----------
3SQL> select * from t1 where id <> all(1,2); ID
----------
3SQL> select * from t1 where id not exists(1,2);
select * from t1 where id not exists(1,2)
*
ERROR at line 1:
ORA-00920: invalid relational operator
不过ERROR at line 1:ORA-00920: invalid relational operator
这个错是不是因为语法错误啊? 改成
SELECT * FROM t1
WHERE NOT EXISTS (SELECT ID FROM t1 WHERE ID IN (1,2)) 应该就不会报这个错了。
FROM BUY1
WHERE BUY_ID1 <> ALL (2,3,4) →BUY_ID1在2,3,4以外的8件被检索出来。SELECT *
FROM BUY1
WHERE BUY_ID1 <> ANY (2,3,4) →全件检索。共11件。SELECT *
FROM BUY1
WHERE BUY_ID1 NOT IN (2,3,4) →和<>ALL结果一样,BUY_ID1在2,3,4以外的8件被检索出来。SELECT *
FROM BUY1
WHERE NOT EXISTS (SELECT BUY_ID1 FROM BUY1 WHERE BUY_ID1 IN (2,3,4)) →没有结果。
其它的都可以理解,就是<>any 不知道为什么会全件检索?各位要是知道请告诉我啊~~~
<>ALL 等价于 NOT IN
=ANY 等价于 INWITH tmp AS (SELECT '1' AS num FROM dual UNION ALL
SELECT '2' AS num FROM dual UNION ALL
SELECT '3' AS num FROM dual)
SELECT * FROM tmp
WHERE num = ANY('1','2');WITH tmp AS (SELECT '1' AS num FROM dual UNION ALL
SELECT '2' AS num FROM dual UNION ALL
SELECT '3' AS num FROM dual)
SELECT * FROM tmp
WHERE num <> ALL('1','2');
所以也同样被检索出来了
你好好想想,
select t.* from t2
3
4
5
select t.* from t t //先将所有记录查出来
WHERE t.A<>ANY(2,3)//接在用这句进行过滤,只要条件为真的记录可以留下,否刚淘汰.那么首先2进行比较,2只要不等于2或3中的任一个就可以留下.那2当然要留下了,接着是3....接着是4....接着....你就明白了.
WHERE BUY_ID1 <> ANY (……)这句跟没写一样是吧?