Comparison operators
- Last Updated: May 12, 2026
- 2 minute read
- OpenAccess SDK
- Version 8.1
- Documentation
Comparison operators compare one expression to another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN. OpenAccess SDK considers the UNKNOWN result as FALSE. The following table describes the supported operators.
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; |
| IN | "Equal to any member of" test. Equivalent to "= ANY" | SELECT * FROM emp WHERE job IN ('CLERK','ANALYST');SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30); |
| NOT IN | Equivalent to "!=ALL". Evaluates to FALSE if the values matches with any member of the set. | SELECT * FROM emp WHERE sal NOT IN (SELECT sal FROM emp WHERE deptno = 30);SELECT * FROM emp WHERE job NOT IN ('CLERK', ANALYST'); |
| ANY SOME | Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows. | SELECT * FROM emp WHERE sal = ANY (SELECT sal FROM emp WHERE deptno = 30); |
| ALL | Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to TRUE if the query returns no rows. | SELECT * FROM emp WHERE sal >= ALL SELECT * FROM emp WHERE sal >= ALL (SELECT sal FROM emp WHERE empno < 0);In the previous example, if the emp table has no records with empno < 0, the result of sal >= ALL (subquery) is always TRUE if sal is not NULL. |
[NOT] BETWEEN x AND y |
[Not] greater than or equal to x and less than or equal to y. |
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000; |
| EXISTS | TRUE if a subquery returns at least one row. | SELECT ename, deptno FROM dept WHERE EXISTS (SELECT * FROM emp WHERE dept.deptno = emp.deptno); |
x [NOT] LIKE y[ESCAPE 'z'] |
TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character, except percent (%) and underscore (_) may follow ESCAPE. A wildcard character is treated as a literal if preceded by the character designated as the escape character |
SELECT * FROM emp WHERE ename LIKE 'A_C/%E%' ESCAPE '/'; |
| IS [NOT] NULL | Tests for nulls. This is the only operator to use for testing for nulls | SELECT ename, deptno FROM emp WHERE comm IS NULL; |