Oracle Operators: Part 1

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:

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

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

       Eg: operator1 operand operator2

Types of operators in Oracle

Oracle Operators

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:

  1. =  Equals to
  2. <  Less than
  3. >  Greater than
  4. <= Less than equal to
  5. >= Greater than equal to
     
  6. 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);
     

  7. 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';
     

  8. 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);
     

  9. 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.

  1. 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:
     

    Cust_ID First Name Last Name Country
    116 QWE Williams Russia
    112 PQR Tayal America
    113 TRY Merchant India
    115 ASD Rai America


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

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

Next Article: Oracle Operators: Part 2