SQL - NOT EQUAL

SQL - The NOT EQUAL Operator

The SQL NOT EQUAL Operator (<> vs !=)

The SQL NOT EQUAL operator is a comparison operator used in a WHERE clause to filter out rows where a column’s value matches a specified value. It returns true for all rows that do not match the condition.

There are two common syntaxes for ‘not equal’:

• <>: This is the ISO standard syntax and is guaranteed to work across all major SQL database systems.
• !=: This syntax is widely supported by most popular databases (like PostgreSQL, MySQL, SQL Server, Oracle) but is not part of the official SQL standard.

Best Practice: It is recommended to use the <> operator for maximum portability and adherence to the SQL standard.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name <> value;

Example Data

Let’s use the following Customers table for our examples.

CREATE TABLE Customers (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT,
    City VARCHAR(100),
    Salary DECIMAL(10, 2)
);

INSERT INTO Customers (ID, Name, Age, City, Salary) 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', NULL, 'Indore', 10000.00);

Using NOT EQUAL with Different Data Types

Example: Filtering by Text

To retrieve all customers whose name is not ‘Ramesh’:

SELECT ID, Name, City FROM Customers WHERE Name <> 'Ramesh';

ID	Name	City
2	Khilan	Delhi
3	Kaushik	Kota
4	Chaitali	Mumbai
5	Hardik	Bhopal
6	Komal	Hyderabad
7	Muffy	Indore

Example: Filtering by Number

To retrieve all customers whose salary is not 2000.00:

SELECT ID, Name, Salary FROM Customers WHERE Salary <> 2000.00;

This will return all customers except Ramesh and Kaushik.

The Critical Role of NULL

This is a very common trap for beginners. In SQL, NULL represents a missing or unknown value. It is not equal to anything, and it is not not equal to anything. Any direct comparison to NULL using operators like =, <>, or != will result in UNKNOWN, which is treated as false in a WHERE clause.

Example: The Pitfall

Let’s try to find all customers whose age is not 25. Notice that Muffy, whose age is NULL, is NOT included in the result set.

SELECT ID, Name, Age FROM Customers WHERE Age <> 25;

The query above is interpreted as “find customers where it is true that their age is not 25”. For Muffy, her age is unknown, so the condition is not true.

The Correct Way to Handle NULL

To handle NULL values, you must use the IS NULL or IS NOT NULL operators. If you want to find all customers whose age is not 25, including those with a missing age, you must explicitly check for NULL.

SELECT ID, Name, Age FROM Customers WHERE Age <> 25 OR Age IS NULL;

Combining with GROUP BY

You can use the NOT EQUAL operator in a WHERE clause to filter rows before they are grouped by a GROUP BY clause.

Example

Let’s count the number of customers in each city, excluding the city of ‘Bhopal’.

SELECT City, COUNT(ID) AS NumberOfCustomers
FROM Customers
WHERE City <> 'Bhopal'
GROUP BY City;

Output

City	NumberOfCustomers
Ahmedabad	1
Delhi	1
Hyderabad	1
Indore	1
Kota	1
Mumbai	1

Combining with other Logical Operators

The NOT EQUAL operator can be combined with AND and OR to create more complex filtering logic.

Example

Find all customers who are not from ‘Bhopal’ AND whose salary is greater than 5000.

SELECT Name, City, Salary
FROM Customers
WHERE City <> 'Bhopal' AND Salary > 5000;

Output

Name	City	Salary
Chaitali	Mumbai	6500.00
Muffy	Indore	10000.00