SQL WHERE Clause

The SQL WHERE clause is used to restrict the number of rows returned by a query. This article describes some of the common ways it can be used.




I have referred to data types very loosely as string, numeric or datetime. This is intended to be a first tutorial and I don't want to get bogged down with too much detail.

Operators

The basic syntax for the SQL WHERE clause is:

WHERE <expression> <operator> <expression>
For example

SELECT * FROM Customer WHERE LastName = 'Smith'
This retrieves all customers whose last name is 'Smith'. Strings should always be enclosed in quotes. Numeric values should not be enclosed in quotes. To retrieve all customers whose age is 30:

SELECT * FROM Customer WHERE Age = 30
Other operators are <, >, <= >=, <>, LIKE, BETWEEN, IN, IS. There are other operators, but these have direct equivalents in this list. For example !> (not greater than) is the same as < (less than).

LIKE Operator

The LIKE operator allows wildcards to be included in the comparison. Wildcards are % and _. % matches with zero, one or more characters. _ matches with exactly one character. For example

SELECT * FROM Customer WHERE LastName LIKE 'S%'
matches all rows where the LastName column begins with an 'S'. This would match with 'S', 'Sm', 'Smi', etc. The default SQL Server installation is not case-sensitive, so this would also match 's', 'SM', 'sMi', etc.

SELECT * FROM Customer WHERE LastName LIKE '%t%'
matches all rows containing the letter 't'.

SELECT * FROM Customer WHERE LastName LIKE 'S_m'
matches all 3 letter names beginning with 'S' and ending with 'm'.

The SQL WHERE clause also provides the ability to search for % and _ in a LIKE statement through the use of 'escape' characters. This will be described in a future article.

BETWEEN

Matches all rows where a column or expression falls between 2 values. This can be used for numeric, string or datetime columns.

SELECT * FROM Customer WHERE Age BETWEEN 25 AND 40
or

SELECT * FROM Customer WHERE LastName BETWEEN 'Brown' AND 'Davis'

IN

The IN operator matches with a selection of one or more values. For example

SELECT * FROM Customer WHERE BirthTown IN ('London', 'Washington', 'Paris')
The IN operator can also be used with a subquery. This is beyond the scope of this article, but I have included an example to demonstrate the syntax.

SELECT * FROM Customer WHERE BirthTown IN (SELECT CapitalCity FROM Country)
matches all customers born in a capital city.

IS

The ANSI standard for SQL specifies that NULL is treated differently. I am not going into detail here about the meaning of NULL, but bear in mind that you cannot use the = operator to test for it. The IS operator should be used, as follows

SELECT * FROM Customer WHERE Gender IS NULL
This may return all customers who have preferred not to specify their gender (depending on application/schema design of course).

Multiple Conditions

Finally I want to show how multiple conditions can be combined. AND, OR and NOT can be used in combination to create quite complex criteria.

SELECT * FROM Customer WHERE (LastName = 'Smith' OR LastName = 'Brown') AND Age = 30 AND BirthTown NOT IN ('London', 'Paris')
matches all 30 year-olds called 'Smith' or 'Brown' who were not born in London or Paris. Note the brackets; order of precedence is NOT, then AND, and finally OR. Without the brackets the above statement would have matched all 30 year-olds called 'Brown' not born in London or Paris PLUS anyone called 'Smith' irrespective of age or birth town!

Summary

In this article I have covered the basics of the SQL WHERE clause. I have described commonly used operators and explained how they can be combined.
I briefly covered use of an SQL subquery and this is explained in the next article.

You should also take performance into account when writing an SQL WHERE clause. You can easily write code that does not use available indexes if you don't know what you're doing. Keep an eye on the SQL Server Performance section of this site for future articles on the subject.

You can keep up-to-date by subscribing to my RSS feed, or to my newsletter.

SQL FAQ |  Recent Articles |  SQL Server Administration |  SQL Server Performance
SQL Server Scripts |  SQL Server Error Reference |  Learn SQL |  Reviews |  Miscellany
My Services |  Contact Me |  Privacy Policy |  Site Map

© Richard Fryar. All Rights Reserved.