Any nested Query
Select emp. empno, emp. ename, emp. sal from scott. emp where sal> any (select sal from scott. emp where job = 'manager ');
Equivalent
Select sal from scott. emp where job = 'manager' result: sal: 2975 2850 2450
Select emp. empno, emp. ename, emp. sal from scott. emp where sal> 2975 or sal> 2850 or sal> 2450
Nested query with some
Select emp. empno, emp. ename, emp. sal from scott. emp where sal = some (select sal from scott. emp where job = 'manager ');
Equivalent
Select sal from scott. emp where job = 'manager' result: sal: 2975 2850 2450
Select emp. empno, emp. ename, emp. sal from scott. emp where sal = 2975 or sal = 2850 or sal = 2450
Note: any and some are equivalent.
Nested query with all
Select emp. empno, emp. ename, emp. sal from scott. emp where sal> all (select sal from scott. emp where job = 'manager ');
Equivalent
Select sal from scott. emp where job = 'manager' result: sal: 2975 2850 2450
Select emp. empno, emp. ename, emp. sal from scott. emp where sal> 2975 and sal> 2850 and sal> 2450
- The relationship between all and any is the relationship between and or.
- ANY is equivalent to SOME. It is said that these two different words are used to accommodate the English syntax. For example, in the (English) syntax where = ANY is used, it should be = SOME.
- Equivalent to = ANY, IN indicates that the variable is IN the (subquery) list, that is, a IN (table B) indicates a = any B. B.
- Not in is equivalent to <> ALL, but NOT equal to <> ANY. Both indicate that the variable is not in the (subquery) list, that is, a not in (table B) indicates a <> all B. b. If a <> any B. B, all B <> a is true.
- The performance difference between IN and EXISTS mainly comes from that IN will compile each row of records IN the column subquery and then return, while EXISTS will return immediately if the first record meeting the condition is met.
- Not in and not exists cannot be fully equivalent. not IN is used only when the field after the select keyword IN the subquery has the NOT null constraint or such suggestion.