The SQL WHERE Clause

In our introduction to the SQL SELECT statement we saw how to select specific columns from a table. In this article we will see how the WHERE clause can be used to select specific rows from the table that match certain specified criteria or conditions.

The WHERE clause is an optional clause in the following SQL statements:

• SELECT statments
• DELETE statements
• UPDATE statements

This makes the WHERE clause a major foundation to build upon when learning SQL as it is so frequently used.

Comparison Operators

SQL uses comparison operators in the WHERE clause to set the criteria that the rows must meet. These comparison operators compare two values against each other, e.g. is x greater than y. The comparison operators return either True or False. ( e.g. x is either greater than y or it isn't. There can be no other result).

The comparison operators are listed in the table below.

The WHERE clause compares the value in a specified column against a specified value and only returns rows where the comparison evaluates to True. The following code demonstrates this by only returning rows from the customer table where the value in the NumberOfOrders column is greater than 5:

SELECT Forename, Surname, CustomerID 
FROM Customers
WHERE NumberOfOrders > 5

These comparison operators can be used on other non numeric data types such as string data types, DateTime data types and Boolean (yes/no) data types:

SELECT *
FROM Customers
WHERE CountryCode = 'UK'

SELECT *
FROM Customers
WHERE LastUpdated <= '01/05/2007'

SELECT *
FROM Customers
WHERE Expired <> True

Logical Operators

Logical operators combine with the comparison operators outlined above to give the WHERE clause the ability to handle multiple criteria in one query - this is sometimes known as specifying compound conditions.

The SQL Logical Operators are listed below:

Examples of using AND, OR and NOT are given below:

SELECT *
FROM Customers
WHERE NumberOfOrders > 5 AND CountryCode = 'UK'

SELECT *
FROM Customers
WHERE LastUpdated > '01/05/2007' OR NumberOfOrders != 0

SELECT *
FROM Customers
WHERE CountryCode = 'UK' AND NOT IsEmployee = True

This last example above would return all customers that lived in the UK and that were not employees of the company.

When you use more than one logical operator in the WHERE clause it is advisable to use brackets - () to specify which parts of the clause to evaluate before evaluating other parts. If no brackets are specified then the logical operators in the WHERE clause are evaluated in the following order: (), NOT, AND and then OR. The example of using brackets below specifies to return customers that live in either the UK or France and that have Made more than five orders:

SELECT *
FROM Customers
WHERE (CountryCode = 'UK' OR CountryCode = 'FR')
 AND NumberOfOrders > 5

If the brackets were omitted as in the example below then the customers returned would ALSO include customers from France who had made 5 or less orders. This is because the AND operator is evaluated before the OR operator. Therefore the resultset returns

1) Customers that live in the UK AND have more than 5 orders
OR
2) Customers that live in France

SELECT *
FROM t_Testing
WHERE CountryCode = 'FR' OR CountryCode = 'UK'
 AND NumberOfOrders > 5

You can see how not using brackets to specify the order of evaluation can quickly make understanding the query quite complicated, even with just two Logical operators. Imagine the required understanding if you were using 5 or more logical operators without brackets. Because of this it is always recommended that you use brackets to help your queries be more readable.

Conclusion

In this article we have given an introduction to the SQL WHERE clause that can be used in SELECT, UPDATE and DELETE statements to filter the rows that the query selects / updates / deletes.

Comparison operators enable us to compare values in a rows column to a specified value and choose to return the row or not. Comparison operators include =, <>, !=, > < >= and <=.

The Logical operators AND and OR enable us to specify more than one (compound) comparison conditions in our WHERE clause for more complex filtering. Logical SQL operators include AND, OR and NOT. When using more than one logical operator in the WHERE clause we should use brackets to indicate the order that the compound conditions should be evaluated in.

In the next article we examine some more advanced aspects of the WHERE clause that enable our SELECT, UPDATE and DELETE statements to perform additional functionality.

Discuss this article in the forums

Tell a friend about this page