MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER

SQL Not Equal Operator: Definitive Guide with Examples

intro

Let's learn everything you need to know about the SQL not equal operator to become an expert in filtering out undesired records.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

A common scenario when querying a database is to exclude specific records from the result set. For example, you might want to retrieve all non-C-level employees with a simple query. How could you do that? With the SQL not equal operator!

That special comparison operator allows you to apply conditions that records do not have to meet in order to be selected. In other terms, it is the opposite of the SQL equal = operator that we all know and love.

In this guide, you will see what the SQL not equal operator is, how it works, what syntax variations it supports, and how to use it in different real-world scenarios. Time to learn how to exclude SQL records like a pro!

What Is the SQL Not Equal Comparison Operator?

In SQL, not equal is a comparison operator to compare two expressions and determine whether they are different from each other. Basically, it tests the inequality between the values of two operands.

The SQL not equal operator is generally used in queries to filter out records that do not match a specific criterion. This helps you exclude records from the results set that do not match one or more conditions.

As part of the ISO standard, most DBMS technologies support such SQL operators. Refer to the official documentation pages for more information:

Not Equal SQL: Syntax and Return Value

This is the syntax of the not equal SQL operator according to the ISO standard:

Copy
        
1 expression1 <> expression2

Where expression1 and expression2 are any valid non-NULL SQL expressions. If the left operand is not equal to the right operand, the result is the value representing the true value in the database system. Otherwise, the result is a false value.

Note: If the two expressions have different SQL types, the DBMS implicitly converts them to a common type so that it can perform the comparison.

Since the SQL equality operator is =, and ! is typically used in programming languages to negate operators, most database systems treat != as an alias of <>. Thus, <> are != equivalent and produce the same result.

In other words, the non-standard syntax for the SQL not equal comparison operator is:

Copy
        
1 expression1 (<>|!=) expression2

For a recap, take a look at the summary table below:

DBMSNot Equal OperatorsResults
MySQL<>, !=0, 1, NULL
PostgreSQL<>, !=false, true, NULL
SQL Server<>, !=FALSE, TRUE, NULL
Oracle<>, !=0, 1, NULL

Note that if any of the two expressions is NULL, the result of the not equal comparison is NULL. To test NULL values, you should instead use IS NULL or IS NOT NULL. Find out more in our guide on how to deal with NULL values in SQL.

Example List for Not Equal in SQL

Now that you know what the SQL not equal operator is and how it works, you are ready to see it in action in some examples!

The queries below will be executed on the following products table:

The Data tab for the products table in Dbvisualizer
The Data tab for the products table in Dbvisualizer

As you can see, it contains a list of product records. This is a MySQL table, but the examples in this section will work with PostgreSQL, SQL Server, and Oracle as well.

The sample queries will be executed in DbVisualizer, the database client with the highest user satisfaction in the market.

Get All Products Except a Specific One

Suppose you want to retrieve all products except the one with id equal to 5. To achieve that, you can use the WHERE not equal SQL query that follows:

Copy
        
1 SELECT * 2 FROM products 3 WHERE id <> 5;

The above query is equivalent to:

Copy
        
1 SELECT * 2 FROM products 3 WHERE id != 5;

The result will be:

Note that the product with id = 5 has been excluded from the result set
Note that the product with id = 5 has been excluded from the result set

Notice how the result set does not include the product with id equal to 5.

Since the DBMS applies data conversion for comparison to the left and right expression of the SQL not equal operator, you could also specify 5 as a string:

Copy
        
1 SELECT * 2 FROM products 3 WHERE id <> '5';

The result will be the same.

Get All Products Excluding Those of a Specific Category

The not equal operator works with any non-null expression, including strings. Use it to select all products except those in the "Clothing" category as below:

Copy
        
1 SELECT * 2 FROM products 3 WHERE category != 'Clothing';

The result set will have no "Clothing" products:

No “Clothing” products in the resulting records
No “Clothing” products in the resulting records

Get All Non-Electronics Products Except for a Specific Product

Sometimes, a single WHERE not equal SQL condition is not enough. You can specify more to get the desired result set by concatenating them with the AND operator:

Copy
        
1 SELECT * 2 FROM products 3 WHERE category != 'Electronics' AND id != 2;

The above query will select all non-electronic products that have an id different from 2 :

The resulting products match the exclusion criteria
The resulting products match the exclusion criteria

Group All Products Except Those in a Specific Category

Not equal in SQL can also be used in GROUP BY statements. Specifically, you can specify it in the SQL HAVING clause as below:

