The recent series of Jump Start columns have been covering different uses of the T-SQL SELECT statement. The SELECT statement is the core of almost all relational database applications. In recent columns I covered subqueries and then the ORDER BY clause. In this column, I’ll continue my coverage of the SELECT statement by showing you how to use the SELECT statement’s basic WHERE clause.
The WHERE clause is used to restrict the row set that is returned by a SELECT statement. The WHERE clause requires that you supply what is called a search condition to specify the rows that will be returned. In its most common form the search condition is basically an expression that tests the value of one or more columns. For example, to select just the rows from the Customers table in the Northwind database where the value in the City column is London you would use a SELECT statement with the following WHERE clause:
SELECT * FROM Customers WHERE City = 'London'
Here, you see the search condition immediately following the WHERE clause contains an expression that uses the column named City, an operator which in this case is an equal sign, and the literal value ‘London’. This simple form of the WHERE clause accepts the following operators:
= tests the condition of being equal.
tests the condition of not being equal
!= tests the condition of not being equal
> tests the condition of greater than
>= tests the condition of greater than or equal
!> tests the condition of not being greater
You can combine multiple conditions together for more restrictive searches. For example to see all the cities in the UK that are not London you could use the following WHERE clause.
SELECT * FROM Customers WHERE Country = 'UK' AND City 'London'