Difference Between != and <> for Not Equal in SQL

1. Introduction

When writing SQL queries, we often need to filter out specific values by using a not equal condition. However, two different operators are available for this purpose: != and <>.

In this tutorial, we’ll examine which operator to use and whether they behave differently across various databases.

We’ll run the queries on the Student table from the Baeldung University database, testing them on SQL Server 2022, MySQL 8, and PostgreSQL 16. However, they should behave similarly in most databases.

2. The Standard Not Equals Operator in SQL

The SQL standard designates <> as the proper way to represent the not equal operation. As part of the ANSI SQL standard, it’s universally supported by all compliant database systems.

Let’s write a query to find out the list of departments other than Computer Science:

SELECT * 
FROM Department
WHERE code <> 'CS';

When we run this query, we get the result as:

+-------+--------------------------------+------+
| id    |            name                | code |
+-------+--------------------------------+------+
|     2 | Electronics and Communications | EC   |
|     3 | Mechanical Engineering         | ME   |
|     4 | Civil Engineering              | CE   |
|     5 | Mathematics                    | MA   |
+-------+--------------------------------+------+

This excludes the Computer Science department, which has the code CS. This query runs on all database systems that comply with the ANSI standard.

3. Alternative != Operator

While <> is the official SQL standard, many databases also support != as an alternative for the not equal condition. This is often used by developers familiar with programming languages like Java, C, or Python, where != is the standard operator for inequality.

Let’s rewrite the previous query using the != operator:

SELECT * 
FROM Department
WHERE code != 'CS';

When we run this query, it returns the same results as well.

4. Which One to Use?

In SQL Server, MySQL, and PostgreSQL, both <> and != are supported as valid operators for the not equal condition, with no performance difference between them. While both operators work similarly across these databases, the choice of which to use often depends on personal preference or team conventions. However, some databases like Microsoft Access only support <>, making != invalid in those environments.

In general, both operators are functionally equivalent across these databases, but <> is the standard as defined by the ANSI SQL specification. However, != tends to be preferred by developers who are more familiar with programming languages like Java, C, or Python, where this syntax is commonly used for inequality comparisons.

It’s important to note that when working with NULL values, we should use IS NULL and IS NOT NULL for comparisons, as standard operators like != and <> may not be reliable with NULL values.

5. Conclusion

In this tutorial, we explored the usage of != and <> for the not equal operation in SQL. Both operators are supported in all the popular databases, such as SQL Server, MySQL, and PostgreSQL, and there’s no performance difference between them. While some developers may prefer using != for familiarity, <> is the standard SQL operator. To ensure consistency and maintainability, the team can establish a convention that promotes readability and portability across databases.