Using Quantifiers Versus IN Teradata

There is another alternative to using the IN. Quantifiers can be used to allow for normal comparison operators without requiring compound conditional checks. The following is equivalent to an IN:

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

This next request uses ANY instead of IN:

SELECT Last_Name
,Class_Code
,Grade_Pt
FROM Student_Table
WHERE Grade_Pt = ANY ( 2.0, 3.0, 4.0 ) ;
3 Row returnedUsing Quantifiers Versus IN

Using a qualifier, the equivalent to a NOT IN is:

[SELECT] <column-name>
[,<column-name> ] FROM <table-name>
WHERE <column-name> NOT = ALL (<value-list>)
;

Notice that like adding a NOT to the compound condition, all elements need to be changed here as well. To reverse the = ANY, it becomes NOT = ALL. This is important, because the NOT = ANY selects all the rows except those containing a NULL. The reason is that as soon as a value is not equal to any one of the values in the list, it is returned.

The following SELECT is converted from an earlier NOT IN:

SELECT Last_Name
,Grade_Pt
FROM Student_Table
WHERE Grade_Pt NOT = ALL ( 2.0, 3.0, 4.0 )
;
6 Rows returnedSELECT is converted from an earlier NOT IN
SELECT is converted from an earlier NOT IN

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