Using Operator Functions in Place of Relational Operators

Each relational operator is bound to a particular operator function, as the table shows. The operator function accepts two values and returns a boolean value of true, false, or unknown.

Relational Operator

Associated Operator Function

<

lessthan( )

<=

lessthanorequal( )

>

greaterthan( )

>=

greaterthanorequal( )

= or ==

equal( )

<> or !=

notequal( )

Connecting two expressions with a relational operator is equivalent to invoking the operator function on the expressions. For example, the next two statements both select orders with a shipping charge of $18.00 or more.

SELECT order_num FROM orders 
   WHERE ship_charge >= 18.00;

SELECT order_num FROM orders
   WHERE greaterthanorequal(ship_charge, 18.00);

The database server provides the operator functions associated with the relational operators for all built-in data types. When you develop a user-defined data type, you must define the operator functions for that type for users to be able to use the relational operator on the type.

If you define lessthan( ), greaterthan( ), and the other operator functions for a user-defined type, then you should also define compare( ). Similarly, if you define compare( ), then you should also define lessthan( ), greaterthan( ), and the other operator functions. All of these functions must be defined in a consistent manner, to avoid the possibility of incorrect query results when UDT values are compared in the WHERE clause of a SELECT.