--expr<>all 相当于 not in(); [TEST@orcl] SQL>with t1 as( 2 select 1 a from dual union all 3 select 2 a from dual union all 4 select 3 a from dual union all 5 select 4 a from dual) 6 select * from t1 where a <> all(1,2) 7 ; A ---------- 3 4 --还有一个 expr=any 相当于 expr in() [TEST@orcl] SQL>with t1 as( 2 select 1 a from dual union all 3 select 2 a from dual union all 4 select 3 a from dual union all 5 select 4 a from dual) 6 select * from t1 where a = any(1,2) 7 ; A ---------- 1 2 --还有下面的,自己体会下 [TEST@orcl] SQL>with t1 as( 2 select 1 a from dual union all 3 select 2 a from dual union all 4 select 3 a from dual union all 5 select 4 a from dual) 6 select * from t1 where a > any(1,2) 7 ; A ---------- 2 3 4[TEST@orcl] SQL>with t1 as( 2 select 1 a from dual union all 3 select 2 a from dual union all 4 select 3 a from dual union all 5 select 4 a from dual) 6 select * from t1 where a > all(1,2) 7 ; A ---------- 3 4
--expr<>all 相当于 not in();
[TEST@orcl] SQL>with t1 as(
2 select 1 a from dual union all
3 select 2 a from dual union all
4 select 3 a from dual union all
5 select 4 a from dual)
6 select * from t1 where a <> all(1,2)
7 ; A
----------
3
4
--还有一个 expr=any 相当于 expr in()
[TEST@orcl] SQL>with t1 as(
2 select 1 a from dual union all
3 select 2 a from dual union all
4 select 3 a from dual union all
5 select 4 a from dual)
6 select * from t1 where a = any(1,2)
7 ; A
----------
1
2
--还有下面的,自己体会下
[TEST@orcl] SQL>with t1 as(
2 select 1 a from dual union all
3 select 2 a from dual union all
4 select 3 a from dual union all
5 select 4 a from dual)
6 select * from t1 where a > any(1,2)
7 ; A
----------
2
3
4[TEST@orcl] SQL>with t1 as(
2 select 1 a from dual union all
3 select 2 a from dual union all
4 select 3 a from dual union all
5 select 4 a from dual)
6 select * from t1 where a > all(1,2)
7 ; A
----------
3
4