25

According to MSDN BOL (Books Online) description on SOME | ANY (Transact-SQL),

SOME and ANY are equivalent.

It does make sense to use either SOME | ANY to make a query more readable.

But is that the only reason why there are 2 keywords in TSQL where they serve the exactly the same purpose?

Are there any historic reasons why they have the same functionality?

M. Tibbits
  • 8,400
  • 8
  • 44
  • 59
dance2die
  • 35,807
  • 39
  • 131
  • 194

5 Answers5

20

From the ANSI-92 SQL Standard (search for "SOME"). Also here, text

<some> ::= SOME | ANY

I suspect the reason is that SQL language comes from the early 1970s, but had no standard until 1986. The standard would have taken elements of the existing SQL dialects, so we have this SOME/ANY anomaly.

This blog article by Brad Schulz explains some differences: "ALL, ANY, and SOME: The Three Stooges"

doubleDown
  • 8,048
  • 1
  • 32
  • 48
gbn
  • 422,506
  • 82
  • 585
  • 676
8

SOME and ANY are equivalent. ANY is ANSI syntax. Why SOME is introduced, I do not know. Could be because of readability, but both of next two sentences are easy to understand.

WHERE 5000 < ANY(SELECT Price FROM dbo.items)
WHERE 5000 < SOME(SELECT Price FROM dbo.items)

Although, SQL server will in both cases execute:

WHERE EXISTS(SELECT * FROM dbo.items WHERE price>5000)

which is also very easy to understand.

Joel
  • 19,175
  • 2
  • 63
  • 83
Niikola
  • 1,452
  • 1
  • 10
  • 12
  • Hi, Niikola: "Why SOME is introduced, I do not know". and that is the kind of point of this question. – dance2die Sep 05 '09 at 19:30
  • I tried to find the reason for these operator to exist. As by your example I find the below query more readable. – regeter Oct 25 '14 at 01:38
5

"Are there any historic reasons why they have the same functionality?"

I'll answer the actual question... At the beginning it was just ALL and ANY.

ALL is an universal quantifier, while ANY was supposed to always be an existential quantifier. However, in English, ANY is frequently used as an universal quantifier as well. "I can beat ANY of you" is not synonym of "I can beat SOME of you". It's in fact synonym of "I can beat ALL of you".

With ANY being confusing, SOME has been introduced as a more reliable synonym for ANY with the adoption of the SQL-92 standard. ANY was supposed to be retained for a while just for backward compatibility with previous product versions. But we still have it today.

Cristian Scutaru
  • 1,375
  • 19
  • 23
  • 1
    Semantics, but I'd say that in your example "any" is still quite distinct from "all": "**any** of you" there would most likely mean "any [one] of you **(at a time)**", _not_ "all of you **(at once)**". Upvoted anyway because I agree there can be ambiguity, especially to anyone who doesn't over-analyze like I do. :) – JuSTMOnIcAjUSTmONiCAJusTMoNICa Nov 06 '17 at 14:49
  • @Nearoo - my last paragraph said why the confusing ANY has been historically replaced by SOME. – Cristian Scutaru Apr 14 '20 at 23:15
3

SOME and ANY are equivalent in the SQL-92 standard, so while it doesn't answer your question to point that out, it does indicate that the history goes back a long way.

Steve Kass
  • 7,144
  • 20
  • 26
2

Remember that some database products have been around for almost three decades. Vendors such as Oracle, IBM and Microsoft have always included features in their products which have subsequently been incorporated into the ANSI standard.

Sometimes these features had been developed by several vendors independently, so either the standard or the vendor had to support synonyms for keywords. For instance Oracle had SELECT DISTINCT long before ANSI specified SELECT UNIQUE. Oracle supports both usages.

I don't know whether a similar scenario applies in the case of SOME and ANY. But it seems likely.

APC
  • 144,005
  • 19
  • 170
  • 281