|
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
|