Teradata Logical Operators



Below are the logical operators which are used in Teradata.

Operator Meaning
= Equal to
<>  Not Equal to
Greater than
Less than
> = Greater than or Equal to
< = Less than or Equal to
AND Combine multiple conditions. Evaluates to true only if all the conditions are met.
OR Combine multiple condition. Evaluates to true if either of the condition is met.
NOT Reverse the meaning of the condition
BETWEEN If the values with range
IN If the values in <expression>
NOT IN If the values not in <expression>
IS NULL If value is NULL
IS NOT NULL If value is NOT NULL

Teradata AND Condition

AND is used to connect different LOGICAL operator. All the condition must be met for a specific row to be qualified.

 

Teradata AND Example

Consider the below student table.

roll_no first_name last_name dept_no dept_name DOB
101 Amal Roy 1 Computer Science 10/12/1990
104 Surajit Saha 3 Physics 8/17/1989
102 Sagar Sharma 2 Information Technology 9/25/1991
105 Bidyut Dey 5 Electrical 12/5/1985
103 Aditya Roy 7 Electronics 7/12/1991

The following example fetches the students whose first name is Aditya and Last name is Roy.

sel * from teradatapoint.student
 where first_name='Aditya'
 AND last_name='Roy';

 

Below is the output of above query.

*** Query completed. One row found. 6 columns returned.
  *** Total elapsed time was 1 second.

    roll_no  first_name  last_name   dept_no  dept_name          DOB
         -----------  ----------  ----------  -------  ----------  ----------
         103  Aditya      Roy               6  Electronic  1991-07-12

 

Teradata OR Condition

OR condition is used to connect between different LOGICAL operators. Only, one of the condition need to be true for a row to be qualified.

 

Teradata OR Example

The following examples fetches the students whose first name is Aditya OR Last name is Roy.

sel * from teradatapoint.student
 where first_name='Aditya'
 OR last_name='Roy';

 

Below is the output of the above query-

*** Query completed. 2 rows found. 6 columns returned.
  *** Total elapsed time was 1 second.

    roll_no  first_name  last_name   dept_no  dept_name          DOB
 -----------  ----------  ----------  -------  ----------  ----------
         101  Amal        Roy               2  Computer Science   1990-10-12
         103  Aditya      Roy               6  Electronic  1991-07-12

 

Teradata BETWEEN Condition

The BETWEEN condition is used to filter the selected rows for a specific range.

 

Teradata BETWEEN Example

The following example filters the students if the Roll_no between 102 and 104.

sel * from teradatapoint.student where roll_no between 102 and 104;

 

Below is the output of above query-

*** Query completed. 3 rows found. 6 columns returned.

 *** Total elapsed time was 1 second.

    roll_no  first_name  last_name   dept_no  dept_name          DOB
 -----------  ----------  ----------  -------  ----------  ----------
         104  Surajit     Saha              8  Physics     1989-08-17
         102  Sagar       Sharma            2  Information Technology  1991-09-25
         103  Aditya      Roy               6  Electronic  1991-07-12

 

Teradata IN Condition

IN condition is used to check the value against a given list of values.

 

Teradata IN Example

The following example fetches records with roll_no 104 and 105.

sel * from teradatapoint.student where roll_no IN (104,105);

 

Below is the result of above query-

*** Query completed. 2 rows found. 6 columns returned.
  *** Total elapsed time was 1 second.

    roll_no  first_name  last_name   dept_no  dept_name          DOB
 -----------  ----------  ----------  -------  ----------  ----------
         104  Surajit     Saha              8  Physics     1989-08-17
         105  Bidyut      Dey               7  Electrical  1985-12-05

 

Teradata NOT IN Condition

NOT IN command reverses the result of IN command. It will return the result which are NOT IN the given list of values.

 

Teradata NOT IN Example

The following example returns the records except the roll_no 104 and 105.

sel * from teradatapoint.student where roll_no NOT IN (104,105);

 

Below is the output of the above query-

 *** Query completed. 3 rows found. 6 columns returned.
  *** Total elapsed time was 1 second.

    roll_no  first_name  last_name   dept_no  dept_name          DOB
 -----------  ----------  ----------  -------  ----------  ----------
         101  Amal        Roy               2  Computer S  1990-10-12
         102  Sagar       Sharma            2  Information Technology  1991-09-25
         103  Aditya      Roy               6  Electronic  1991-07-12