What is exactly "SOME / ANY" and "IN"

Question

When someone is calling a function by "SOME" or "ANY" makes me unsure about this.

Can I think about "SOME" as "OneOf" or "OneFrom"? Like an array (table) searching function? If yes, then what's the difference between SOME and IN?

I am total newbie with SQL

Sorry for my bad english, for some reason I think I am little overtired to use it correctly now

I personally never have any need for `SOME`, `ANY`, or `ALL`. The rest of the SQL language seems quite adequately expressive without them. — Gordon Linoff, Mar 06 '14 at 02:35
Yeah, I think the same, but I have to describe that operators with examples (I'll do them alone). Just need a little tip with definition — Bartłomiej Sobieszek, Mar 06 '14 at 02:37
syntax sugar for exists: see http://stackoverflow.com/questions/2298550/oracle-any-vs-in — Eli Algranti, Mar 06 '14 at 02:41
[The Postgres manual has a good page explaining it.](http://www.postgresql.org/docs/current/interactive/functions-subquery.html) — Erwin Brandstetter, Mar 06 '14 at 02:43

Wagner DosAnjos · Accepted Answer · 2014-03-06T02:57:00.200

Check the following link for some examples that should clarify the difference between them:

http://technet.microsoft.com/en-us/library/ms175064.aspx

IN only tests for equality, while ALL/ANY/SOME can test against any of the comparison operators.

Here is a sample SQL Fiddle.

-- SQL 2012

CREATE TABLE T1 (ID int) ;

INSERT T1 VALUES (1) ;
INSERT T1 VALUES (2) ;
INSERT T1 VALUES (3) ;
INSERT T1 VALUES (4) ;

SELECT CASE WHEN 3 IN (SELECT id FROM t1) THEN 'in true' ELSE 'in false' END
UNION

SELECT CASE WHEN 3 < SOME (SELECT id FROM t1) THEN 'some true' ELSE 'some false' END
UNION

SELECT CASE WHEN 3 < ANY (SELECT id FROM t1) THEN 'any true' ELSE 'any false' END
UNION

SELECT CASE WHEN 3 < ALL (SELECT id FROM t1) THEN 'all true' ELSE 'all false' END;


/* Returns

all false
any true
in true
some true

*/

score 0 · Answer 2 · answered Mar 06 '14 at 02:59

If any1 else will google that question I will leave the answer

SOME is a synonym for ANY. IN is equivalent to = ANY.

IN compares every row of the right-hand result looking for (and only for) equality

SOME / ANY works in the same way, but you can use a operator like < != etc. to precise the result (true / false / unknown)

also from http://technet.microsoft.com/en-us/library/ms188074.aspx

When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN. When SET ANSI_NULLS is OFF, the same rules apply, except an equals (=) operator returns TRUE if both expressions are NULL. For example, NULL = NULL returns TRUE when SET ANSI_NULLS is OFF.

What is exactly "SOME / ANY" and "IN"

2 Answers2

Linked