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

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.

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.

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)