Comparison Operators

Description

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
1=NULL

0
NULL

<=>

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
1<=> NULL

0
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

Syntax 1

expression  comparison_operator  expression

 

expression :

bit string

character string

numeric value

date-time value

collection value

NULL

 

comparison_operator :

=

| <=>

| <>

| !=

| >

| <

| >=

| <=

Syntax 2

expression IS [NOT] boolean_value

 

expression :

bit string

character string

numeric value

date-time value

collection value

NULL

 

boolean_value :

< UNKNOWN | NULL>

| TRUE

| FALSE

Example

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.