Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL - SOME | ANY why are they same with different names?

Tags:

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?

like image 445
dance2die Avatar asked Sep 05 '09 18:09

dance2die


People also ask

Are some and any same in SQL?

ANY and SOME are the same. You can use any one. A subquery used with the ANY or SOME operator, can only return a single column values. The ANY or SOME operator must be preceded by comparison operators like =, !=

How do I find similar names in SQL?

To find the duplicate Names in the table, we have to follow these steps: Defining the criteria: At first, you need to define the criteria for finding the duplicate Names. You might want to search in a single column or more than that. Write the query: Then simply write the query to find the duplicate Names.

What is == in SQL?

== (Equal) (SSIS Expression)

What is the with clause in SQL?

The WITH clause in SQL was introduced in standard SQL to simplify complex long queries, especially those with JOINs and subqueries. Often interchangeably called CTE or subquery refactoring, a WITH clause defines a temporary data set whose output is available to be referenced in subsequent queries.


2 Answers

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"

like image 107
gbn Avatar answered Sep 21 '22 12:09

gbn


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.

like image 27
Niikola Avatar answered Sep 22 '22 12:09

Niikola