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 | |