See the Tutorial List

Operators in PL/SQL

An operator is a symbol that tells the compiler to perform the operation on one or more operands specified along with the symbol. The variables on which the operation has to be performed is called operand and what operation is to be done is indicated by the operator symbol.

In PL/SQL, operators can be classified broadly into following categories:

  • Arithmetic operators
  • Relational operators
  • Comparision operators
  • Logical operators

Arithmetic Operators

Arithmetic operators are used to perform different mathematical operations on operands. Following are the arithmetic operators available in PL/SQL:

NOTE: For providing examples for every operator, let's consider two variables, a and b with values 6 and 3 respectively.

Operator Use Example
+ Adds the two operands a+b will give 9
- Performs subtraction, where the second operand is subtracted from the first operand. This operator can return negative value too. a-b will give 3
/ Performs Division operation. a/b will give 2
* Performs Multiplication a*b will give 18
** Performs Exponentiation operation which means the first operand raised to the power the second operand a**b will give 216

Relational Operators

Relational operators are used to compare two values and return the result in the form of boolean value(either TRUE or FALSE). They are mostly used in conditions where some sort of comparison is required. Following are the relational operators available in PL/SQL:

NOTE: For providing examples for every operator, let's consider two variables, a and b with values 7 and 14 respectively.

Operator Use Example
= To check if the values of the two operands given is equal or not. If it is equal then the condition will return true else false. (a = b) is False

!=

<>

~=

These operators are used to check if the two operands are not equal to each other or they don't have the same values. If the operands are not equal then the condition will return true else false. a != b is True
< To check if LHS value is smaller than RHS value. If yes, condition returns true. a < b is True
> To check if LHS value is greater than RHS value. If yes, condition returns true. a > b is False(not true)
<= To check if LHS value is smaller than or equal to RHS value. a <= b is True
>= To check if LHS value is greater than or equal to RHS value a >= b is False(not true)

Logical Operators

Logical operators are used to combine multiple expressions or define an expression with two operand and return either True or False based on the operands or expressions surrounding the logical operators.

NOTE: For providing examples for every operator, let's consider two variables(or expressions), a and b with values true and false respectively.

Operator Use Example
AND Returns TRUE when both LHS and RHS operand are true.
Returns FALSE when LHS and RHS operand are both or either of them are false.
a AND b will return false
OR Returns TRUE when LHS and RHS operand both or either of them are true.
Returns FALSE when LHS and RHS operand are false.
a OR b will return true
NOT Applied on a single operand.
Returns TRUE when operand is false.
Returns FALSE when operand is true.
NOT a will return false

In the table above, when we say operand, it can be an expression too. While writing SQL queries we tend to use these operators with the WHERE clause.


Comparison Operators

Comparison operators are used to compare one value with the other to return the result as TRUE or FALSE or NULL. Following are the different types of comparision operators:


1. LIKE Operator

This operator is used to match a single character or group of characters(string). There are two wildcard characters which are used for the purpose of matching.

% is used to match a string of any characters.

Whereas, _ is used to match a single character.

You can see here: LIKE operator example in SQL

This operator returns TRUE if string or characters are matched otherwise returns FALSE.

SELECT * from student WHERE sname LIKE 'J%';

When we execute the above SQL query, it will display student record whose name starts with J where % is used to match string of any character after first character J. We will get names like Jon, Jiya, John, James etc if they are present in the student table.

Let's take another example,

SELECT * from student WHERE sname LIKE '_ _ _ a';

When we execute the above SQL query, it will display student record whose name is of 4 letters and ends with the character A.


2. BETWEEN Operator

This operator is used to check whether the value is within a certain given range. It returns TRUE if the value is in the given range otherwise returns FALSE.

Let's take an example:

SELECT * from student WHERE age BETWEEN 12 AND 18;

The above SQL query, will display student records whose age lies between 12 and 18.


3. IN Operator

This operator is used when any value is required to be compared to a given list of values. It returns TRUE if the value is present in the given list otherwise it returns FALSE. This operator comes in handy where we have to do multiple compariosns rather than using multiple OR conditions.

Let's take an example:

SELECT * from student WHERE city IN ('Delhi','Goa','Kerela');

The above SQL query, will display the student records who belongs to city Delhi or Goa or Kerela.


4. IS NULL Operator

This operator returns TRUE if the operand value is NULL(empty) otherwise returns FALSE

Let's take an example:

SELECT * from student WHERE age IS NULL;

The above SQL query, will display student records whose age field in table is blank(empty)