The Sky's the Limit with the T-SQL WHERE Clause

Excerpt by Don Kiely

You will rarely want SQL Server to return every row in a table. The most efficient queries retrieve only the data you will actually use-no more, no less. To limit the rows that the query returns, you have to specify the subset of records that you want. The WHERE clause is the primary row filter of a SELECT statement.

You use the WHERE clause to specify the search conditions that SQL Server should use to identify rows that should or shouldn't be included in the result set. The simplest WHERE clauses check for equality, so that each row returned has to meet the specified condition.

Using Transact-SQL comparison operators in a WHERE clause lets you build queries that search for records other than those that are exactly equal to a static value. This article takes a look at using the IN comparison operator.

Using the IN Operator

Sometimes you have to filter rows of a table based on whether a column's value matches multiple values. You can use the OR operator to filter on a list of values, as shown in the following query, which returns customers in France or Spain:

SELECT CustomerID, Country
FROM dbo.Customers
WHERE Country = 'France'
OR Country = 'Spain'

However, a much more efficient way (one that SQL Server can often execute faster) is to use the IN operator. The IN operator compares a field against an array of values. The following query returns the same list of all customers in France and Spain:

SELECT CustomerID, Country
FROM dbo.Customers
WHERE Country IN ('France', 'Spain')

The following Figure shows the result set:

Using the IN operator

TIP: You can also use the IN operator with a subquery, which is a query within a query. For example, to find all customers who have not placed orders, you could use this:

FROM dbo.Customers
WHERE CustomerID
NOT IN (SELECT CustomerID FROM dbo.Orders);

SQL Server first executes the subquery (highlighted in this code), then uses that list of CustomerID values as the set of values used by the IN operator to compare CustomerID values in the Customers table against. So you are not limited to using the IN operator with a fixed set of static string values.

The WHERE clause of a SELECT statement in SQL Server is an incredibly powerful way to filter the rows returned by a data selection query. There are a number of ways to put this clause to use, and as you learn more about T-SQL you'll see that you can get very creative with how you control the rows in a query result set. By creating a filtering condition based on other data in the database, you can dynamically return results based on the current state of your data, rather than a fixed set of static conditions. The sky's the limit!

Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.

Don Kiely
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

This course excerpt was originally posted April 23, 2013 from the online courseware SQL Server 2012, Part 8 of 9: T-SQL Select, Where, and Clauses by Don Kiely

SQL Server