Special comparison operator used in where Clause
a. between. ...and...It gives range among two values (inclusive)
Example:
SELECT ENAME, SAL FROM EMP
WHERE SAL BETWEEN 1000 AND 1500;
b. In (list): match any of a list of values
Example:
SELECT EMPNO, ENAME, SAL, MGR FROM EMP
WHERE MGR IN (7902, 7566, 7788);
7902, 7566, and 7788 are Employee numbers c. Like: match a character pattern
- % indicates zero or many characters
- Like operator is used only with char and Varchar2 to match a pattern
- _ indicates one character
- Combination of % and_can also be used.
Example:
(I) List the names of employees whose name begins with 's' SELECT ENAME FROM EMP
WHERE ENAME LIKE 'S%';
(II) List the ename finished with's'
SELECT ENAME FROM EMP WHERE ENAME LIKE '%S';
(III) List ename having I as a second character
SELECT ENAME FROM EMP WHERE ENAME LIKE '_I%';
d. Is null operator
Example:
to search employee whose manage-id is not specified
SELECT ENAME, MGR FROM EMP WHERE MGR IS NULL;