Home >> FAQs/Tutorials >> SQL Server FAQ

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

By: FYIcenter.com

(Continued from previous topic...)

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...)

  1. What Is a Boolean Value?
  2. What Are Conditional Expressions?
  3. What Are Comparison Operations?
  4. How To Perform Comparison on Exact Numbers?
  5. How To Perform Comparison on Floating Point Numbers?
  6. How To Perform Comparison on Date and Time Values?
  7. How To Perform Comparison on Character Strings?
  8. What To Test Value Ranges with the BETWEEN Operator?
  9. What To Test Value Lists with the IN Operator?
  10. What To Perform Pattern Match with the LIKE Operator?
  11. How To Use Wildcard Characters in LIKE Operations?
  12. How To Test Subquery Results with the EXISTS Operator?
  13. How To Test Values Returned by a Subquery with the IN Operator?
  14. What Are Logical/Boolean Operations?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...