ORACLE "ANY" Keyword in SQL SERVER

Question

The following query works well in Oracle, but not in SQL SERVER. I know "ANY", keyword in SQL SERVER developed for some other purpose, but I Wanna know is there any similar keyword in SQL SERVER FOR doing this In the following query fisrtname, lastname, middlename are columns in Person table

SELECT * FROM Person WHERE 'Somename' = ANY(firstname, lastname,middlename)

possible duplicate of [Difference between in and any operators in sql](http://stackoverflow.com/questions/3699356/difference-between-in-and-any-operators-in-sql) — Lukas Eder, Jul 30 '12 at 12:40
Or also: http://stackoverflow.com/questions/2298550/oracle-any-vs-in — Lukas Eder, Jul 30 '12 at 12:41

score 3 · Accepted Answer · answered Jul 30 '12 at 12:35

3

The equivalent is

SELECT * FROM Person WHERE 'Somename' IN (firstname, lastname, middlename)

answered Jul 30 '12 at 12:35

Tim Rogers

21,297
6
52
68

i never tried to put column names in the IN condition i'm just checking it and you posted the answer :P – Waqar Janjua Jul 30 '12 at 12:37
I'm not sure, but does the optimizer really index the query correctly when using IN like this? – njr101 Jul 30 '12 at 12:46
ya, this works cool, how can I do this for checking not null? – Nageswaran Jul 30 '12 at 12:47
@njr Queries aren't indexed - tables are indexed. The optimizer won't treat this any differently to an expanded OR statement. – Tim Rogers Jul 30 '12 at 12:54
@Surendhar I don't understand. The `IN` statement compares values. If you want to check a value is not null use `IS NOT NULL`. – Tim Rogers Jul 30 '12 at 12:56
@TimRogers: Ya, but how to use IS NOT NULL with "IN" condition? – Nageswaran Jul 30 '12 at 13:16
@Surendhar Sorry, I still don't understand your question. Do you mean you want to check if `firstname`, `lastname`, or `middlename` are null? You don't need to because you are checking if any of them matches a value, and they are not going to match a value if they are null. – Tim Rogers Jul 30 '12 at 13:27
@Timrogers: say I wanna select rows where firstname or lastname or middle name is not null. Writing query using OR condition is trivial one, but just wanna know is there any possibility to write query using ANY keyword – Nageswaran Jul 30 '12 at 17:10

score 0 · Answer 2 · answered Jul 30 '12 at 12:18

0

Can you not simply do:

SELECT * FROM Person
WHERE
     firstname = 'Somename'
  OR lastname = 'Somename'
  OR middlename= 'Somename'

There are other possibilities but in most cases they will not let the optimizer make such good use of the indexes e.g.

SELECT * FROM Person
WHERE
     ('---' + firstname + '---' + lastname + '---' + middlename + '---')
     LIKE '%---Somename---%'

answered Jul 30 '12 at 12:18

njr101

9,499
7
39
56

The 2nd solution looks good, but what if I am checking for not null. – Nageswaran Jul 30 '12 at 12:26

score 0 · Answer 3 · answered Jul 30 '12 at 12:19

0

I'm not aware of a way to check multiple columns other than writing something like this:

SELECT * FROM Person WHERE 
firstname = 'Somename' 
OR lastname = 'Somename' 
OR middlename = 'somename'

answered Jul 30 '12 at 12:19

dougajmcdonald

19,231
12
56
89

This is obvious, but I want to know is there any similar keyword in SQL Server. In this case its only 3 columns, what if there are ten columns, and I want to check for some other value, then I don't want to change the value again and again in all places. – Nageswaran Jul 30 '12 at 12:24

ORACLE "ANY" Keyword in SQL SERVER

3 Answers3