Copy
        
1 SELECT category, COUNT(*) as product_counter 2 FROM products 3 GROUP BY category 4 HAVING category <> 'Electronics';

The resulting record set will be:

No “Electronics” products in the grouped result set
No “Electronics” products in the grouped result set

Mark Products as Not Belonging to a Specific Category

While not equal SQL operator is mainly used in the WHERE clause, it can also appear in the SELECT clause. This is useful for marking records as not belonging to a specific category:

Copy
        
1 SELECT name, price, category != "Electronics" as is_not_electronics 2 FROM products;

This will return the following data:

Note the boolean is_not_electronics column
Note the boolean is_not_electronics column

Note the 0 and 1 values in the is_not_electronics column. In PostgreSQL, those would be boolean values. In SQL Server, the query would produce a syntax error as the not equal operator cannot be used in the SELECT clause.

Et voilà! You are now a master of not equal in SQL.

Conclusion

In this guide, you looked at the SQL not equal operator to find out which databases support it, how it works, and what its syntax is. Now you know that it helps you compare two SQL expressions for inequality. Thanks to the examples shown here, you also understood when and how to use not equal in SQL.

Applying complex filtering conditions becomes easier with a database client. DbVisualizer is a powerful database client that supports many DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free today!

FAQ

Equal and not equal in SQL: What is the difference?

In SQL, the equal operator (=) compares two expressions for equality, ensuring that they have the same values. On the contrary, the SQL not equal operator (!= or <>) checks for inequality. Thus, they are one the opposite of the other.

What is the difference between SQL not equal Operator <> and !=?

In terms of output, there is no real difference, since both are equivalent SQL not equal operators. In particular, != is an alias of <>. The real difference is that <> is defined in the ANSI 99 SQL standard, while != is not. So, not all databases may support !=, while <> is supported by most DBMS engines.

What are the performance considerations of the not equal SQL operator?

The <> operator often triggers a full table scan, which can worsen the performance of a query. This is because indexes are less efficient when it comes to inequality comparisons, as each row must be tested to see whether it passes the inequality check. To improve performance, you can use the IN operator with the specific values to look for, instead of looking for values to exclude with the not equal operator.

Is it possible to use the not equal to SQL Server operator in a SELECT clause?

No, it is not possible because it would result in a SQL syntax error. For example, consider the SQL Server query below:

Copy
        
1 SELECT Id, Name, Price <> 'Electronics' as is_not_electronics 2 FROM Product;

This will fail with the following error:

Copy
        
1 [Code: 102, SQL State: 42000] Incorrect syntax near '<'.

How to use the Equals (=) and Not Equal To (<>) comparison operators with NULL values in SQL Server?

In SQL Server, using = and <> with NULL values requires special handling because NULL signifies unknown. Comparisons like column = NULL or column <> NULL always return NULL. Instead, use IS NULL or IS NOT NULL for equality checks. Otherwise, set ANSI_NULLS to OFF to change the behavior of the Equals (=) and Not Equal To (<>) comparison operators.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Clustered vs Non-Clustered Index: Complete SQL Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2025-02-13
title

Queries and Subqueries in SQL: The Definitive Guide

author Lukas Vileikis tags DbVisualizer MySQL POSTGRESQL SQL SQL SERVER 7 min 2025-02-12
title

How to Optimize a Clustered Index Scan in SQL

author Antonello Zanini tags MySQL SQL SQL SERVER 7 min 2025-02-11
title

SQL Derived Table: Everything You Need to Know

author Antonello Zanini tags MySQL POSTGRESQL SQL SQL SERVER 9 min 2025-02-06
title

How To List Databases in PostgreSQL: A Complete Guide

author Antonello Zanini tags POSTGRESQL 8 min 2025-02-05
title

Understanding MVCC in MySQL

author Lukas Vileikis tags DbVisualizer MySQL SQL 7 min 2025-02-05
title

MySQL UPDATE JOIN Queries: A Complete Guide

author Antonello Zanini tags MySQL 8 min 2025-02-04
title

Postgres Create Array of Numbers: Complete Guide

author Lukas Vileikis tags POSTGRESQL SQL 7 min 2025-02-03
title

How to Protect MySQL With Fail2Ban

author Antonello Zanini tags MySQL 12 min 2025-01-30
title

SQL Injection Cheat Sheet: SQLi 101

author Lukas Vileikis tags MySQL SECURITY SQL 10 min 2025-01-29

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.