SQL - NOT EQUAL

The SQL NOT EQUAL Operator

The SQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by "<>" and "!=". The difference between these two is that <> follows the ISO standard, but != doesn't. So, it is recommended to use the <> operator.

We can use the NOT EQUAL operator in WHERE clause to filter records based on a specific condition and in GROUP BY clause to group the results.

The comparison is case-sensitive by default, while using the NOT EQUAL operator with text values.

Syntax

Following is the syntax of the NOT EQUAL operator in SQL −

WHERE expression1 <> expression2;

Example

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
); 

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as follows −

NOT EQUAL with Text

We can use the NOT EQUAL operator with text in SQL to compare two text values and return. We can use "<>" or "!=" in the WHERE clause of a SQL statement and exclude rows that match a specific text value.

Example

In the following query, we are retrieving all the records from the CUSTOMERS table whose NAME is not 'Ramesh' −

SELECT * FROM CUSTOMERS WHERE NAME <> 'Ramesh';

Output

The output of the above code is as shown below −

NOT EQUAL with GROUP BY Clause

We can use the NOT EQUAL operator with the GROUP BY clause to group the results by the values that are not equal to the specified text value.

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

Example

Here, we are retrieving the number of records with distinct ages (excluding '22') in the 'CUSTOMERS' table and grouping them by age value −

SELECT COUNT(ID), AGE FROM CUSTOMERS 
WHERE AGE <> '22' GROUP BY AGE;

Output

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

NOT EQUAL with Multiple Conditions

The not equal operator can also be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria.

Example

Now, we are retrieving all the customers whose salary is either ">2000" or "=2000". At the same time, the customer must not be from "Bhopal" −

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

Output

Following is the output of the above code −

Negating a Condition Using NOT EQUAL

In SQL, the NOT EQUAL operator can also be combined with the NOT Operator to negate a condition. It filters out the rows that meet a specific condition.

Example

In the following query, we are retrieving all rows from the "CUSTOMERS" table where the "SALARY" is equal to '2000' −

SELECT * FROM CUSTOMERS WHERE NOT SALARY != '2000';

Output

After executing the above code, we get the following output −