Compound Comparisons ( AND / OR ) Teradata

Many times a single comparison is not sufficient to specify the desired rows. To add more functionality to the WHERE it is common to use more than one comparison. The multiple condition checks and column names are not separated by a comma, like column names. Instead, they must be connected using a logical operator. The following is the syntax for using the AND logical operator:

SEL[ECT] <column-name>
[,<column-name> ]
FROM <table-name>
WHERE <column-name> <comparison> <data-value> { AND | OR }
<column-name> <comparison> <data-value>
;

Notice that the column name is listed for each comparison separated by a logical operator; this will be true even when it is the same column being compared twice. The AND signifies that each individual comparison on both sides of the AND must be true. The final result of the comparison must be TRUE for a row to be returned.

Compound Comparisons ( AND / OR )

When using AND, different columns must be used because a single column can never contain more than a single data value.

Therefore, it does not make good sense to issue the next SELECT using an AND on the same column because no rows will ever be returned.

SELECT Last_Name
,First_Name
FROM Student_Table WHERE Grade_Pt = 3.0 AND Grade_Pt = 4.0;
No rows found

The above SELECT will never return any rows. It is impossible for a column to contain more than one value. No student has a 3.0 grade average AND a 4.0 average. They might have one or the other, but not both. It might contain one or the other, but never both at the same time. The AND operator indicates both must be TRUE and should never be used between two comparisons on the same column.

By substituting an OR logical operator for the previous AND, rows will now be returned. The following is the syntax for using OR:

SELECT Student_ID
,Last_Name
,First_Name
,Grade_Pt
FROM Student_Table
WHERE Grade_Pt = 3.0 OR Grade_Pt = 4.0 ;
2 Rows returnedsyntax for using OR

The OR signifies that only one of the comparisons on each side of the OR needs to be true for the entire test to result in a true and the row to be selected.

Compound Comparisons ( AND / OR )

When using the OR, the same column or different column names may be used. In this case, it makes sense to use the same column because a row is returned when a column contains either of the specified values as opposed to both values as seen with AND.

It is perfectly legal and common practice to combine the AND with the OR in a single SELECT statement.

The next SELECT contains both an AND as well as an OR:

SELECT *
FROM Student_Table
WHERE Grade_Pt = 3.0 OR Grade_Pt = 4.0 AND Class_Code = 'FR' ;
2 Rows returnedSELECT contains both an AND as well as an OR

At first glance, it appears that the comparison worked correctly. However, upon closer evaluation it is incorrect because Phillips is a senior and not a freshman. When mixing AND with OR in the same WHERE clause, it is important to know that the AND is evaluated first. The previous SELECT actually returns all rows with a grade point of 3.0. Hence, Phillips was returned. The second comparison returned Thomas with a grade point of 4.0 and a class code of ‘FR’.

When it is necessary for the OR to be evaluated before the AND the use of parentheses changes the priority of evaluation. A different result is seen when doing the OR first. Here is how the statement should be written:

SELECT Last_Name
,Class_Code
,Grade_Pt
FROM Student_Table
WHERE ( Grade_Pt = 3.0 OR Grade_Pt = 4.0 ) AND Class_Code = 'FR' ;
1 Row returnedhow the statement should be written

Now, only Thomas is returned and the output is correct.



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