Home page
 
 Home 
 ASP 
 PHP 
 SQL 
 HTML 
 JavaScript 
 Search 
 Contact 
 
Search
or browse popular tags
Subscription

Sign up for the free email newsletter for new tips, tutorials and more. Enter your email address below, and then click the button.

Privacy Policy

RSS Twitter

SQL: IN Operator

Print

We've seen that Between defines a range of values to check against for inclusion or exclusion from the result table. This is not always enough. What if we needed to check for certain values only? Values that do not always fit into a neat range. To accommodate this, SQL allows the use of the IN operator. In SQL, there are two uses of the IN keyword, and this section introduces the one that is related to the Where clause. When used in this context, we know exactly the value of the returned values we want to see for at least one of the columns.

The syntax for using the IN keyword is as follows:

SELECT column_name
FROM derived_table
WHERE column_name [NOT] IN ('value1', 'value2', ... ,'value_n')

This SQL statement will return the records where column1 is value1, value2..., or value_n. The number of values in the parenthesis can be one or more, and they can be numerical or characters.

Example #1

Select *
From EmployeeAddressTable
Where FirstName IN ('Mary', 'Sam')

This would return all rows where the FirstName is either Mary or Sam. Because the * is used in the select, all fields from the EmployeeAddressTable table would appear in the result set.

Example #2

SELECT *
FROM EmployeeAddressTable
WHERE FirstName = 'Mary'
OR FirstName = 'Sam'

It is equivalent to the previous statement. As you can see, using the IN function makes the statement easier to read and more efficient.

Example #3

Select *
From EmployeeAddressTable
Where FirstName NOT IN ('Mary', 'Sam')

This query uses the NOT operator to retrieve all records where the employee's lastname is neither Mary nor Sam. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Example #4

Select *
From EmployeeStatisticsTable
Where Benefits in (15000, 12000)

You can also use the IN function with numeric values. This SQL statement would return all Employees for which Benefits is either 15000 or 12000.


If you'd like to see how it works, enter SQL statement and press Execute
 

 
 



Tags:

Add To: Add to dzone dzone | Digg this digg | Add to del.icio.us del.icio.us | Stumble it stumbleupon

  • Comments





Copyright © 2005-2012             www.WebCheatSheet.com All Rights Reserved.