Often, it is necessary to define one or more conditions that limit the selected rows. The WHERE clause specifies a Boolean expression (an expression that returns a value of TRUE or FALSE) that is tested for each row to be returned (potentially). If the expression is true, then the row is returned; if it is false, it is discarded.
Example 6.2 shows the use of the WHERE clause.
Get the names and numbers of all departments located in Dallas:
USE sample;
SELECT dept_name, dept_no
FROM department
WHERE location = 'Dallas';
Code language: PHP (php)
The result is
dept_name | dept_no |
---|---|
Research | d1 |
Marketing | d3 |
In addition to the equal sign, the WHERE clause can contain other comparison operators, including the following:
<> | (or !=) not equal |
< | less than |
> | greater than |
>= | greater than or equal |
<= | less than or equal |
!> | not greater than |
!< | not less than |
Example 6.3 shows the use of a comparison operator in the WHERE clause.
Get the last and first names of all employees with employee numbers greater than or equal to 15000:
USE sample;
SELECT emp_lname, emp_fname
FROM employee
WHERE emp_no >= 15000;
Code language: PHP (php)
The result is
emp_lname | emp_fname |
---|---|
Smith | Matthew |
Barrimore | John |
James | James |
Moser | Sybill |
An expression can also be a part of the condition in the WHERE clause, as Example 6.4 shows.
Get the project names for all projects with a budget > 60000 £. The current rate of exchange is 0.51 £ per $1.
USE sample;
SELECT project_name
FROM project
WHERE budget*0.51 > 60000;
Code language: PHP (php)
The result is
project_name |
---|
Apollo |
Mercury |
Comparisons of strings (that is, values of data types CHAR, VARCHAR, NCHAR, or NVARCHAR) are executed in accordance with the collating sequence in effect (the “sort order” specified when the Database Engine was installed). If two strings are compared using ASCII code (or any other code), each of the corresponding (first, second, third, and so on) characters will be compared. One character is lower in priority than the other if it appears in the code table before the other one. Two strings of different lengths are compared after the shorter one is padded at the right with blanks, so that the length of both strings is equal. Numbers compare algebraically. Values of temporal data types (such as DATE, TIME, and DATETIME) compare in chronological order.