Updated on 2024-09-19 GMT+08:00

Comparison Operators

Description

Comparison operators are used to compare two values and return true or false. Values of the numeric type and contents of the string type can be compared. For example, comparison operators can be used to check whether the numeric field num1 < num2 is true and whether str1 of the string type exists in string strs. For details, see Table 1.

Syntax

SELECT fieldname1 WHERE fieldname1 > fieldname2

Comparison Operator Statements

Table 1 Comparison operator statements

Statement

Description

Example

x = y

Equal to

SELECT num1 < num2

x <> y

Not equal to

SELECT num1 <> num2

x > y

Greater than

SELECT num1 > num2

x >= y

Greater than or equal to

SELECT num1 >= num2

x < y

Less than

SELECT num1 < num2

x <= y

Less than or equal to

SELECT num1 <= num2

x BETWEEN y AND z

Equivalent to x >= y AND x <= z

SELECT num1 BETWEEN num2 AND num3

x NOT BETWEEN y AND z

Equivalent to x < y OR x > z

SELECT num1 NOT BETWEEN num2 AND num3

x LIKE pattern

Returns true if x matches the SQL LIKE mode.

SELECT str1 LIKE '*'

x NOT LIKE pattern

Returns true if x does not match the SQL LIKE mode.

SELECT str1 NOT LIKE '*'

x IS NULL

Returns true if x is null or an empty string.

SELECT str1 IS NULL

x IS NOT NULL

Returns true if x is neither null nor an empty string.

SELECT str1 IS NOT NULL

x IN (values)

If x is one of the listed values, true is returned.

SELECT str1 IN ('testStr1', 'testStr2')

x NOT IN (values)

If x is not listed, true is returned.

SELECT str1 NOT IN ('testStr1', 'testStr2')

x IN (subquery)

Returns true if x is returned through a specified subquery.

SELECT str1

WHERE str2 IN

(SELECT DISTINCT str2 LIMIT 100)

x NOT IN (subquery)

If x is not returned through the specified subquery, true is returned.

SELECT str1

NOT IN

(SELECT str2

LIMIT 100)