This article explores the SQL Not Equal comparison operator (<>) along with its usage scenarios.
Introduction
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.
Operator | Description |
= | Equals to |
<> | Not Equal |
!= | Not Equal |
> | Greater than |
>= | Greater than to equals to |
< | Less than |
<= | 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.
|
1 2 3 4 5 6 |
CREATE TABLE dbo.Products (ProductID INT PRIMARY KEY IDENTITY(1, 1), ProductName VARCHAR(50), ProductLaunchDate DATETIME2 ); |
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.
|
1 |
Select * from dbo.products where ProductID <> 1 |
As stated earlier, we can use != operator as well to get the same output.
|
1 |
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.
|
1 |
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.
|
1 |
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.
|
1 |
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.
|
1 2 3 4 |
Select Count(ProductLaunchDate) from dbo.Products 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.
|
1 |
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
|
1 2 3 |
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.
|
1 2 3 |
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.
Conclusion
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023