EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Operators

Oracle Operators

Priya Pedamkar
Article byPriya Pedamkar

Updated March 24, 2023

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:

ADVERTISEMENT
Popular Course in this category
ORACLE DBA Database Management System Course Bundle - 2 Courses in 1

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • 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:

Oracle Operators 1-1

Query:

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

Output:

Oracle Operators 1-2

Note:  If any column value in an Arithmetic expression is NULL, the overall result is also NULL.

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:

Comparison

Query:

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

Output:

Oracle Operators 1-4

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
Note: The order can be controlled using parenthesis.

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:

CONCATENATION

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:

BETWEEN

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:

Oracle Operators 1-7

Query:

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

Output:

Oracle Operators 1-8

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:

Oracle Operators 1-9

Query:

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

Output:

Oracle Operators 1-10

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
ADVERTISEMENT
GOLANG Course Bundle - 6 Courses in 1
23+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
iOS DEVELOPER Course Bundle - 61 Courses in 1
147+ Hours of HD Videos
61 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JAVA SERVLET Course Bundle - 18 Courses in 1 | 6 Mock Tests
56+ Hours of HD Videos
18 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
RED HAT LINUX Course Bundle - 5 Courses in 1
28+ Hours of HD Videos
5 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW