| Most of the time, you will not want to retrieve all the records from a particular table. It is more likely that you will only want to return information that meets a certain criterion such as "Show me all the employees in the marketing department." To limit the amount of data that a SELECT statement will return, you use the WHERE keyword to set up a matching criterion. For example, the following SELECT statement SELECT FirstName, LastName FROM Employees WHERE LastName = 'Johnson' would return only those employees whose last name is Johnson. There are several matching conditions that can be used in a WHERE clause. You can also broaden the matching criterion by using wildcards. For example, the following SQL statement SELECT FirstName, LastName FROM Employees WHERE LastName LIKE 'J%' would return all employees whose last names begin with the letter J. For more information about wildcards, see the sidebar "Dealing With Wildcards" later in this step. Table 3.5 outlines the different comparisons that can be made using the WHERE clause. Table 3.5. WHERE Clause Comparisons| Variable Name | Description |
|---|
| = | This tests whether values on both sides of the = are equal. | | <> | Not equal to. This tests to see whether values on both sides of the <> are different. | | > | Greater than. This checks to see whether the value on the left of the > is greater than the value on the right. | | >= | Greater than or equal to. This checks to see whether the value to the left of the >= is greater than or equal to the value on the right. | | < | Less than. This check to see whether the value to the left of the < is less than the value to the right. | | <= | Less than or equal to. This checks to see whether the value on the left of the <= is less than or equal to the value on the right. | | BETWEEN | This checks to see whether the value to the left of the BETWEEN keyword is between two values. The value range is inclusive. | | IN | This checks to see whether the value to the left is contained IN a list of values to the right. | | IS NULL | This checks to see whether the field to the left of IS NULL contains a NULL value. A NULL value is the absence of a value. For example, empty fields in the database would qualify as NULL values. | | IS NOT NULL | This checks to see whether the field to the left contains a non-NULL value. | | LIKE | This checks to see whether the value to the left of the LIKE keyword contains a specific pattern. | | NOT | This negates a test. |
When comparing string/text values, the value to the right must usually be surrounded by single quotation marks, as follows: WHERE LastName = 'Johnson' When comparing numerical values, however, do not use the quotation marks because it will be seen as a string value by SQL. The following line of code is an example of a numeric comparison using a WHERE clause: WHERE DepartmentID = 2 Check your database documentation if you are experiencing problems. Dealing with Wildcards There will be plenty of times when you want to match a particular pattern rather than an exact value. To do this, you can use special SQL characters known as wildcards. Wildcards are similar to the concept of wildcards that you might use in poker or other card games. If the dealer says, "Jokers are wild," you can substitute the value of any card for the joker. Similarly, SQL has special characters that act in the same way. Table 3.6 outlines the use of several SQL wildcards. Table 3.6. SQL Wildcards| Wildcard | Usage |
|---|
| * | Selects all values. SELECT * FROM Employees This would select all field names from the Employees table. | | % | Matches any number of characters. WHERE LastName LIKE 'M%' This would match last names beginning with the letter M. WHERE LastName LIKE '%t' This would match last names that end in t. | | _ | The underscore represents only a single character. To find first names that are only four letters long and that begin with the letter E, use three underscore characters, as follows: WHERE FirstName LIKE 'E___' |
|
|