Article Information
This article contains and tries to cover following subjects:
- Explanation of IN keyword in a SQL query
- Syntax of IN and how to use it to test values from a list
- An example of IN keyword in a SQL basic query
This article contains and tries to cover following questions and issues:
- How to query a column to select values provided by a list?
- How to build test condition with WHERE to look for particular list of values?
- How to find more than one value in a column with SQL query?
Explanation of IN keyword in SQL
IN keyword in SQL is used to compare values against the values in a list. With IN keyword, value of list is provided to WHERE condition. SQL looks for the values for matching in list. If matching value is found, the row which contains value is selected.
Syntax of IN keyword in SQL
SELECT -Column Name-
FROM -Table Name-
WHERE -column name-
IN -column name- (-Values-)
Example of IN keyword in SQL
As example, lets query Adventureworks sample database of Microsoft SQL server. From "HumanResources.Department" Table, we query "Name" and "Group Name" columns and retrieving the rows which "groupname" is "research and development" and "manufacturing".
USE AdventureWorks;
GO
SELECT Name,
GroupName
FROM HumanResources.Department
WHERE GroupName
IN ('Research and Development','Manufacturing')
Output:
Above example, we queried the table which their group names are "Research and Development" and "Manufacturing".
Remarks:
- IN keyword looks for exact match from its list.
- Apart from providing values to list, column names can be provided also to check a value if exist within that provided columns. For example:
USE AdventureWorks;
GO
SELECT Name,
GroupName
FROM HumanResources.Department
WHERE 'Manufacturing'
IN (Name, GroupName)
Above query, we look within the list of columns, if any column from that list contains value as "manufacturing". If exist, WHERE condition evaluates TRUE, and SELECT statement selects rows for TRUE evaluated ones.
- In keyword can be reversed with NOT keyword as well. For example:
USE AdventureWorks;
GO
SELECT Name,
GroupName
FROM HumanResources.Department
WHERE 'Manufacturing'
NOT IN (Name, GroupName)
Above query looks to list of columns and within their values which have not "manufacturing" value. Returning rows will be ones which their name or groupname have not manufacturing.