| Previous | Table of Contents | Next |
Inequality Conditions
In addition to the equality operator (=), SQL Server allows the use of a number of other comparison operators in the WHERE clause. Table 3.1 describes the operators that are available, and what each of these operators means.
| Operator | Description |
|---|---|
| > | Greater than |
| >= | Greater than or equal to |
| < | Less than |
| <= | Less than or equal to |
| <>, != | Not equal to |
These operators are necessary to perform different kinds of comparisons. To find all the books in the titles table that cost more than $13, try this query:
select title, price from titles where price > $13
title ------------------------------------------------------------------------- The Busy Executive’s Database Guide Straight Talk About Computers Silicon Valley Gastronomic Treats But Is It User Friendly? Secrets of Silicon Valley Computer Phobic AND Non-Phobic Individuals: Behavior Variations Prolonged Data Deprivation: Four Case Studies Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean Sushi, Anyone? (9 row(s) affected)
All titles in the titles table that match the WHERE clause (that is, that cost more than $13) are included in the result set. Since I am searching in a column containing money values, I use the expression price > $13 rather than price > 13. Both expressions will work, but the first expression will be more efficient and therefore faster, because the server does not have to convert the integer 13 to the fixed-decimal point $13.
OK, so now you know how to restrict rows using the WHERE clause. But what if you need to apply multiple conditions to a row? T-SQL allows you to use boolean logic to specify many conditions in a WHERE clause. If you’re new to programming, the next section provides a crash course in boolean logic. If you’re already comfortable with this topic, feel free to skip ahead.
Boolean Logic
Boolean logic is a form of algebra in which all values are reduced to either
TRUE or FALSE.
Whenever you use a WHERE clause in a query, the WHERE test is performed against each row in the table that might satisfy the query’s conditions. If the WHERE clause is true, the row is returned. If the WHERE clause is false, the row is not returned.
Boolean Operators
There are four basic boolean operators: AND, OR, XOR, and NOT. Table 3.2 describes these operators.
| Operator | Result |
|---|---|
| x AND y | The result is TRUE if both x and y are TRUE. Otherwise the result is FALSE. |
| x OR y | The result is TRUE if either x or y is TRUE. Otherwise the result is FALSE. |
| x XOR y | The result is TRUE only if x and y have different values. Otherwise the result is FALSE. |
| NOT x | The result is the opposite of x, so it is TRUE if x is FALSE. The result is FALSE if x is TRUE. |
Transact-SQL does not use the XOR operator. The NOT operator is usually used to modify SQL keywords such as LIKE, BETWEEN, and IN, covered later today. The AND and OR operators can be used in a WHERE clause to specify additional restrictions on what rows are returned. The row will be returned whenever the entire WHERE clause resolves to TRUE.
Using NOT By Itself
You can use NOT in a where clause to get all the rows that do not satisfy the given condition. Here is the opposite of the last query example I gave earlier today:
select title, price from titles where not price > $13
This query gives all the titles in the titles table that are not greater than $13. Traditionally, the NOT operator is not used in this fashion in the real world, but it is still perfectly valid syntax.
| Previous | Table of Contents | Next |