Search

Saturday, December 14, 2013
0 comments

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

5:10 AMSaturday, December 14, 2013
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')

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


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

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

Retrieve records matches condition
Retrieve records within given range
Protected by Copyscape Online Copyright Protection Software

0 comments :

Post a Comment

 
Toggle Footer
Top