Rajendra Gupta
ApexSQL Generate

SQL Not Equal Operator introduction and examples

June 6, 2019 by

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.

ApexSQL Generate

We can see sample data in the Products table.

Sample data

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.

Get all product details except ProductID

As stated earlier, we can use != operator as well to get the same output.

Get all product details except ProductID

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.

Get a list of all product except those launched in the Year 2019 using SQL Not Equal

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.

Get a list of all product excluding a specific product

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.

Specifying multiple conditions using SQL Not Equal operator

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.

sample data

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.

Output of SET STATISTICS TIME

In the actual execution plan of this query, we can see SQL Not Equal predicates along with a Non-clustered index scan operator.

Actual execution plan

Index scan property

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.

SET STATISTICS TIME output

In the Actual Execution plan, it used Clustered Index Seek while SQL Not Equal used

Clustered index seek operator

In the property for the Clustered Index Seek, it uses an equality operator to produce a similar result set.

Clustered Index Seek

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

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views