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.

The >= operator in the first statement implicitly invokes the greaterthanorequal( ) operator function:

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 less_than( ), greater_than( ), 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 less_than( ), greater_than( ), 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.