Oracle OPERATORS

Oracle provides operators to be used with data in order to perform some related action and return a result. An operator can act on a single operand (Unary Operators) or on two operands (Binary Operators).

The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL.

Oracle provides following kinds of operators:

1. Arithmetic Operators
2. Concatenation Operators
3. Comparison Operators
4. Logical Operators
5. Hierarchical Operators
6. Set Operators

Arithmetic Operators

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value.

The available Arithmetic Operators are: +, -, *, and /.

Examples

The following are some examples.

SELECT sal + comm FROM emp
  WHERE SYSDATE - hiredate
  > 365;

UPDATE emp
  SET sal = sal * 1.1;

c := a + b;

Concatenation Operator

Concatenation Operator operate on two string operands to join them into a single string.

The available Concatenation Operator is ||.

Examples

SELECT 'Name is ' || ename
   FROM emp;

SELECT col1||col2||col3||col4 "Concatenation"
        FROM tab1;

Comparison Operators

Comparison Operators are used to build a comparable condition between two terms, thereby returning a logical result as TRUE, FALSE, or NULL.

The available Comparison operators are: =, <, >, <=, >=, (NOT)IN, (NOT) LIKE, (NOT) BETWEEN, (NOT) EXISTS, and IS (NOT) NULL.

Examples

The following are some examples.

=	Equality test.	SELECT * FROM emp WHERE sal = 1500;
>	Greater than test	SELECT * FROM emp WHERE sal > 1500;
<=	Less than or equal to tests	SELECT * FROM emp WHERE sal <= 1500;
NOT IN	Evaluates to FALSE if any member of the set is NULL.	SELECT * FROM emp WHERE job NOT IN ('CLERK', 'ANALYST');
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);

Logical Operators

Logical Operators operate on two operands and return a BOOLEAN output.

The available logical operators are: AND, OR, and NOT.

AND Operator

Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN.

SELECT * FROM emp
  WHERE job = 'CLERK'
  AND deptno = 10;

OR Operator

Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.

SELECT * FROM emp
  WHERE job = 'CLERK'
  OR deptno = 10;

NOT Operator

Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.

SELECT * FROM emp
  WHERE NOT (sal BETWEEN 1000 AND 2000);

Hierarchical Operators

Hierarchical Operators are used only in hierarchical queries.

The available Hierarchical Operators are: PRIOR and CONNECT_BY_ROOT.

Example

SELECT Lpad(ename,Length(ename) + LEVEL * 10 - 10,'-') 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr;

KING
----------JONES
--------------------SCOTT
------------------------------ADAMS
--------------------FORD
------------------------------SMITH
----------BLAKE
--------------------ALLEN
--------------------WARD
--------------------MARTIN
--------------------TURNER
--------------------JAMES
----------CLARK
--------------------MILLER

Set Operators

Set Operators are used to compound multiple queries and return the combined result set.

The available SET Operators are: UNION, UNION ALL, MINUS, and INTERSECT.

UNION Example

The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_DATE and TO_NUMBER functions) when columns do not exist in one or the other table:

SELECT part, partnum, to_date(null) date_in
    FROM orders_list1
UNION
SELECT part, to_number(null), date_in
    FROM orders_list2;

UNION ALL Example

The following statement combines the results with the UNION ALL operator, which does not eliminate duplicate selected rows:

SELECT part 
    FROM orders_list1 
UNION ALL 
SELECT part 
    FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 
FUEL PUMP 
TAILPIPE 
CRANKSHAFT 
TAILPIPE 
TAILPIPE 

MINUS Example

The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

SELECT part FROM orders_list1 
MINUS 
SELECT part FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 

INTERSECT Example

The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT part FROM orders_list1 
INTERSECT 
SELECT part FROM orders_list2;

PART 
---------- 
TAILPIPE