Sams Teach Yourself SQL in 24 Hours

Recommended Book

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Recommended Book

Recommended Book 

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Negating Conditions with the NOT Operator

Of all the conditions tested by the logical operators discussed here, there is a way to negate each one of these operators to change the condition's viewpoint.

The NOT operator reverses the meaning of the logical operator with which it is used. The NOT can be used with the following operators in the following methods:

• Not Equal
• NOT BETWEEN
• NOT IN
• NOT LIKE
• IS NOT NULL
• NOT EXISTS
• NOT UNIQUE

Each method is discussed in the following sections. First, let's look at how to test for inequality.

Not Equal

You have learned how to test for inequality using the <> operator. Inequality is worth mentioning in this section because to test for it, you are actually negating the equality operator. The following is a second method for testing inequality available in some SQL implementations:

In the second example, you can see that the exclamation mark is used to negate the equality comparison. The use of the exclamation mark is allowed in addition to the standard operator for inequality <> in some implementations.

NOT BETWEEN

The BETWEEN operator is negated as follows:

   

   SELECT *

   FROM PRODUCTS_TBL

   WHERE COST NOT BETWEEN 5.95 AND 14.5;

   
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
13         FALSE PARAFFIN TEETH            1.1
9          CANDY CORN                      1.35
6          PUMPKIN CANDY                   1.45
87         PLASTIC SPIDERS                 1.05
119        ASSORTED MASKS                  4.95
2345       OAK BOOKSHELF                  59.99

7 rows selected.

NOT IN

The IN operator is negated as NOT IN. All salaries in the following example that are not in the listed values, if any, are returned:

   

   SELECT *

   FROM PRODUCTS_TBL

   WHERE PROD_ID NOT IN ('13','9','87','119');

   
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
222        PLASTIC PUMPKIN 18 INCH         7.75
90         LIGHTED LANTERNS               14.5
15         ASSORTED COSTUMES              10
6          PUMPKIN CANDY                   1.45
1234       KEY CHAIN                       5.95
2345       OAK BOOKSHELF                  59.99

7 rows selected.

In this output, records were not displayed for the listed identifications after the NOT IN operator.

NOT LIKE

The LIKE, or wildcard, operator is negated as NOT LIKE. When NOT LIKE is used, only values that are not similar are returned. Examples include:

   

   SELECT PROD_DESC

   FROM PRODUCTS_TBL

   WHERE PROD_DESC NOT LIKE 'L%';

   
PROD_DESC
------------------------
WITCHES COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF

10 rows selected.

In this output, the product descriptions starting with the letter L were not displayed.

IS NOT NULL

The IS NULL operator is negated as IS NOT NULL to test for values that are not NULL.

   

   SELECT EMP_ID, LAST_NAME, FIRST_NAME, PAGER

   FROM EMPLOYEE_TBL

   WHERE PAGER IS NOT NULL;

   
EMP_ID    LAST_NAM FIRST_NA PAGER
--------- -------- -------- ----------
213764555 GLASS    BRANDON  3175709980
313782439 GLASS    JACOB    8887345678

2 rows selected.

NOT EXISTS

EXISTS is negated as NOT EXISTS.

   
   

   SELECT MAX(COST)

   FROM PRODUCTS_TBL

   WHERE NOT EXISTS ( SELECT COST
                   
   FROM PRODUCTS_TBL
                   
   WHERE COST > 100 );

   
 MAX(COST)
-----------
     59.99

The maximum cost for the table is displayed in this output because there were not any records that existed where the cost was greater than 100.

NOT UNIQUE

The UNIQUE operator is negated as NOT UNIQUE.