EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Operators
Secondary Sidebar
Oracle Tutorial
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Oracle Operators

By Priya PedamkarPriya Pedamkar

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,992 ratings)

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
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

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

EDUCBA Login

Forgot Password?

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

EDUCBA

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

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

Let’s Get Started

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

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