Using the IN Comparison Operator in SQL

Suppose, you wish to see a list of departments under two different locations i.e. 1800 and 2700. If you use the BETWEEN operator, you’ll get a list of departments (including those falling between the two ranges) that you don’t intend to see. Another alternate will be to use a list of conditions like: location_id=1800 OR location_id=2700. Although it is a valid condition, but what if you add ten or more locations to your WHERE clause? Obviously, the statement will grow up in size and you won’t like typing such long statements that consequently enhances program code. To cope with the situation, you’re provided with the IN operator, where you just provide a list of desired values in parentheses, as shown in the following example. Since location_id is a numeric field, you provided the values without the quotation marks. Note that only characters and dates used in the IN list are enclosed within single quotes.

NOTE

The use of the IN operator is a cleaner way if you are working with long lists of conditions. Secondly, the IN operator can also contain another SELECT statement to form a more dynamic WHERE clause.

    SQL Statement :

SELECT       department_id, department_name, location_id

FROM          departments

WHERE       location_id IN (1800,2700);

    Output :