LearnNowOnline

Blog

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

Excerpt by Don Kiely | April 23, 2013

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:

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:

SELECT CustomerID

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!

This 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.