BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Simplifying Repetitive Queries with IN

The usual use of the IN clause in a Transact-SQL (T-SQL) query allows a column to be compared with multiple values. For searches, where the same literal is compared with multiple columns, the IN clause can be used to simplify the query.

IN Clause

In the SQL Server Programming Fundamentals tutorial the article examining basic Transact-SQL (T-SQL) queries included a section that described the use of the IN clause. The most common use of this clause is to compare a column with a set of values and return a row if the column's value is present. You can also check if the column value is not in the set using NOT IN.

For example, the following query returns all rows from the People table where the Country column is set to one of the four specified values:

SELECT * FROM People WHERE Country IN ('England', 'Scotland', 'Wales', 'Northern Ireland')

Sometimes you might want to perform the reverse operation; checking that a single value is present in one or more columns in your query's results. This is useful when you are searching for a specific value that may appear in several places. The reverse operation is possible using the IN and NOT IN clauses, although it is less commonly encountered.

To demonstrate, we need a table and some data to query. To create this, run the following script against a test SQL Server database.

CREATE TABLE Jobs
(
    JobID INT IDENTITY(1,1),
    JobName VARCHAR(50),
    PrimaryCoder VARCHAR(10),
    SecondaryCoder VARCHAR(10),
    Helper VARCHAR(10)
)

INSERT INTO Jobs VALUES ('Create database', 'Jim', 'Bob', 'Mel')
INSERT INTO Jobs VALUES ('Develop data classes', 'Sam', 'Dave', NULL)
INSERT INTO Jobs VALUES ('Develop business logic', 'Dave', 'Mel', 'Sam')
INSERT INTO Jobs VALUES ('Develop screen one', 'Dave', 'Mel', 'Sam')
INSERT INTO Jobs VALUES ('Develop screen two', 'Jim', 'Pat', 'Dave')

The sample Jobs table holds software development tasks that need to be completed. Each job has a unique identifier, which is created automatically using an identity column. The jobs also have a name and up to three associated developers. The names of the developers are held in the PrimaryCoder, SecondaryCoder and Helper columns. In a real-world database these would probably be further normalised.

If you wanted to find all of the jobs where Sam has some involvement, you might decide to use a query similar to that shown below. Here we have three comparisons, one for each of the coder and helper columns.

SELECT
    JobName
FROM
    Jobs
WHERE
    PrimaryCoder = 'Sam'
OR
    SecondaryCoder = 'Sam'
OR
    Helper = 'Sam'

/* RESULTS

JobName
----------------------
Develop data classes
Develop business logic
Develop screen one

*/

Using three comparisons is repetitive and increases the risk of errors. This only becomes worse if more columns needed to be compared to the value. We can simplify the query by replacing the three comparisons with a single IN clause. In this case we want to check that 'Sam' appears in one of the three important columns. The updated query is as follows:

SELECT
    JobName
FROM
    Jobs
WHERE
    'Sam' IN (PrimaryCoder, SecondaryCoder, Helper)

/* RESULTS

JobName
----------------------
Develop data classes
Develop business logic
Develop screen one

*/

You can also use NOT IN to ensure that a particular value does not appear in a set of columns, as in the following example. This returns all jobs for which Sam does not have a role.

SELECT
    JobName
FROM
    Jobs
WHERE
    'Sam' NOT IN (PrimaryCoder, SecondaryCoder, Helper)

/* RESULTS

JobName
------------------
Create database
Develop screen two

*/

The items within the IN clause's set do not have to just be column names. You can also include a mixture of variables, functions or other discrete values. The latter being useful when the value for which you are searching is provided as a stored procedure's parameter.

10 July 2013