This article explores the SQL Not Equal comparison operator (<>) along with its usage scenarios.
We must have used comparison operators in mathematics in the early days. We use these operators to compare different values based on the conditions. For example, we might compare the performance of two authors based on a number of articles. Suppose Raj wrote 85 articles while Rajendra wrote 100 articles. We can say that-
The total number of articles written by Rajendra > (Greater than) the total number of articles written by Raj.
We can have the following comparison operators in SQL.
Greater than to equals to
Less than or equals to
In the table above, we can see that there are two operators for Not Equal (<> and !=) . In this article, we will explore both operators and differences in these as well.
SQL Not Equal <> Comparison Operator
We use SQL Not Equal comparison operator (<>) to compare two expressions. For example, 10<>11 comparison operation uses SQL Not Equal operator (<>) between two expressions 10 and 11.
Difference between SQL Not Equal Operator <> and !=
We can use both SQL Not Equal operators <> and != to do inequality test between two expressions. Both operators give the same output. The only difference is that ‘<>’ is in line with the ISO standard while ‘!=’ does not follow ISO standard. You should use <> operator as it follows the ISO standard.
Let’s set up a sample table to explore SQL Not Equal operator.
CREATE TABLE dbo.Products
PRIMARY KEY IDENTITY(1, 1),
To generate the test data, I used ApexSQL Generate as shown in the following screenshot.
We can see sample data in the Products table.
Example 1: Get all product details except ProductID 1
We are going to use SQL Not Equal operator <> to exclude ProductID 1 in the output.
Select * from dbo.products where ProductID <> 1
As stated earlier, we can use != operator as well to get the same output.
Select * from dbo.products where ProductID!=1
Example 2: Get a list of all product except those launched in the Year 2019
Suppose we want to get a list of products that launched except in the year 2019. We can use the following query using SQL Not Equal operator.
Select * from dbo.products where Year(ProductLaunchDate) <>2019
In the output, we can see all products except those launched in the Year 2019.
Example 3: Get a list of all products excluding a specific product
In previous examples, we used SQL Not Operator and specified a numerical value in the WHERE condition. Suppose we want to exclude a particular product from the output. We need to use string or varchar data type with a single quote in the where clause.
Select * from dbo.products where Productname<>'Batchpickphone'
In the output, we do not have productID 10 as it gets excluded from the output.
If we do not specify the expression in a single quote, we get the following error message. It treats the expressions as a table column name without the single quote.
Msg 207, Level 16, State 1, Line 11 Invalid column name ‘Batchpickphone’.
Example 4: Specifying multiple conditions using SQL Not Equal operator
We can specify multiple conditions in a Where clause to exclude the corresponding rows from an output.
For example, we want to exclude ProductID 1 and ProductName Winitor (having ProductID 2). Execute the following code to satisfy the condition.
Select * from dbo.products where ProductID<>1 and ProductName<>'Winitor''
In the output, we do not have ProductID 1 and ProductID 2.
Example 5: SQL Not Equal operator and SQL Group By clause
We can use SQL Not Equal operator in combination with the SQL Group By clause. In the following query, we use SQL Group by on ProductLaunchDate column to get a count of products excluding the year 2019.
group by ProductLaunchDate
having Year(ProductLaunchDate) <>2019
Performance consideration of SQL Not Equal operator
In this part, we will explore the performance consideration of SQL Not Equal operator. For this part, let’s keep only 10 records in the products table. It helps to demonstrate the situation quickly.
Execute the following query to delete products having ProductID>10.
Delete from products where ProductID>10
We have the following records in the Products table.
Let’s execute the following query with the following tasks.
- We use SET STATISTICS IO ON to show statistics of IO activity during query execution
- We use SET STATISTICS TIME to display the time for parse, compile and execute each statement in a query batch
- Enable the Actual Execution plan to show the execution plan used to retrieve results for this query by the query optimizer
Set Statistics IO ON
Set Statistics Time On
Select * from dbo.products where ProductID<>1 and Year(ProductLaunchDate)<>2018 and ProductName<>'Winitor'
In the message tab, we can see the elapsed time for this query is 52 ms.
In the actual execution plan of this query, we can see SQL Not Equal predicates along with a Non-clustered index scan operator.
Let’s rewrite this query using IN operator. We get the same number of rows in this as well in comparison with a previous query using SQL Not Equal operator.
Set Statistics IO ON
Set Statistics Time On
Select * from dbo.products where ProductID in(5,6,7,8,9)
This time query took less time to return the same number of rows. It took only 1 ms while query with SQL Not Equal took 52 ms.
In the Actual Execution plan, it used Clustered Index Seek while SQL Not Equal used
In the property for the Clustered Index Seek, it uses an equality operator to produce a similar result set.
- Caution: We should use the Equality operator to get a better performance in comparison with the SQL Not Equal operator.
In this article, we explored SQL Not Operator along with examples. We also considered its performance implications in comparison with the Equality operators. You should try to use the Equality operator for better query performance. If you have any comments or questions, feel free to leave them in the comments below.
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at firstname.lastname@example.org
View all posts by Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
- Auditing in AWS RDS SQL Server - January 20, 2020
- Import JSON data into SQL Server - January 17, 2020
- Access PostgreSQL databases using the Azure Data Studio extension - January 16, 2020