SQL NOT EQUAL Operator

The SQL NOT EQUAL Operator is a comparison operator used to check if two expressions are equal or not. This operator is represented by “ != ” or “<>“.

This operator returns boolean values. If given expressions are equal, the operator returns false otherwise true. If any one expression is NULL, it will return NULL. It performs type conversion when expressions are of different data types, for example, 5!= “Five”.

We use the NOT EQUAL operator to display our table without some exceptional values. For example, Let’s, consider a table ‘Students’. For this table, we have, “id”, “name”, and “marks” as its columns. Now we want to display all those rows that marks other than “100”. In this kind of situation, NOT EQUAL operator can be used.

Note: <> and != perform the same operation i.e. check inequality. The only difference between <> and != is that <> follows 
the ISO standard but != does not. So it is recommended to use <> for NOT EQUAL Operator.

In this article, we will explore the NOT operator in SQL with proper examples along with a brief explanation.

SQL NOT EQUAL Syntax

SELECT * FROM table_name

WHERE column_name != value;

Demo SQL Database

In this tutorial on the NOT EQUAL operator, we will use the below table for examples and see the NOT EQUAL operator in SQL query.

user_id	name	contest_score	rank	coding_streak
vish3001	Vishu	100	1	150
neeraj119	Neeraj	99	2	125
ayush105	Aayush	98	3	110
sumit85	Sumit	99	2	100
harsh05	Harsh	98	3	95

To create this table in your system, write the following SQL queries in your DBMS;

MySQL

CREATE TABLE geeksforgeeks(
  user_id varchar(100) PRIMARY KEY,
  name varchar(100),
  contest_score int,
  rank int,
  coding_streak int
 );
INSERT INTO geeksforgeeks(user_id,name,contest_score,rank,coding_streak)
VALUES('vish3001','Vishu',100,01,150);
INSERT INTO geeksforgeeks(user_id,name,contest_score,rank,coding_streak)
VALUES('neeraj119','Neeraj',99,02,125);
INSERT INTO geeksforgeeks(user_id,name,contest_score,rank,coding_streak)
VALUES('ayush105','Aayush',98,03,110);
INSERT INTO geeksforgeeks(user_id,name,contest_score,rank,coding_streak)
VALUES('sumit85','Sumit',99,02,100);
INSERT INTO geeksforgeeks(user_id,name,contest_score,rank,coding_streak)
VALUES('harsh05','Harsh',98,03,95);

NOT EQUAL Operator Examples

Let’s look at some examples of the NOT EQUAL operator in SQL. We will cover using SQL NOT OPERATOR in WHERE clause.

Example 1: SQL NOT operator For String

We will display all those rows which do not have a name equal to ‘Harsh’

SELECT * FROM geeksforgeeks WHERE name!='Harsh';

Output:

SQL NOT operator For String

In the above image, we can see we have all those rows displayed which do not have their name equal to ‘Harsh’.

Note: The NOT EQUAL comparison is case-sensitive for strings. Meaning “geeks” and “GEEKS” are two different strings for NOT EQUAL operator.

Example 2: SQL NOT EQUAL Operator with Multiple Condition

We will display all those rows which do not have their contest score as 98 and rank as 3 and the coding streak should be greater than or equal to 100. Using AND or OR operator you can use the SQL NOT operator for multiple values.

SELECT * FROM geeksforgeeks
WHERE contest_score != 98 AND rank != 3
AND coding_streak >= 100;

Output:

SQL NOT EQUAL Operator with Multiple Condition

In the above image, we can observe that all those rows are displayed which have followed all three conditions.

Example 3: SQL NOT EQUAL Operator with GROUP BY Clause

We will display all those ranks with their count that do not have their contest score as 100. 

SELECT rank, COUNT(*) as count_score
FROM geeksforgeeks
WHERE contest_score <> 100
GROUP BY rank;

Output:

SQL NOT EQUAL Operator with GROUP BY Clause

In the above image, we can see ranks 2, and 3 have a count of 2 and, 2 respectively.

Key Takeaways About NOT EQUAL Operator

• SQL NOT EQUAL Operator is a comparison operator denoted as != or <>. It returns boolean values i.e. True or False.
• It returns False when the compared expressions are equal otherwise it returns True.
• We use this operator with the WHERE clause.
• We can use this operator for integers and strings-based logical reasoning. It is case-sensitive for string comparisons.
• We can put multiple conditions using the AND or OR operator.