  # SQL Not Equal Operator introduction and examples

June 6, 2019

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.

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. As stated earlier, we can use != operator as well to get the same output. ### 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.

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.

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.

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.

## 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.

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

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.

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.

Functions, SQL commands 