Oracle Operators: Part 1

James Tomar
10y
7.8k
0
1

Article

In Oracle, operators are used with the data items to perform the related actions and then return a result. Oracle provides the following two general classes of operators:

Unary: In unary operators, only one operand is used.

Eg: operator operand
Binary: In binary operators, two operands are used.

Eg: operator1 operand operator2

Types of operators in Oracle

1. To perform mathematical computations on numeric operands, arithmetic operators are used.

Addition (+)

To add two or more numbers the (+) operator is used.
Substraction (-)

To subtract two numeric values the (-) operator is used.
Multiply (*)

The (*) operator multiplies one number by another.
Division (/)

The (/) operator divides one number by another.

Syntax

SELECT <Expression> [arithmetic operator] <Expression>.....

FROM [table_name]

WHERE [Expression];

Example

BEGIN

DBMS_OUTPUT.PUT_LINE(7 * 3); --multiplication

DBMS_OUTPUT.PUT_LINE(18 / 5); --division

DBMS_OUTPUT.PUT_LINE(15 + 6); --addition

DBMS_OUTPUT.PUT_LINE(32 - 16); --subtraction

END;

2. Concatenate Operator (||)

The Concatenate operator concates two strings and returns another string as the result.

Syntax

String_1 ||String_2 || String_n

Example

‘Friday’ || ‘January 20th’

Note: It returns Friday 20th January
'2' || '0' || '1' || '4'

Note: It returns 2014

3. Comparison Operator

The Comparison operator is used when we want to compare one expression with another. It returns TRUE, FALSE or NULL.

Here are the comparison operators:

= Equals to
< Less than
> Greater than
<= Less than equal to
>= Greater than equal to
IN Operator

To determine if a column's value exists in a given set of values we use the IN operator and if the column value is equal to any of the values specified in a given set then the condition is validated. The condition that is defined using the IN operator is also known as a membership condition.

Example

SELECT Emp_id

FROM Employees

WHERE Salary IN (24000, 50000, 35000);
LIKE Operator

For wildcard searches and pattern dependent matching in a SELECT query we use the “LIKE” operator. If we know any portion of the column value a wildcard can be used to find the rest of the portion. It uses a wildcard for the search string; that’s why it is also known as a "wildcard search". The two wildcard operators are: Percentile (%) and Underscore (_). Percentile is used to replace more than one character and underscore is used to replace a single character.

Example

SELECT First_Name

FROM Students

WHERE Last_Name LIKE 'Arora';
BETWEEN

It is used to compare a column value within a specified range having a lower and upper limit. It is generally the same as the composite inequality operators (<= and >=).

Example

SELECT Emp_Name

FROM Employees

WHERE Salry BETWEEN (22000 AND 45000);
IS(NOT) NULL

NULL values are normally unknown and also unassigned whereas the equality operator tests for a definite value, that’s why it cannot be tested using the equality operator. The NULL operator serves as an equality operator to check for NULL values of a column.

Example

SELECT Emp_Name

FROM Employees

WHERE Bonus_Pct IS NULL;

4. Logical Operator

Oracle includes three types of logical operators: AND, OR and NOT. These operators operate on Boolean operands and produce Boolean results.

AND Operator: If the value of both of the operands that we used are TRUE then the condition will automatically be TRUE.

Syntax: Boolean Expression AND Boolean Expression

For Example: Assume the following table to understand the examples:

Cust_ID	First Name	Last Name	Country
111	ABC	Gupta	Australia
116	QWE	Williams	Russia
114	XYZ	Dayal	Australia
112	PQR	Tayal	America
113	TRY	Merchant	India
115	ASD	Rai	America

To find the first and the last names of the Customers between the id 111 to 114 years, the query will be like:

Query

SELECT First_name, Last_name, Country

FROM Customers

WHERE Cust_Id >= 111 OR Id <= 113

The output will be:

Cust_ID	First Name	Last Name	Country
111	ABC	Gupta	Australia
114	XYZ	Dayal	Australia
112	PQR	Tayal	America
113	TRY	Merchant	India

Note: The following table describes how logical the "AND" operator selects a row.

Column 1 Satisfied	Column 2 Satisfied	Row Selected
Yes	Yes	Yes
Yes	No	No
No	Yes	No
No	No	No

OR Operator: By using the logical operator “OR”, if any of the two operands are true then the condition will automatically become TRUE.

Syntax: Boolean Expression OR Boolean Expression

For example

If you want to find the first and last names of the Customers belonging to the country Australia or America, the query will be like:

Query

SELECT First_name, Last_name, Country

FROM Customers

WHERE Country = 'America' OR Country = 'Australia'

The output will be:

First Name	Last Name	Country
ABC	Gupta	Australia
XYZ	Dayal	Australia
PQR	Tayal	America
ASD	Rai	America

Note: The following table describes how the logical "OR" operator selects a row.

Column 1 Satisfied	Column 2 Satisfied	Row Selected
Yes	Yes	Yes
Yes	No	Yes
No	Yes	Yes
No	No	No

NOT Operator: It is used to reverse the logical state of its operand. If the condition of the operand is TRUE then using the NOT operator makes it FALSE.

Syntax: NOT Boolean Expression

For example: If you want to determine the Customers who do not belong to the country Australia, the query will be like:

SELECT First_name, Last_name, Country

FROM Customers

WHERE NOT Country = 'Australia'

The output will be: