Oracle Operators

What are Oracle Operators?

Operators are nothing but a character or symbol that represents an action or process. An Operator is capable of manipulating operand items and returns a result. All oracle operators have been divided into several categories. They are listed below:

• Unary and Binary Operators
• Arithmetic Operators
• Comparison Operators
• Logical Operators
• Set operators
• Some other Built-In Operators
• User-defined operators

Top 7 Oracle Operators

Following are the different oracle operators with its examples.

1. Unary and Binary Operators

There are two general classes of operators. They are:

• UNARY: An operator that operates on only one operand is called the UNARY operator and the format is OPERATOR Operand.
• BINARY: An operator that operates on two operands is called BINARY operator and the format is operand  OPERATOR Operand.

2. Arithmetic Operators

Through a SELECT statement, Arithmetic operators can be used. Uses of Arithmetic operators:

• To present the data as per requirement.
• To perform calculations.
• To perform the mathematical operation.

What can be used by an Arithmetic expression?

• Table column names.
• Numerical values.
• Arithmetic operators.

Arithmetic expressions can be created on NUMBER or DATE data type using Arithmetic operators. The Arithmetic Operators cannot be used in the FROM clause.

The Arithmetic operators supported are:

Operators	Description
+	Addition
–	Subtraction
*	Multiplication
/	Division

Operator Precedence:

• [ * , / , + , – ]
• Multiplication and Division are having a higher priority than Addition and Subtraction.
• Arithmetic operators get evaluated from left to right they are the same priority.
• Parenthesis would be a good choice to prioritize the Arithmetic operator evaluation.
• + / _ operators can be operated as Unary or Binary operators.

Examples of Arithmetic Operators:

Query:

SELECT 2*3/3+1-1 FROM DUAL;

Output:

Query:

SELECT 2*4/ ((3+1)-1) FROM DUAL;

Output:

3. Comparison Operators

• The comparison operators are used in such conditions that compare one expression to another.
• The format of the operator in the WHERE clause is WHERE Expression OPERATOR VALUE.
• The different Comparison Operators are given below:

Symbol	Name
=	Equality Operator
<>, !=, ^=	Not Equality Operator
>	Greater Than Operator
<	Less Than Operator
>=	Greater Than or Equal to Operator
<=	Less Than or Equal to Operator

Examples of Comparison Operators:

Query:

SELECT Ename, Sal, Job FROM Emp WHERE Job='MANAGER';

Output:

Query:

SELECT Ename, Sal, Job FROM Emp WHERE Sal>=3000;

Output:

4. Logical Operators

Logical Operators combine the results of two-component conditions to produce a single result. Logical operators provided by ORACLE are:

Symbol	Name
AND	Logical Conjunction Operator
OR	Logical Disjunction Operator
NOT	Logical Negation Operator

AND Operator

• It returns TRUE if both or all component conditions are TRUE.
• It returns FALSE if either is FALSE, Else returns unknown.

Truth Table:

T = TRUE
F = FALSE

AND	TRUE	FALSE	NULL
TRUE	T	F	NULL
FALSE	F	F	F
NULL	NULL	F	NULL

OR Operator

• It returns TRUE if either of the components is TRUE.
• It returns FALSE if both or all component conditions are FALSE, else returns unknown.

Truth Table:

OR	TRUE	FALSE	NULL
TRUE	T	T	T
FALSE	T	F	NULL
NULL	T	NULL	NULL

NOT Operator

• It returns TRUE if the following condition is FALSE.
• It returns FALSE if the following condition is TRUE.
• If the condition is unknown, it returns unknown.

Truth Table:

NOT	TRUE	FALSE	NULL
NOT	F	T	NULL

Rules of Operator Precedence:

The default precedence order is,

• All comparison operators
• NOT operator
• AND operator
• OR operator

5. SET Operators

