Selecting Ranges of Values with BETWEEN
(2 row(s) affected)
But wait! How do I know the name of the department with the ID of 6? Well, you could use a similar query to find out. Try this:
SELECT Department
FROM Departments
WHERE DepartmentID = 6
Executing this query reveals that the department with the ID of 6 is Engineering.
Department
--------------------------------------------------
Engineering
(1 row(s) affected)
Selecting Ranges of Values with BETWEEN
There may be times when you’ll want to search within a database table for rows that fall within a certain range of values. For instance, if you wanted to retrieve from the Departments table all departments that have IDs between 2 and 5, you could use the BETWEEN keyword like so:
SELECT DepartmentID, Department
FROM Departments
WHERE DepartmentID BETWEEN 2 AND 5
As we requested, all departments whose IDs are between 2 and 5 are returned. Note that the range is inclusive, so departments with IDs of 2 and 5 will also be retrieved.
Note that any conditions that use BETWEEN could be easily rewritten by combining two “greater than or equal” and “less than or equal” conditions:
SELECT DepartmentID, Department
FROM Departments
WHERE DepartmentID >= 2 AND DepartmentID <= 5
We could also use the NOT keyword before the BETWEEN keyword to specify all items that fall outside the range, as follows:
303
Chapter 8: Speaking SQL
SELECT DepartmentID, Department
FROM Departments
WHERE DepartmentID NOT BETWEEN 2 AND 5
In this example, all rows whose DepartmentIDs are less than 2 or greater than 5 are returned.
Matching Patterns with LIKE
As we’ve just seen, the WHERE clause allows us to filter results based on criteria that we specify. The example we discussed earlier filtered rows by comparing two numbers, but SQL also knows how to handle strings. For example, if we wanted to search the company’s Employees table for all employees named Zak Ruvalcaba, we'd use the following SQL statement:
SELECT EmployeeID, Username
FROM Employees
WHERE Name = 'Zak Ruvalcaba'
However, we won’t see many such queries in reality. In real-world scenarios, most record matching is done by matching the primary key of the table to some specific value. When an arbitrary string such as a name is used (as in the example above), it’s likely that we’re searching for data based on partially complete information.
A more realistic example is one in which we want to find all employees with the surname Ruvalcaba. The LIKE keyword allows us to perform pattern matching with the help of wildcard characters. The wildcard characters supported by SQL Server are the percentage symbol (%), which matches any sequence of zero or more characters, and the underscore symbol (_), which matches exactly one character.
If we wanted to find all names within our Employees table with the surname of Ruvalcaba, we could modify the SQL query using a wildcard, as follows:
SELECT EmployeeID, Name
FROM Employees
WHERE Name LIKE '%Ruvalcaba'
With this query, all records in which the Name column ends with Ruvalcaba are returned, as shown below.
EmployeeID |
Name |
----------- |
-------------------------------------------------- |
1 |
Zak Ruvalcaba |
304
Using the IN Operator
2 |
Jessica Ruvalcaba |
(2 row(s) affected)
As we knew that the last name was Ruvalcaba, we only needed to place a wildcard immediately before the last name. But what would happen if we didn’t know how to spell the entire last name? That name is pretty difficult to spell! You could solve the problem by modifying your SQL statement to use two wildcards as follows:
SELECT EmployeeID, Name
FROM Employees
WHERE Name LIKE '%Ruv%'
In this case, the wildcard is placed before and after the string Ruv. Although this statement would return the same values we saw in the results table above, it would also return any employees whose names (first or last) contain the sequence Ruv. As SQL is case-insensitive, this would include the names Sarah Ruvin, Jonny Noruvitch, Truvor MacDonald, and so on.
Using the IN Operator
We use the IN operator in SELECT queries primarily to specify a list of values that we want to match in our WHERE clause. Let’s say we want to find all employees who live in California, Indiana, and Maryland. You could write the following SQL statement to accomplish this task:
SELECT Name, State
FROM Employees
WHERE State = 'CA' OR State = 'IN' OR State = 'MD'
A better way to write this statement uses the IN operator as follows:
SELECT Name, State
FROM Employees
WHERE State IN ('CA', 'IN', 'MD')
If you execute this query, you’ll get the expected results. Since our database only contains employees living in CA, only those records will be displayed.
Name |
State |
-------------------------------- |
-------------------------------- |
Zak Ruvalcaba |
Ca |
Jessica Ruvalcaba |
Ca |
305
Chapter 8: Speaking SQL
Ted Lindsey |
Ca |
|
Shane Weebe |
Ca |
|
David |
Levinson |
Ca |
Geoff |
Kim |
Ca |
(6 row(s) affected)
Sorting Results Using ORDER BY
Unless you specify some sorting criteria, SQL Server can’t guarantee to return the results in a particular order. We’ll most likely receive the results sorted by the primary key, because it’s easier for SQL Server to present the results in this way than any other, but this ordering isn’t guaranteed. This explains why, in some of the examples we’ve completed so far, the order of the results you see on your machine may differ from what you see in this book. The ORDER BY clause provides you with a quick way to sort the results of your query in either ascending or descending order. For instance, to retrieve the names of your employees in alphabetical order, you would need to execute this command:
SELECT EmployeeID, Name
FROM Employees
ORDER BY Name
Looks simple, doesn’t it?
EmployeeID Name
----------- --------------------------------------------------
5David Levinson
6Geoff Kim
2 |
Jessica Ruvalcaba |
|
4 |
Shane Weebe |
|
3 |
Ted |
Lindsey |
1 |
Zak |
Ruvalcaba |
(6 row(s) affected)
Note that the default ordering here is ascending (i.e. running from A to Z). You could add the DESC designation (for descending) to the end of the statement, to order the results backwards:
SELECT EmployeeID, Name
FROM Employees
ORDER BY Name DESC
306