intro
Let's learn everything you need to know about the SQL not equal operator to become an expert in filtering out undesired records.
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:
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:
1
expression1 (<>|!=) expression2
For a recap, take a look at the summary table below:
| DBMS | Not Equal Operators | Results |
|---|---|---|
| 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:
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:
1
SELECT *
2
FROM products
3
WHERE id <> 5;
The above query is equivalent to:
1
SELECT *
2
FROM products
3
WHERE id != 5;
The result will be:
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:
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:
1
SELECT *
2
FROM products
3
WHERE category != 'Clothing';
The result set will have no "Clothing" products:
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:
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 :
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:
1
SELECT category, COUNT(*) as product_counter
2
FROM products
3
GROUP BY category
4
HAVING category <> 'Electronics';
The resulting record set will be:
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:
1
SELECT name, price, category != "Electronics" as is_not_electronics
2
FROM products;
This will return the following data:
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:
1
SELECT Id, Name, Price <> 'Electronics' as is_not_electronics
2
FROM Product;
This will fail with the following error:
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.
