Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition

By Ronald R. Plew, Ryan K. Stephens

Table of Contents
Hour 8.  Using Operators to Categorize Data

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 :

Example

Meaning

WHERE SALARY <> '20000'

SALARY does not equal 20000

WHERE SALARY != '20000'

SALARY does not equal 20000

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.

Check your particular implementation for the use of the exclamation mark to negate the inequality operator. The other operators mentioned are most always the same if compared between different SQL implementations.

NOT BETWEEN

The BETWEEN operator is negated as follows :

Example

Meaning

WHERE Salary NOT BETWEEN '20000' AND '30000'

The value for SALARY cannot fall between 20000 and 30000, to include the values 20000 and 30000

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.

Remember that BETWEEN is inclusive; therefore, in the previous example, any rows that equal 5.95 or 14.50 are not included in the query results.

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:

Example

Meaning

WHERE SALARY NOT IN ('20000', '30000', '40000')

The SALARY cannot be equal to any of the given values for action to be taken

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:

Example

Meaning

WHERE SALARY NOT LIKE '200%'

Finds any values that do not start with 200

WHERE SALARY NOT LIKE '%200%'

Finds any values that do not have 200 in any position

WHERE SALARY NOT LIKE '_00%'

Finds any values that have 00 starting in the second position

WHERE SALARY NOT LIKE '2_%_%'

Does not find any values that start with 2 and have a length of 3 or greater

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.

Example

Meaning

WHERE SALARY IS NOT NULL

Only NOT NULL rows are returned

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.

Example

Meaning

WHERE NOT EXISTS (SELECT EMP_ID FROM EMPLOYEE_TBL WHERE EMP_ID = '333333333'

Searching to see whether the EMP_ID 3333333333 is not in the EMPLOYEE_TBL

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.

Example

Meaning

WHERE NOT UNIQUE SALARY (SELECT FROM EMPLOYEE_TBL)

Testing to see whether there are salaries in the table that are not UNIQUE


Team-Fly    
Top
 

Категории