The comparison operators compare the operand on the left and on the right, and they return 1 or 0. The operands of comparison operations must have the same data type. Therefore, implicit type casting by the system or implicit type casting by the user is required.
The following table shows the comparison operators supported by CUBRID and their return values.
Comparison Operators Supported by CUBRID
Comparison Operator |
Description |
Predicate |
Return Value |
---|---|---|---|
= |
A general equal sign. It compares whether the values of the left and right operands are the same. Returns NULL if one or more operand is NULL. |
1=>2 |
0 |
<=> |
A NULL-safe equal sign. It compares whether the values of the left and right operands are the same including NULL. Returns 1 if both operands are NULL. |
1<=>2 |
0 |
<>, != |
The value of left operand is not equal to that of right operand. If any operand value is NULL, NULL is returned. |
1<>2 |
1 |
> |
The value of left operand is greater than that of right operand. If any operand value is NULL, NULL is returned. |
1>2 |
0 |
< |
The value of left operand is less than that of right operand. If any operand value is NULL, NULL is returned. |
1<2 |
1 |
>= |
The value of left operand is greater than or equal to that of right operand. If any operand value is NULL, NULL is returned. |
1>=2 |
0 |
<= |
The value of left operand is less than or equal to that of right operand. If any operand value is NULL, NULL is returned. |
1<=2 |
1 |
IS boolean_value |
Compares whether the value of the left operand is the same as boolean value of the right. The boolean value may be TRUE, FALSE (or NULL). |
1 IS FALSE |
0 |
IS NOT boolean_value |
Compares whether the value of the left operand is the same as boolean value of the right. The boolean value may be TRUE, FALSE (or NULL). |
1 IS NOT FALSE |
1 |
expression comparison_operator expression
expression :
• bit string
• character string
• numeric value
• date-time value
• collection value
• NULL
comparison_operator :
=
| <=>
| <>
| !=
| >
| <
| >=
| <=
expression IS [NOT] boolean_value
expression :
• bit string
• character string
• numeric value
• date-time value
• collection value
• NULL
boolean_value :
< UNKNOWN | NULL>
| TRUE
| FALSE
EVALUATE (1 <> 0); -- 1 is displayed because it is TRUE.
EVALUATE (1 != 0); -- 1 is displayed because it is TRUE.
EVALUATE (0.01 = '0.01'); -- An error occurs because a numeric data type is compared with a character string type.
EVALUATE (1 = NULL); -- NULL is displayed.
EVALUATE (1 <=> NULL); -- 0 is displayed because it is FALSE.
EVALUATE (1.000 = 1); -- 1 is displayed because it is TRUE.
EVALUATE ('cubrid' = 'CUBRID'); -- 0 is displayed because it is case sensitive.
EVALUATE ('cubrid' = 'cubrid'); -- 1 is displayed because it is TRUE.
EVALUATE (SYSTIMESTAMP = CAST(SYSDATETIME AS TIMESTAMP)); -- 1 is displayed after casting the type explicitly and then performing comparison operator.
EVALUATE (SYSTIMESTAMP = SYSDATETIME)); 0 is displayed after casting the type implicitly and then performing comparison operator.
EVALUATE (SYSTIMESTAMP <> NULL); -- NULL is returned without performing comparison operator.
EVALUATE (SYSTIMESTAMP IS NOT NULL); -- 1 is returned because it is not NULL.