The NOT Operator
The NOT operator is a useful keyword that negates the value of a predicate. This can make it much simpler to express some conditions in a where clause, allowing the SELECT statement to be more readable. The operator is placed to the left of a predicate to reverse its meaning. The predicate can, if preferred, be contained within parentheses.
The statement below provides the opposite result to an earlier example. The predicate alone would cause the command the return all of the engineers earning £20 or less per hour. With the introduction of the NOT keyword, the statement returns only those engineers earning more than this amount.
SELECT
EngineerId AS Id,
EngineerName AS [Engineer Name],
HourlyRate AS Cost
FROM
Engineers
WHERE
NOT HourlyRate <= 20
The NOT keyword is sometimes included in an operator directly. An example of this is with the IN keyword. This can be negated using the NOT IN variant, as shown below.
SELECT DISTINCT
EngineerId
FROM
EngineerWorkingAreas
WHERE
AreaCode NOT IN ('HUM', 'NYO', 'SYO', 'WYO')
I mentioned earlier that predicates can return one of three possible values, these being true, false and unknown. When using NOT, an expression that evaluates to true becomes false. A false value becomes true. We will shortly see predicates that return unknown. It is important to understand that when the NOT operator is used with an unknown value, the result remains unknown.
Checking for Null Values
When a column value or an expression evaluates as NULL, this indicates that the value is undefined or empty. This is different from a value of zero or an empty string as every null value should be considered to be a different value. Any comparison of a value with NULL or of two NULL values returns a result of "unknown".
The result of this type of comparison has some effects that are unusual but that must be carefully anticipated. We can query the Customers table to demonstrate the potential pitfalls. This table contains twenty rows. Of these, only three represent business customers, so the BusinessName column in the other seventeen rows contains NULL.
If we execute a query that returns all of the rows where the business name is "Blue-Green ltd", we find that only one row matches the predicate.
SELECT
FirstName,
LastName,
BusinessName
FROM
Customers
WHERE
BusinessName = 'Blue-Green ltd'
Developers who are new to SQL Server may expect that changing this query to use the "not equal" operator, as shown below, would reverse the results. They may expect to see the other nineteen rows. However, as the comparison returns unknown for all comparisons with NULL, only two rows are included in the result set. This means that two apparently opposite queries do not return every row when combined.
SELECT FirstName,
LastName,
BusinessName
FROM
Customers
WHERE
BusinessName != 'Blue-Green ltd'
The same effect would be seen when using the NOT operator. If the predicate returns unknown because of a comparison with NULL, negating the result still returns an unknown value so the row is excluded from the results.
IS NULL and IS NOT NULL
If you wish to test if a column or expression is null, you can use the IS NULL keyword, rather than another comparison operator. To retrieve all of the customers that have no business name specified, execute the following statement:
SELECT
FirstName,
LastName,
BusinessName
FROM
Customers
WHERE
BusinessName IS NULL
The opposite of "IS NULL" is "IS NOT NULL". This evaluates to true only when the column value does not contain a null value. Therefore, you can find all of the customers that are associated with businesses by executing the following.
SELECT
FirstName,
LastName,
BusinessName
FROM
Customers
WHERE
BusinessName IS NOT NULL
Using Multiple Criteria
So far in this article we have only considered WHERE clauses with a single predicate. To expand the flexibility of the criteria in a query, you can add as many predicates as required to obtain the desired results. To do so, you use the AND and the OR operators.
3 August 2008