15.00 - Comparison Operators - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

Comparison Operators

Purpose

Comparison operators, which are types of logical predicates (also called conditional expressions), test the truth of relations between expressions.

Comparison operators can appear in:

  • IF, WHILE, REPEAT, and CASE expressions in stored procedures
  • WHEN clauses in searched CASE expressions
  • WHERE, ON, and HAVING clauses to qualify or disqualify rows in a SELECT statement
  • CASE_N functions
  • Syntax

    where:

     

    Syntax Element …

    Specifies …

    operator

    one of the comparison operators.

    expression_1
    expression_2

    an SQL scalar expression, including derived period expressions.

    quantifier

    one of the following quantifier keywords:

  • ANY
  • SOME
  • ALL
  • For information, see “ANY / ALL / SOME Quantifiers” on page 931.

    literal

    one or more literal values. A literal may be any of the following:

  • Defined value
  • Macro parameter
  • Built-in value such as TIME, DATE, or USER
  • The comparison operation may compare an expression against a list of explicit literals.

    The data types of expression and literal must be compatible. If the data types of the operands differ, Teradata Database performs an implicit conversion from one type to another in some cases. For details, see “Implicit Type Conversion of Comparison Operands” on page 500.

    subquery

    an SQL SELECT statement.

    Using a subquery in a condition is restricted in certain cases.

    scalar_expression

    an expression to be evaluated in comparison with a second scalar_expression, including derived periods.

    Comparison operators do not support BLOB or CLOB type expressions. You can explicitly cast BLOBs to BYTE or VARBYTE and cast CLOBs to CHARACTER or VARCHAR, and use the result with comparison operators.

    An expression that results in a UDT data type can only be compared with another expression that results in the same UDT data type.

    comparison_operator

    the type of comparison to be evaluated for truth.

    For a list of the supported comparison operators, see “Supported Comparison Operators” on page 495.

    Results

    A logical expression that uses a comparison operator evaluates to TRUE, FALSE, or UNKNOWN.

    ANSI Compliance

    The following comparison operators are ANSI SQL:2011 compliant.

     

    The following comparison operators are Teradata extensions to the ANSI SQL:2011 standard. Their use is deprecated.

     

    Supported Comparison Operators

    Teradata Database supports the following comparison operators.

     

    ANSI Operator

    Teradata Extensions

    Function

    =

    EQ

    Tests for equality.

    <>

    ^=
    NE
    NOT=

    Tests for inequality.

    <

    LT

    Tests for less than.

    <=

    LE

    Tests for less than or equal.

    >

    GT

    Tests for greater than.

    >=

    GE

    Tests for greater than or equal.

    Comparison Operators Using Subqueries

    A subquery is a SELECT statement that returns values used to satisfy the comparison operation. The subquery must be enclosed in parentheses, and it does not end with a semicolon.

    The subquery must refer to at least one table. A table that is in the WHERE clause, but that is not referred to in any other parts of the subquery, is not applicable.

    A comparison operation may be used with a subquery whether or not a quantifier is used. If a quantifier is not used, however, then an error condition results if the subquery returns more than one value.

    If a subquery returns no values, and if a quantifier is not used, then the result of the comparison is false. Therefore, if the following form is used, the subquery must return either no values (in which case the comparison evaluates to false), or it returns one value.

       expression > (subquery)

    With the following form, subquery must select the same number of expressions as are specified in the expression list.

    The two expression lists are equal if each of the respective expressions are equal.

    If the respective expressions are not equal, then the result of the comparison is determined by comparing the first pair of expressions (from the left) for which the comparison is not true.

    A subquery in a comparison operation cannot specify a SELECT AND CONSUME statement.

    Example  

    The following statement uses the ALL quantifier to compare two expressions with the values returned from a subquery to find the employee(s) with the most years of experience in the group of employees having the highest salary:

       SELECT EmpNo, Name, DeptNo, JobTitle, Salary, YrsExp
       FROM Employee 
       WHERE (Salary,YrsExp) >= ALL 
        (SELECT Salary,YrsExp FROM Employee) ;