Home

SQL Operators: IN
     

Introduction

If you have a series of records and want to find a record or a group of records among them, you can use the IN operator by adding it to a WHERE statement. The IN operator is a type of various OR operators. It follows this formula:

 
IN(Expression1, Expression2, Expression_n)

Each Expression factor can be one of the values of a column. This is equivalent to Expression1 OR Expression2 OR Expression_n, etc.

To create an IN expression in the Table window, select the desired columns. Under the Filter column that corresponds to the field on which you want to set the condition, type the IN expression. Here is an example:

IN

To create an IN expression in SQL, after the WHERE operator, type the name of the column that holds the values to be considered, followed by the IN expression. From our list of students, imagine that you want to get a list of students who live either in Silver Spring, in Rockville, or in Chevy Chase. You can write an IN expression as follows:

SELECT FirstName, LastName, Sex, City, State
FROM Students
WHERE City IN (N'Silver Spring', N'Rockville', N'Chevy Chase')

To make the expression easier to read, you should include the whole IN statement in its own parentheses. Here is an example:

IN

When creating your IN statement, you can combine any of the other features we have so studied so far. For example, to get a list of  single family and townhouses located in Rockville, you would create a statement as follows:

SELECT house.PropertyNumber AS [Prop #],
       house.PropertyType AS Type,
       house.YearBuilt AS [Year Built],
       house.City,
       house.State,
       house.ZIPCode AS [ZIP Code],
       house.Bedrooms AS Beds,
       house.Bathrooms AS Baths,
       house.MarketValue AS Value
FROM Properties house
WHERE (house.PropertyType IN (N'Single Family', N'Townhouse')) AND
      (house.City = N'Rockville')

If you are working in a Windows application, you can assign the IN expression to the Filter property of the binding source.

   
 
Home Copyright © 2010-2016, FunctionX