How to write NOT EQUAL in SQL
In Structured Query Language (SQL), comparison operators are essential for filtering data based on conditions. One of the most commonly used is the “not equal” operator, which checks whether two values are different. While this seems straightforward, SQL provides multiple ways to express inequality — and not all of them are standardized.
This article explores the ISO-standard and non-standard ways of expressing “not equal” in SQL, supported by example data and queries for practical understanding.
Learn SQL online
ISO Standard Syntax: <>
The ISO SQL standard defines <> as the correct operator for not equal. This syntax is supported across all major relational database systems, including:
PostgreSQL
MySQL
Oracle
SQL Server
SQLite
Example Usage
SELECT * FROM employees
WHERE department_id <> 10;
This query returns all employees who are not in department 10.
Non-ISO (Vendor-Specific) Syntax: !=
Many SQL engines also allow != as a shorthand for inequality. While this is not part of the ISO standard, it’s widely supported as a non-standard extension, particularly by:
MySQL
PostgreSQL
SQL Server
SQLite
However, Oracle does not support !=, and using it will result in a syntax error. For maximum compatibility, prefer <>.
Example Usage
SELECT * FROM employees
WHERE department_id != 10;
This query behaves the same way as the previous example — selecting employees not in department 10 — but it may not work on every RDBMS.
Sample Data
Consider the following employees table:
| employee_id | name | department_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Carol | 30 |
| 4 | Dave | 10 |
Using the <> operator:
SELECT name FROM employees
WHERE department_id <> 10;
Result:
| name |
|---|
| Bob |
| Carol |
The same result would be returned using != in most databases:
SELECT name FROM employees
WHERE department_id != 10;
Summary of Compatibility
| Syntax | ISO Standard | Supported By | Notes |
|---|---|---|---|
<> | ✅ Yes | All major SQL databases | Recommended for portability |
!= | ❌ No | MySQL, PostgreSQL, SQL Server, SQLite | May cause errors in Oracle |
Best Practice
Use
<>for maximum portability and standards compliance.Use
!=only if you are certain of the database system in use and prefer the syntax.
Bonus Tip: Handling NULL with Inequality
Neither <> nor != will return rows where the column is NULL, due to SQL’s three-valued logic (TRUE, FALSE, UNKNOWN). Use IS NOT NULL to explicitly filter out NULL values.
SELECT * FROM employees
WHERE department_id <> 10 AND department_id IS NOT NULL;
Conclusion
While both <> and != are widely used in SQL for expressing inequality, only <> is officially standardized by ISO. Using <> ensures compatibility across different database systems, making it the preferred choice in multi-database environments or in codebases meant to be portable.
Always be aware of how your target database interprets SQL syntax and handle NULL values explicitly to avoid unexpected results.
Please see the other SQL video learning articles below as part of this learning SQL online course video.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
