SQL PLUS Operators in Oracle

Read this Oracle Tutorial Point article to know more about the BETWEEN - AND and NOT BETWEEN - AND, IN and NOT IN, IS NULL or IS NOT NULL, LIKE and NOT LIKE operators in SQL in Oracle Database.

BETWEEN - AND and NOT BETWEEN - AND:
This order is used to show rows based on a range of values. The declared range is inclusive. The lower limit should be declared first.

Query for Between….And:

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE SAL BETWEEN 1000 AND 1500;

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE JOB BETWEEN 'MANAGER' AND 'CLERCK';

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE HIREDATE BETWEEN '19-APR-16' AND '01-OCT-16';

Query for Not Between…And:

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE SAL NOT BETWEEN 1000 AND 1500;

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE JOB NOT BETWEEN 'MANAGER' AND 'CLERCK';

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE HIREDATE NOT BETWEEN '19-APR-16' AND '01-OCT-16';

IN & NOT IN Operator:
The operator is used to test for values in a defined list. The operator can be used upon any data type.

Query using IN Operator:

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE NAME IN ('SUBBA','REDDY');

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE DNO IN (10,20);

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE HIREDATE IN ('19-APR-16' , '01-OCT-16');

Query using NOT IN Operator:

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE NAME NOT IN ('SUBBA','REDDY');

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE DNO NOT IN (10,20);

SQL> SELECT NAME,SAL,JOB FROM EMP WHERE HIREDATE NOT IN ('19-APR-16' , '01-OCT-16');

IS NULL & IS NOT NULL Operator:
The operator tests for NULL values. It is the only operator that is used to test for NULL’s. NULL means the value is unavailable, unassigned, unknown or inapplicable.

Query using NULL Operator:

SQL> SELECT NAME,DNO,COMM,JOB FROM EMP WHERE COMM IS NULL;

SQL> SELECT NAME,DNO,JOB FROM EMP WHERE MGR IS NULL;

Query using NOT NULL Operator:

SQL> SELECT NAME,DNO,COMM FROM EMP WHERE COMM NOT NULL;

SQL> SELECT NAME,DNO,JOB FROM EMP WHERE MGR IS NOT NULL;

LIKE & NOT LIKE Operator:
The LIKE Operator is used to search for a matching character. The character pattern matching operator is referred as a wild chad search. The accessible wild cards in Oracle are,

• / represent any one character at that position.
• % used to represent any sequence of ZERO or more characters.

The wild card symbol can be used in any combination with literal characters. For getting exact match for ‘%’ and ‘-’ the ESCAPE option has to be used, which is ‘/’ symbol with ESCAPE option.

Query using LIKE Operator:

SQL> SELECT NAME,JOB FROM EMP WHERE NAME LIKE 'S%;;

SQL> SELECT NAME,JOB FROM EMP WHERE NAME LIKE '_A%';

SQL> SELECT NAME,SAL FROM EMP WHERE NAME='SM%;;

SQL> SELECT * FROM DEPT WHERE DNAME LIKE '%\_%' ESCAPE '\';

Query using NOT LIKE Operator:

SQL> SELECT NAME,JOB FROM EMP WHERE NAME NOT LIKE 'S%';

SQL> SELECT NAME,JOB FROM EMP WHERE NAME NOT LIKE '_A%';

SQL> SELECT NAME,SAL FROM EMP WHERE 'SM%' NOT LIKE;

SQL> SELECT * FROM DEPT WHERE DNAME NOT LIKE '%\_%' ESCAPE '\';