SQL - NOT EQUAL

A comparison operator is a reserved word that is used in the WHERE clause of an SQL query to compare two components. These operators are employed in SQL statements to enumerate conditions and act as conjunctions for numerous conditions. SQL Not Equal is one of these operators.

SQL NOT EQUAL

We are aware that there are two Not Equal operators (!= and <>). In order to test for inequapty between two expressions, we can utipze the SQL Not Equal operators ‘!=’ and’<>’. The result from both operators is the same.

The main distinction is that "!=" does not follow the ISO standard, but "<>" does. As it corresponds to the ISO standard, you have to utipze the operator ‘<>’.

Syntax

Following is the portion of the SQL query that uses the not equal operator, which is displayed below.

WHERE expression1 <> expression2

Example

Let’s consider the following table, "Customers," present in our database. To get the Customer table, use the following query −

SQL> SELECT * FROM Customers;

Output

On executing the above query, it will generate the following output −

+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan   |  25 | Delhi     | 1500.00 |
|  3 | kaushik  |  23 | Kota      | 2000.00 |
|  4 | Chaitap |  25 | Mumbai    | 6500.00 |
|  5 | Hardik   |  27 | Bhopal    | 8500.00 |
|  6 | Komal    |  22 | MP        | 4500.00 |
+----+----------+-----+-----------+---------+

NOT EQUAL with text

The not equal operator with text in SQL helps you to compare a text value to another text value to determine if they are different. We can use "<>" or "!=" in the WHERE clause of a SQL statement and exclude rows that match a specific text value.

Example

In this case, we want to pick out every customer in the table whose name isn t Ramesh. The not equal operator is apppcable to both text and numeric data. Execute the below query to get all the customer details except for one whose name is not Ramesh.

SQL> SELECT * FROM Customers WHERE NAME <>  Ramesh ;

Output

On executing the above query, it will generate the output as shown below −

+----+----------+-----+---------+---------+
| ID | NAME     | AGE | ADDRESS | SALARY  |
+----+----------+-----+---------+---------+
|  2 | Khilan   |  25 | Delhi   | 1500.00 |
|  3 | kaushik  |  23 | Kota    | 2000.00 |
|  4 | Chaitap |  25 | Mumbai  | 6500.00 |
|  5 | Hardik   |  27 | Bhopal  | 8500.00 |
|  6 | Komal    |  22 | MP      | 4500.00 |
+----+----------+-----+---------+---------+

NOT EQUAL with GROUP BY clause

We can use the NOT EQUAL operator with the GROUP BY clause to group rows by a specific column and exclude certain values of that column.

The aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() are frequently used with the GROUP BY statement.

Example

Here, we are going to use the group by clause along with the not-equal operator to count and display the number of customers at each address except the age "22”.

SQL> SELECT COUNT(id), AGE FROM Customers
WHERE AGE <>  22  GROUP BY AGE;

Output

On executing the above query, it will generate an output as shown below −

+-----------+-----+
| COUNT(id) | AGE |
+-----------+-----+
|         1 |  32 |
|         2 |  25 |
|         1 |  23 |
|         1 |  27 |
+-----------+-----+

NOT EQUAL with multiple conditions

Depending on the situation, the not equal operator can be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria.

Example

In this case, we are going to combine two conditions in the query, and we are going to select all the customers on the table whose salary is either ">2000" or "=2000". At the same time, the customer must not be from "Bhopal".

SQL> SELECT * FROM Customers 
WHERE ADDRESS <>  Bhopal  AND (SALARY> 2000  OR SALARY= 2000 );

Output

On executing the above query, it will generate the following output as shown below −

+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
|  3 | kaushik  |  23 | Kota      | 2000.00 |
|  4 | Chaitap |  25 | Mumbai    | 6500.00 |
|  6 | Komal    |  22 | MP        | 4500.00 |
+----+----------+-----+-----------+---------+

Negating a condition using NOT EQUAL

In SQL, the not equal operator can also be used to negate a condition by combining it with the NOT operator. This is often used to filter out rows that meet a specific condition.

Example

You will obtain the outcomes of the equals (=) operator if you use the NOT operator to negate the condition provided by the not equal operator. Execute the below query, to understand more about it.

SQL> SELECT *
FROM Customers
WHERE NOT SALARY !=  2000 ;

Output

When the query gets executed it will generate the following output as shown below −

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+