Keyword ANY, SOME, and ALL in SQL

Source: Internet
Author: User
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

  1. The relationship between all and any is the relationship between and or.
  2. 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.
  3. Equivalent to = ANY, IN indicates that the variable is IN the (subquery) list, that is, a IN (table B) indicates a = any B. B.
  4. 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.
  5. 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.
  6. 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.