Oracle Special Operators

Special Operators are:
In(Not in)
Between(Not Between)
IS Null(Is not null)
Like(not like)

IN: IN operator is used to pick the values one by one from list of values. We can also use ‘OR’ operator in place of IN. But, IN operator performance is high compared to OR operator.

Syntax: SELECT* FROM table_name
WHERE column_name IN(list of values);

Examples:

SELECT * FROM emp WHERE deptno IN(10,30,50,70);
SELECT * FROM emp WHERE ename IN(‘SMITH’,’KING’);
SELECT * FROM emp WHERE deptno NOT IN(10,20);

BETWEEN: This is used to retrieve range of values.
Syntax: SELECT* FROM table_name WHERE column_name BETWEEN low value and high values.

Example:

SELECT * FROM emp WHERE sal BETWEEN 2000 and 5000;

LIKE: Like operator is used to retrieve string data based on character pattern. Like operator performance is very high compared to other searching function along with like operator we must use to wild characters. They are %,_(underscore)

Syntax:
SELECT * FROM table_name
WHERE column_name LIKE ‘Character pattern’;

Examples:
Query to display employees whose name starts with M.

SELECT*FROM emp
WHERE ename LIKE ‘M%’;

Query to display employees whose name contains L as second character.

SELECT*FROM emp
WHERE ename LIKE ‘_L%’;

Query to display employees who joined in February month

SELECT*FROM emp
WHERE HIREDATE LIKE ‘%FEB%’;

Concatenation Operator ||(Double piper): This is used to concatenate column data with literl strings.
Example:

SELECT ‘This is ‘|| ename from emp;

Powered by k2schools