PL/SQL NOT EQUAL

Introduction to PL/SQL Not Equal

PL/SQL NOT EQUAL is the relational operator in the PL/SQL framework that helps us determine whether the two objects or values we are comparing are equal. When the values are equal, the NOT EQUAL operator returns false, and when the values or objects we are trying to compare are NOT EQUAL, then it returns the true value. Here we will see about the types of operators present in PL/ SQL and how we can use the NOT EQUAL operator, which falls into relational operators.

Operators in PL/ SQL and NOT EQUAL Introduction

There are various kinds of operators supported by PL/ SQL, which include Relational operators, Arithmetic operators, Logical operators, String operators, Comparison operators. The comparison operators include operators like IN, IS NULL, LIKE, BETWEEN, etc. The relational operators include EQUAL, NOT EQUAL, LESS THAN, GREATER THAN, LESS THAN OR EQUAL TO, GREATER THAN OR EQUAL TO, etc. For example, the NOT EQUAL operator in PL/ SQL can be represented by using three different types of symbols, which are <> or != or ~=.

NOT EQUAL operator is just the negation of EQUAL operator and works in the exactly opposite manner than that of EQUAL TO operator. The EQUAL operator returns true when the two objects are exactly the same in value or else returns false, while in the case of NOT EQUAL operator, it returns true when both the objects are not the same or not equal, and when they are same, it returns a false value. The objects whose values we are trying to compare should be placed at both the ends of the NOT EQUAL operator. Suppose we have to compare values of A and B objects using NOT EQUAL operator, then we can write it as A <> B or A != B or A ~= B.

Syntax:

AS PER OUR REQUIREMENT, the NOT EQUAL operator can be used anywhere inside the PL/ SQL query statement, such as WHERE clause, SELECT clause, etc.

The syntax for specifying the operator is given below:

A <> B

Or

A ~= B

Or

A !=B

In the above syntax, A and B can be any objects that can be column values of a table, expressions of some values, or even the literal constant values we are trying to compare. Any of the above-mentioned three syntaxes can be used for comparison. For example, using the above syntax, we can check the inequality of two objects.

Examples of PL/SQL NOT EQUAL

Given below are the examples of PL/SQL NOT EQUAL:

Test Data:

Consider one table whose name is customers_details that stores the information about all the customers and their contact details.

The contents of the table can be checked by using the following query statement.

Code:

SELECT * FROM [customers_details];

The output of the execution of the above query statement is as shown below, which shows the rows contained by the customers_details table.

Output:

We will consider this table for the implementation of the NOT EQUAL operator in PL/ SQL.

Example #1

Suppose we want to retrieve only those records involving f_name, l_name, store_id, and mobile_number whose store_id is not equal to VEGETABLES.

Code:

SELECT f_name, l_name, store_id, mobile_number FROM [customers_details] WHERE store_id <> "VEGETABLES";

The output of the execution of the above query statement is as shown below, displaying all the rows of customers whose store_id is not equal to VEGETABLES.

Output:

Example #2

We can retrieve the same result shown above by using the operator != for NOT EQUAL operator.

Code:

SELECT f_name, l_name, store_id, mobile_number FROM [customers_details] WHERE store_id != "VEGETABLES";

The output of the execution of the above query statement contains the same rows as above, as displayed below.

Output:

Example #3

An alternative way is to use the ~= operator, which is also a form of NOT EQUAL operator in PL SQL. For example, using the below query statement, we can retrieve the same rows shown in the above two examples.

Code:

SELECT f_name, l_name, store_id, mobile_number FROM [customers_details] WHERE store_id ~= "VEGETABLES";

Output:

Example #4

Let us now consider an example where we will use the NOT EQUAL operator in the SELECT statement and try to retrieve the value which we get after comparing the store id column value with electronics using the NOT EQUAL operator. Which means that if the store id is not equal to ELECTRONICS, then it will return a true or Boolean representation of truth is also sometimes considered as 1. When the store id is equal to ELECTRONICS, the returned value will be false or 0, which represents the Boolean value of false. Our query statement will then become.

Code:

SELECT f_name, l_name, store_id != "ELECTRONICS", mobile_number FROM [customers_details] ;

The output of the above statement will retrieve 1 for customers whose store id is not equal to electronics and 0 for electronics store id for customers as shown in the below image.

Output:

Example #5

We can even make the use of NOT EQUAL operator more than one times in where clause for the same column name. For example, let us retrieve all the customers whose store ids are not equal to electronics or vegetables. For this, we can make use of the following PL/ SQL query statement.

Code:

SELECT f_name, l_name, mobile_number, store_id FROM [customers_details] WHERE store_id != "ELECTRONICS" and store_id!= "VEGETABLES" ;

Output:

Conclusion

We can make the use of the NOT EQUAL relational operator in PL/ SQL to compare the two values and get true only when both of the objects are not equal else get false. There are various symbolic ways in which we can use NOT EQUAL operator that involves !=, <> or ~= operator.

Recommended Articles

This is a guide to PL/SQL NOT EQUAL. Here we discuss the introduction, operators in PL/ SQL and NOT EQUAL introduction, examples, respectively. You may also have a look at the following articles to learn more –

1. Triggers in PL/SQL
2. PL/SQL Collections
3. PL/SQL Data Types
4. Loops in PL/SQL