|
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
(1400, 3000);
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;
|