Operators in Oracle

 

What are Operators in Oracle?

Operators are used to expressing the conditions in Select statements. The operator manipulates individual data items and returns a result. The data items are called operands or arguments. The different types of Operators available in Oracle are as follows:

• Arithmetic operators
• Assignment operator
• Relational operators
• Logical operators
• Special Operators
• Set Operators

Arithmetic Operators in Oracle:

The Arithmetic Operators in Oracle are used for performing mathematical calculations such as Addition, Subtraction, Multiplication, Modulo, and Division represented by the expected +, -, *(star or asterisk), %, and / forward slash symbols respectively on the given operand values. That is, they are used to perform mathematical operations on two expressions of the same or different data types of numeric data. 

1. The arithmetic operations can be used to create expressions on number and date data.
2. The arithmetic operations can be Used to perform any Arithmetic Operations like Addition, Subtraction, Multiplication, and Divided by.
3. The arithmetic operators can be used in any clause of a SQL statement.
4. SQL * Plus ignores the blank spaces before and after the arithmetic operator.

(1) Arithmetic Operator Addition (+)

Example: Display salary of employees with 2000 increments in their salary.

SELECT ID, Name, Salary, Salary + 2000 “Incremented salary” FROM Employee;

(2) Arithmetic Operator Subtraction (-):

This operator is used to perform the subtraction between two numbers and dates.

Example: Display the details of employees decreasing their salary by 200.

SELECT ID, Name, Salary, Salary – 200 “Decreased Salary” FROM Employee;

(3) Arithmetic Operator Multiplication (*):

This operator is used to perform multiplication.

Example: Display the details of the employees Incrementing their salary two times.

SELECT ID, Name, Salary, Salary * 2 “Increased Salary” FROM Employee;

(4) Arithmetic Operator Division (/):

This operator is used to perform a Division test. The division will display only the Quotient value, not the remainder value. Example 6/2 gives 3 because 2 divides 6 by 3 times.

Example: Display half of the salary of employees.

SELECT ID, Name, Salary, Salary / 2 “Division Salary” FROM Employee;

 

 

Assignment Operator in Oracle

The Assignment Operator in Oracle is used to assign or compare a value to a column or a field of a table. The equal sign (=) is the assignment operator where the value on the right is assigned to the value on the left. It is also used to establish a relationship between a column heading and the expression that defines the values for the column.

Example: Update the Price of each product by adding 1000

Our requirement is to increase the salary of all employees by 1000. Now we will update the Salary column of the Employee table by using the equals operator as an assignment. Following is the SQL statement.

UPDATE Employee SET Salary = Salary + 1000;

 

 

Relational operayors:- As the name suggests the Relational/Comparison Operators in Oracle are used to compare two values i.e. these operators are used for comparing one expression with another expression. The relational operators determine whether the two values are equal or a value is greater than the other, or less than the other. The result of a comparison can be TRUE, FALSE, or NULL (When one or both the expressions contain NULL values).

Types of Relational Operators in Oracle

The different types of relational operators that are available in Oracle are as follows: 

1. Equal (=) Operator
2. Not Equal (!= or <>) Operator
3. Greater Than (>) Operator
4. Less Than (<) Operator
5. Greater Than or Equal To (>=) Operator
6. Less Than or Equal To (<=) Operator

Understanding the Relational Operators in Oracle with Examples:

(1)Equal (=) Relational Operator in Oracle

The Equal (=) Operator in Oracle is used to check whether the two expressions are equal or not. If both the expressions are equal then the condition becomes true and will return the matched rows. For example, the following SQL Query will return all the records from the Employee table where the Gender is Male. Here the expression is Gender = ‘Male’ and it will check all the Gender column values in the Employee table and those values which are Matched with the value Male will be returned.

SELECT * FROM Employee WHERE Gender = ‘Male’;

 

(2) Not Equal (!=) Relational Operator in Oracle

The Not Equal (!=) Operator in Oracle is just the opposite of the equal operator. That means this operator is used to check whether the two expressions are equal or not. If both the expressions are not equal then the condition becomes true and will return the not-matched records. For example, the below SQL Statement will return all records from the Employee table except the employees whose gender is Male.

SELECT * FROM Employee WHERE Gender != ‘Male’;

 

(3) Greater Than (>) Relational Operator in Oracle

