Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • Hour 7. Introduction to the Database Query
  • Hour 8. Using Operators to Categorize Data
    • What Is an Operator in SQL?
    • Comparison Operators
    • Logical Operators
    • Conjunctive Operators
    • Negating Conditions with the NOT Operator
    • Arithmetic Operators
    • Summary
    • Q&A
    • Workshop
  • Hour 9. Summarizing Data Results from a Query
  • Hour 10. Sorting and Grouping Data
  • Hour 11. Restructuring the Appearance of Data
  • Hour 12. Understanding Dates and Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

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.

Share This