Using NOT in SQL Comparisons Teradata

It can be fairly straightforward to request exactly which rows are needed. However, sometimes rows are needed that contain any value other than a specific value. When this is the case, it might be easier to write the SELECT to find what is not needed instead of what is needed. Then convert it to return everything else. This might be the situation when there are 100 potential values stored in the database table and 99 of them are needed. So, it is easier to eliminate the one value than it is to specifically list the desired 99 different values individually.

Either of the next two SELECT formats can be used to accomplish the elimination of the one value:

This second version of the SELECT is normally used when compound conditions are required. This is because it is usually easier to code the SELECT to get what is not wanted and then to enclose the entire set of comparisons in parentheses and put one NOT in front of it. Otherwise, with a single comparison, it is easier to put NOT in front of the comparison operator without requiring the use of parentheses.

The next SELECT uses the NOT with an AND comparison to display seniors and lower classmen with grade points less than 3.0:

6 Rows returned

Using NOT in SQL Comparisons

Without using the above technique of a single NOT, it is necessary to change every individual comparison. The following SELECT shows this approach, notice the other change necessary below, NOT AND is an OR.

Since you cannot have conditions like: NOT >= and NOT <>, they must be converted to < (not < and not =) and = (not, not =). It returns the same 5 rows, but also notice that the AND is now an OR:

6 Rows returned

Using NOT in SQL Comparisons

Using NOT in SQL Comparisons

Using NOT in SQL Comparisons

To maintain the integrity of the statement, all portions of the WHERE must be changed, including AND, as well as OR. The following two SELECT statements illustrate the same concept when using an OR:

1 Row returnedtwo SELECT statements illustrate the same concept when using an OR



In the earlier Truth table, the NULL value returned an unknown when checked with a comparison operator. When looking for specific conditions, an unknown was functionally equivalent to a false, but really it is an unknown.

These two Truth tables can be used together as a tool when mixing AND and OR together in the WHERE clause along with NOT.

two Truth tables

two Truth tables

There is an issue associated with using NOT. When a NOT is done on a true condition, the result is a false. Likewise, the NOT of a false is a true. However, when a NOT is done with an unknown, the result is still an unknown. Whenever a NULL appears in the data for any of the columns being compared, the row will never be returned and the answer set will not be what is expected.

It takes some practice and consideration when using NOT. Another area where care must be taken is when allowing NULL values to be stored in one or both of the columns. As mentioned earlier, previous versions of Teradata had no concept of "unknown" and if a compare didn't result in a true, it was false. With the emphasis on ANSI compatibility the unknown was introduced.

If NULL values are allowed and there is potential for the NULL to impact the final outcome of compound tests, additional tests are required to eliminate them. One way to eliminate this concern is to never allow a NULL value in any columns. However, this may not be appropriate and it will require more storage space because a NULL can be compressed. Therefore, when a NULL is allowed, the SQL needs to simply check for a NULL.

Therefore, using the expression IS NOT NULL is a good technique when NULL is allowed in a column and the NOT is used with a single or a compound comparison. This does require another comparison and could be written as:

7 Rows returnedUsing NOT in SQL Comparisons

Notice that Johnson came back this time and did not appear previously because of the NULL values. Later in this, the COALESCE will be explored as another way to eliminate NULL values directly in the SQL instead of in the database.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics