Operators in Oracle

Operators in oracle:-

There are different types of operators available in oracle:-

·         Set operator

·         Arithmetic operator

·         Character operator

·         Comparison operator

·         Logical operator

·         Other operator

1)    Set operator:-

Set operators combine the results of two queries into a single result.
There are mainly 4 types of Set Operators listed as below:-

Operator	What it does
UNION	This combines the results of two queries and returns the set of distinct rows returned by either query
UNION ALL	This combines the results of two queries and returns all rows returned by either query, including duplicates
MINUS	This combines the results of two queries and returns the distinct rows that were in the first query, but not in the second
INTERSECT	This combines the results of two queries and returns the set of distinct rows returned by both queries

1)    UNION:-

Union operator retrieves records from both queries by eliminating duplicate records. Query should have common numbers of columns in each query OR if required, will have to specify dummy column and convert the data type of the column to null which is/are not present in one table and exist in other table. We will see the examples on how to achieve this.
For verifying the result of Set operator we will create two tables called TEST and TEST_1.
TEST table having 7 records:-

TEST_1 table having 10 records:-

Above we have created TEST and TEST_1 table which are having 7 and 10 records respectively. All the records are unique in both the tables so it should retrieve all the records:-





Now let’s try to use UNION Operator:-

17 records are retrieved from the above query. Now insert a duplicate record in one of the tables and again execute a select query. We will inset the record in TEST table which is a duplicate record:-

Now again execute select query and see the result.

After inserting duplicate record its shows 17 records only. I.e. duplicate records are not shown.

2)    UNION ALL:-

UNION ALL operator retrieves all the records from both the queries. It does not worry about duplicate records and hence it is faster than UNION operator.
For example we will use the TEST and TEST_1 tables.

TEST table is having 8 records and TEST_1 table is having 10 records.
Execute the select query to use UNION ALL operator:-

UNION ALL operator shows 18 records in total.


Question: - I need to select 4 columns from one table and 3 columns from second table, how can I use UNION or UNION ALL operator to retrieve the desired output?
Answer: - To achieve the desire output you need to specify the dummy column and convert the data type of the dummy column to the data type of the second table’s column. Let’s try the example.
Data in the tables are as follows. TEST_1 table does not have deptno column:-

Now by adding dummy deptno column, try to retrieve the records as shown below:-

It shows blank/null records for the rows from the TEST_1 table.

Note:-

You need to convert both the column in char data type otherwise it will through error

ORA-01790: expression must have same data type as corresponding expression

3)    MINUS:-

MINUS operator returns only distinct rows return by the first query and not by the second query.
In other words MINUS operator retrieves all the records which are present in the first query and not in second query.
Example, we have 1 to 10 empno in the TEST_1 and 1,2,3,4,6,8,9 and 10(excluding 5 and 7) in TEST table. So we can find it out by the query using MINUS operator.

If we do it by reveres then we will not get any row since all the empno in the TEST table are present in the TEST_1 table.
 

4)    INTERSECT :-

The SQL INTERSECT query allows you to return the results of 2 or more select queries. Intersect operator return only those records which are return by both the queries. It finds out matching records from both the queries (tables). It the record is available in one query and not in other, it will omit the record.
We will see the example:-
First query both the tables. Now try to find out which are the records that are matching in both the tables.

If you try to find out on empno only, result will be changed since there are many employee numbers which are same in both the tables. So if we query on employee number only then the result would be like-:

2)    ARITHMETIC OPERATOR:-

Arithmetic operators manipulate numeric operands.

Below is the list of arithmetic operators.

Arithmetic Operation	Arithmetic Operation Explanation
+	Used for addition in SQL
*	Used for Multiplication in SQL
/	Used for division in SQL
-	Used for Subtraction in SQL

We will see the example of arithmetic operator with the help of DUAL table of oracle which is virtual table having 1 column and 1 row.










We will see the example of these operators with respect to the table. We can use these operators to show increase in the salary like:-

3)    Character operator:-

Character operators are used in expressions to manipulate character strings.
|| (Concatenate) is a character operator.
We can use concatenate operator to combine two character strings.
Below are the various types of example to use character operator:-

4)    Comparison operator:-

Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or NULL.

Comparison operators are used to compare one value or expression to another.

All comparison operators return a Boolean result.

Operator	Usage
=	Equality operator.
<>,!=,~=	Inequality operator.
<	Less than operator.
>	Greater than operator.
<=	Less than or equal to operator.
>=	Greater than or equal to operator.
LIKE	Pattern-matching operator.
BETWEEN	Checks to see if a value lies within a specified range of values.
IN	Checks to see if a value lies within a specified list of values.
IS NULL	Checks to see if a value is null.
ALL	Compares a value with every value in a list or returned by a query.

We would be using TEST and TEST_1 table to see the examples of the above operators:-

Below are some examples of the comparison operators:-

5)    Logical operator:-

There are three Logical Operators namely, AND, OR and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When we retrieve data using a SELECT statement, we can use logical operators in the WHERE clause, which allows you to combine more than one condition.

Operator	What it does
NOT	Reverses the meaning of another logical expression's result
OR	Logical OR—True if any are true, false else
AND	Logical AND—True if all are true, else false

Below are some examples of the Logical operators:-

6)    Other operator :-

We have (+) operator in the category of other operator.
(+) indicates that the preceding column is the outer join column in a join.
(+) sign in the where condition in join indicates that, query should retrieve only matching rows from the table which consists (+) sign after the column name and retrieve all the records from the table which does not contain (+) sign after the column name.
Structure and data of the TEST and TEST_1 TABLE.
Output for (+) operator:-


Above we have tried to cover all the operators which are available in oracle. There are more operators we will see them in the next edition.