SQL Comparison Operators

Updated on 2025-02-12 GMT+08:00

View PDF

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)