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.
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.
|<>||Not Equal to|
|!=||Not Equal to|
|<=||Less than or equal to|
|>=||Greater than or equal to|
|!<||Not Less than|
|!>||Not greater than|
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:
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:
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:
|AND||This adds another condition to the row filtering effect of the WHERE clause and only returns rows when both conditions return true.|
|OR||This adds another condition to the row filtering effect of the WHERE clause and returns rows that match either of the conditions.|
|NOT||The NOT operator tells the row filtering effect of the WHERE clause to return everything the condition following the NOT have not specified.|
Examples of using AND, OR and NOT are given below:
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:
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
2) Customers that live in France
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.
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.