Multiple Value Search (IN) Teradata

Previously, it was shown that adding a WHERE clause to the SELECT limited the returned rows to those that meet the criteria. The IN comparison is an alternative to using one or more OR comparisons on the same column in the WHERE clause of a SELECT statement and the IN comparison also makes it a bit easier to code:

SEL[ECT] <column-name>
[,<column-name> ]
FROM <table-name>
WHERE <column-name> IN (<value-list>)
;

The value list normally consists of multiple values separated by commas. When the value in the column being compared matches one of the values in the list, the row is returned. The following is an example for the alternative method when any one of the conditions is enough to satisfy the request using IN:

SELECT Last_Name
,Class_Code
,Grade_Pt
FROM Student_Table
WHERE Grade_Pt IN ( 2.0, 3.0, 4.0 ) ;
3 Row returned: Multiple Value Search (IN)

The use of multiple conditional checks as well as the IN can be used in the same SELECT request. Considerations include the use of AND for declaring that multiple conditions must all be true. Earlier, we saw the solution using a compound OR.

Using NOT IN

As seen earlier, sometimes the unwanted values are not known or it is easier to eliminate a few values than to specify all the values needed. When this is the case, it is a common practice to use the NOT IN as coded below. The next statement eliminates the rows that match and return those that do not match:

SELECT Last_Name
,Grade_Pt
FROM Student_Table
WHERE Grade_Pt NOT IN ( 2.0, 3.0, 4.0 ) ;
6 Rows returnedUsing NOT IN
Using NOT IN

The following SELECT is a better way to make sure that all rows are returned when using a NOT IN:

SELECT Last_Name
,Class_Code
,Grade_Pt
FROM Student_Table
WHERE Grade_Pt NOT IN ( 2.0, 3.0, 4.0 ) OR Grade_Pt IS NULL ;
7 Rows returnedSELECT is a better way when using not in

Notice that Johnson came back in this list and not the previous request using the NOT IN. You may be thinking that if the NULL reserved word is used within the IN list it will cover the situation. Unfortunately, you are forgetting that this comparison always returns an unknown. Therefore, the next request will NEVER return any rows:

SELECT Last_Name
,Class_Code
,Grade_Pt
FROM Student_Table
WHERE Grade_Pt NOT IN ( 2.0, 3.0, 4.0, NULL ) ;
No Rows found

Making this mistake will cause no rows to ever be returned. This is because every time the column is compared against the value list the NULL is an unknown and the Truth table shows that the NOT of an unknown is always an unknown for all rows. If you are not sure about this, do an EXPLAIN of the NOT IN and a subquery to see that the AMP step will actually be skipped when a NULL exists in the list. There are also extra AMP steps to compensate for this condition. It makes the SQL VERY inefficient.


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

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

Teradata Topics