The Greater Than (>) Operator in Oracle is used to check whether the left-hand expression value is higher than the right-hand expression value. If the left-hand expression value is higher than the right-hand expression value then the condition becomes true and it will return the matched records. For example, the below SQL query will return all the records from the Employee table where the employee salary is greater than 45000.

SELECT * FROM Employee WHERE Salary > 45000;

(4) Less Than (<) Relational Operator in Oracle

The Less Than (>) Operator in Oracle is used to check whether the left-hand expression value is lower than the right-hand expression value. If the left-hand expression value is lower than the right-hand expression value then the condition becomes true and will return the matched records. For example, the below SQL query will return all records from the Employee table where the employee salary is less than 50000.

SELECT * FROM Employee WHERE Salary < 50000;

(5) Greater Than or Equal To (>=) Operator in Oracle

The Greater than or Equal To (>=) Operator in Oracle is used to check whether the left-hand expression value is higher than or equals to the right-hand expression value or not. If the left-hand expression value is higher than or equals to the right-hand expression value then the condition becomes true and will return all the matched records. For example, the following SQL Query will return all the records from the Employee table where the Salary is greater than or equal to 50000.

SELECT * FROM Employee WHERE Salary >= 50000;

(6) Less Than or Equal To (<=) Operator in Oracle

The Less than or Equal To (<=) Operator in Oracle is used to check whether the left-hand expression value is lower than or equal to the right-hand expression value or not. If the left-hand expression value is lower than or equals to the right-hand expression value then the condition becomes true and it will return all the matching records. For example, the following SQL Query will return all the records from the Employee table where the Salary is less than or equal to 50000.

SELECT * FROM Employee WHERE Salary <= 50000;

 

 

Logical Operators:- If you want to combine more than one condition, then you need to use the Logical Operators in Oracle. The Logical Operators in Oracle are basically used to check for the truth-ness of some conditions. Logical operators return a Boolean data type with a value of TRUE, or FALSE. In Oracle, there are three Logical Operators available. They are as follows:

1. AND: TRUE if both Boolean expressions are TRUE.
2. OR: TRUE if one of the Boolean expressions is TRUE.
3. NOT: Reverses the value of any other Boolean operator.

The Logical Operators in Oracle are used to compare two conditions to check whether a row (or rows) can be selected for the output. 

(1) Logical AND Operator in Oracle

The Logical AND operator in Oracle compares two conditions and returns TRUE if both of the conditions are TRUE and returns FALSE when either is FALSE. If you want to select rows that must satisfy all the given conditions, then in such cases you need to use the AND operator in Oracle.

SELECT * FROM employee WHERE (department = 'IT' AND Gender = 'Male'); --OR

SELECT * FROM employee WHERE department = 'IT' AND Gender = 'Male'; -- Bracket is optional

 

(2) Logical NOT Operator Example in Oracle:

We can also use the NOT keyword in the statement to revert one of the conditions. Suppose our requirement is to fetch all the employees whose Department is IT and Age is not equal to 28. Then in that case we can use NOT Operator as shown in the below query.

SELECT * FROM employee WHERE department = ‘IT’ AND NOT Age = 28;

(3) Logical OR Operator in Oracle

In some cases, we will have to evaluate only one of the conditions is TRUE to return the result set. In such a case, the logical OR operator is useful to create such compound conditions. Similar to AND operator, The OR operator in Oracle is useful to add multiple conditions in a single SQL statement. It displays the data rows if any one of the multiple conditions is TRUE. If all the conditions are false the SQL statement won’t return any result set.

SELECT * FROM Employee WHERE age = 25 OR age = 26; -- OR

SELECT * FROM Employee WHERE (age = 25 OR age = 26); -- Bracket is optional

(4) Logical NOT Operator in Oracle:

The Logical NOT Operator in Oracle takes a single Boolean as an argument and changes its value from false to true or from true to false. If we want to select rows that do not satisfy a condition, then you need to use the logical NOT operator. NOT results in the reverse of a condition. That is, if a condition is satisfied, then the row is not returned. For example, if we want to fetch the Employees who do not belong to the City of London, then we need to use the NOT Operator as shown in the below SQL query.

SELECT * FROM Employee WHERE NOT City = ‘London’;

(5) Nested Logical Operators Example:

If we want to select the employees whose Salary is between 27000 and 30000, or those whose City is not Mumbai, then the query would be like,

SELECT * FROM Employee WHERE Salary >= 27000 AND Salary <= 30000 OR NOT CITY = ‘Mumbai’;