How to Retrieve Records Containing any value from Set of Values: SQL Programming

List operators, in context of SQL programming, are used to compare a value to a list of literal values that have been specified. When field name must contain one of the values returned by an expression for inclusion in the query result, list operators comes in to existence.

Sometimes, you might want to retrieve data either within a given range or after specifying a set of values to check whether the specified value matches any data of the table. This type of operation is performed by using the IN and NOT IN keywords. The syntax of using the IN and NOT IN operators in the SELECT statement is:

SELECT column_list
FROM table_name
WHERE expression list_operator ('value_list')

Where

• column_list: list of fields to be shown in output.
• table_name: from the records are to be retrieved.
• expression is any valid combination of constants, variables, functions, or column-based expressions.
• list_operator is any valid list operator, IN or NOT IN.
• value_list is the list of values to be included or excluded in the condition.

IN keyword selects values that match any one of the values in a list. The following SQL query retrieves records of employees who are Recruiters or Stockers from the Employee table:

SELECT BusinessEntityID, JobTitle, LoginID FROM HumanResources.Employee WHERE JobTitle IN ('Recruiter', 'Stocker')

NOT IN keyword restricts the selection of values that match any one of the values in a list. The following SQL query retrieves records of employees whose designation is not Recruiter or Stocker:

SELECT BusinessEntityID, JobTitle, LoginID FROM HumanResources.Employee WHERE JobTitle Not IN ('Recruiter', 'Stocker')

Retrieve records matches condition
Retrieve records within given range