SQL Server FAQ - IN - Testing Value in a Value List

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

What To Test Value Lists with the IN Operator?

Sometimes you want to test a given value against a list of values. You can do this in a loop with the regular "equal" operator. But you can also use the special comparison operator IN to get it done with the following syntaxes:

1. In-the-list test
test_value IN (value_1, value_2, ..., value_n)

- Returns the same results as the following expression
test_value = value_1
OR test_value = value_2
...
OR test_value = value_n

2. Not-in-the-list test
test_value NOT IN (value_1, value_2, ..., value_n)

- Returns the same results as the following expression
test_value <> value_1
AND test_value <> value_2
...
AND test_value <> value_n

Here are two examples of using the IN operator:

DECLARE @my_number INT;
SET @my_number = 767;
SELECT CASE WHEN 
      @my_number IN (525, 272, 532, 767, 150, 637)
   THEN 'You are a winner.'
   ELSE 'You are not a winner.'
   END;
GO
You are a winner.

DECLARE @my_number INT;
SET @my_number = 676;
SELECT CASE WHEN 
      @my_number NOT IN (525, 272, 532, 767, 150, 637)
   THEN 'You are not a winner.'
   ELSE 'You are a winner.'
   END;
GO
You are not a winner.

(Continued on next topic...)