• SET operators are used to combine information about similar DATA type from one or more than one table. They can combine two or more queries into one result set.
• The data type of the corresponding columns in all the SELECT statements should be the same. The different types of SET operators are,
  1. UNION Operator
  2. UNION ALL Operator
  3. INTERSECT Operator
  4. MINUS Operator

Syntax:

< SQL Query>
{UNION | UNION ALL | INTERSECT | MINUS}
<SQL Query >;

• UNION: It combines the results of two SELECT statements into one result set and then eliminates any duplicate row(s) from the result set.
• UNION ALL: It combines the results of two SELECT statements into one result set, but it doesn’t eliminate any duplicate row(s) from the result set.
• INTERSECT: It returns only those rows that are returned by each of the two SELECT statements.
• MINUS: It takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

6. Other Built-In Operators

Following are a few more built-in operators.

CONCATENATION Operator

• The concatenation Operator links columns to another column, Arithmetic expressions or Constant values.
• Columns on either side of the operator are combined to make a single output column.
• The resultant column is treated as a CHARACTER expression.
• The concatenation operator is represented in ORACLE by a double pipe symbol (||).

Query:

SELECT Empno||' '|| ' belongs to '||Ename "Employee" FROM Emp;

Output:

BETWEEN … AND … Operator

• It is used to display rows based on a range of values.
• The declared range is inclusive.
• The lower limit should be declared first.
• The operator can be used upon any data type.
• The negation of the operator is NOT BETWEEN …AND….

Query:

SELECT Ename, Sal, Job FROM Emp WHERE Job BETWEEN 'MANAGER' AND 'SALESMAN';

Output:

IN Operator

• It is used to test for values in a specified list.
• The operator can be used upon any data type.
• The negation of this operator is NOT IN.

Query:

SELECT Ename, Sal, Job FROM Emp WHERE Job IN ‘SALESMAN’;

Output:

Query:

SELECT Ename, Sal, Job FROM Emp WHERE Job NOT IN ‘SALESMAN’;

Output:

IS NULL Operator

• This operator tests for NULL values.
• It is the only operator that can be used to test for NULL.
• The negation of this operator is IS NOT NULL.

Query:

SELECT Ename, Sal, Comm FROM Emp WHERE Comm IS NULL;

Output:

Query:

SELECT Ename, Sal, Comm FROM Emp WHERE Comm IS NOT NULL;

Output:

7. User-Defined Operators

• Like built-in operators, users can create them with the CREATE OPERATOR statement.
• The user-defined operator resides in the same namespaces as tables.
• User needs Operator privilege to create, drop, execute and modify an operator.

Syntax:

CREATE OR REPLACE OPERATOR SCHEMA.OPERATOR
BINDING_CLAUSE;

BINDING_CLAUSE SYNTAX:

BINDING
(parameter_type [, parameter_type]...)
RETURN return_type
[implementation_clause] using_function_clause
[, (parameter_type [, parameter_type]...)
RETURN return_type
[implementation_clause] using_function_clause]

Example #1:

CREATE OR REPLACE FUNCTION equality_func (a VARCHAR2, b VARCHAR2)
RETURN NUMBER
AS
BEGIN
IF a = b THEN RETURN 1;
ELSE RETURN 0;
END IF;
END;

Example #2:

CREATE OR REPLACE OPERATOR equality_operator
BINDING (VARCHAR2, VARCHAR2)
RETURN NUMBER
USING equality_func;

Conclusion

Oracle Operators is nothing but a character or symbol that represents an action or process. To manipulate individual data items and to return a result Oracle Operators can be used. An operator manipulates individual data items and returns a result. Operators are represented by special characters or by keywords.

Recommended Articles

This is a guide to Oracle Operators. Here we discuss the basic concept, and the top 7 oracle operators along with various examples and query implementation. You may also look at the following articles to learn more –

1. Implementation of INTERSECT in Oracle
2. Oracle UNION ALL | Syntax | Examples
3. Career in Oracle
4. Function in Oracle