Comparison operators compare one expression to another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN (if one of the operands is NULL). The driver considers the UNKNOWN result as FALSE.

The following table lists the supported comparison operators.

Table 1. Comparison Operators
Operator Purpose Example
= Equality test. SELECT * FROM emp WHERE sal = 1500
!=<> Inequality test. SELECT * FROM emp WHERE sal != 1500
>< "Greater than" and "less than" tests. SELECT * FROM emp WHERE sal > 1500 SELECT * FROM emp WHERE sal < 1500
>=<= "Greater than or equal to" and "less than or equal to" tests. SELECT * FROM emp WHERE sal >= 1500 SELECT * FROM emp WHERE sal <= 1500
LIKE % and _ wildcards can be used to search for a pattern in a column. The percent sign denotes zero, one, or multiple characters, while the underscore denotes a single character. The right-hand side of a LIKE expression must evaluate to a string or binary. SELECT * FROM emp WHERE ENAME LIKE 'J%'
ESCAPE clause in LIKE operator

LIKE 'pattern string' ESCAPE 'c'

 The Escape clause is supported in the LIKE predicate to indicate the escape character. Escape characters are used in the pattern string to indicate that any wildcard character that is after the escape character in the pattern string should be treated as a regular character.

The default escape character is backslash (\).

 SELECT * FROM emp WHERE ENAME LIKE 'J%\_%' ESCAPE '\'

This matches all records with names that start with letter 'J' and have the '_' character in them.

SELECT * FROM emp WHERE ENAME LIKE 'JOE\_JOHN' ESCAPE '\'

This matches only records with name 'JOE_JOHN'.
[NOT] IN "Equal to any member of" test. SELECT * FROM emp WHERE job IN ('CLERK','ANALYST') SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30)
[NOT] BETWEEN x AND y "Greater than or equal to x" and "less than or equal to y." SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000
EXISTS Tests for existence of rows in a subquery. SELECT empno, ename, deptno FROM emp e WHERE EXISTS (SELECT deptno FROM dept WHERE e.deptno = dept.deptno)
IS [NOT] NULL Tests whether the value of the column or expression is NULL. SELECT * FROM emp WHERE ename IS NOT NULL SELECT * FROM emp WHERE ename IS NULL