find and solve || findandsolve.com
Please wait.....

Not Equal Operator in MS SQL Server

Not Equal Operator in MS SQL Server

In SQL Server, Is not equal operator is used to check whether two expressions (<> and !=) are equal or not. != and <> no difference in terms of performance or result.


In MS SQL null is not equal ( = ) to anything—not even to another null. According to the three-valued logic of SQL, the result of null = null is not true but unknown. MS SQL is not a null predicate to test if a particular value is null.


MS SQL Not equal is used to return a set of rows ( from a specific table column ) after making sure that two expressions placed on either side of the NOT EQUAL TO (<>) operator are not equal.


Introduction


You must have used comparison operators in mathematics in the early days. You use these operators to compare different values based on the required conditions. For example, You might compare the performance of two authors based on several blogs. Suppose Syliva wrote 40 blogs while Sundar wrote 30 blogs. you can say that-

The total number of blogs written by Sundar > (Greater than) the total number of blogs written by Sylvia.


You can have the following comparison operators in SQL.

OperatorDescription
=Equals to
<>Not Equal
!=
Not Equal
>Greater than
>=Greater than to equals to
<
Less than
<=
Less than or equals to


In the given above table, you can see that there are two operators for Not Equal (<> and !=). In this blog, you will explore both operators and differences in these as well.


SQL Not Equal <> Comparison Operator


You use MS SQL Not Equal comparison operator (<>) to compare two expressions. For example, 41<>44 comparison operation uses MS SQL Not Equal operator (<>) between two expressions 41 and 44


Difference between SQL Not Equal Operator <> and !=


you can use both MS SQL Not Equal operators <> and != to do an 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 the <> operator as it follows the ISO standard.


Let’s set up a sample table to explore MS SQL Not Equal operator.

CREATE TABLE dbo.EmployeeInfo
(
EmployeeId INT PRIMARY KEY IDENTITY(1,1),
 FirstName      VARCHAR(100),
 LastName VARCHAR(100),
);


Insert some record in EmployeeInfo Table like as given below

INSERT INTO dbo.EmployeeInfo VALUES('Sylvia','Neupane')
INSERT INTO dbo.EmployeeInfo VALUES('Rahul','Sharma')
INSERT INTO dbo.EmployeeInfo VALUES('Ubina','Thapa')
INSERT INTO dbo.EmployeeInfo VALUES('Debin','Bhattarai')
INSERT INTO dbo.EmployeeInfo VALUES('Ganesh','Khadka')
INSERT INTO dbo.EmployeeInfo VALUES('Rahul','Thapa')
INSERT INTO dbo.EmployeeInfo VALUES('Solina','Jharada')
INSERT INTO dbo.EmployeeInfo VALUES('Mekani','Laula')
INSERT INTO dbo.EmployeeInfo VALUES('Phadindra','Sewaro')
INSERT INTO dbo.EmployeeInfo VALUES('Silpu','Pokharel')
INSERT INTO dbo.EmployeeInfo VALUES('Baniya','Bhat')
INSERT INTO dbo.EmployeeInfo VALUES('Sokya','Lohoni')
INSERT INTO dbo.EmployeeInfo VALUES('Serang','jamkeri')


You can see sample data in the EmployeeInfo table.



Example 1: Get all product details except EmployeeId  1


You are going to use MS SQL Not Equal operator <> to exclude EmployeeId 1 in the output


SELECT * FROM  dbo.EmployeeInfo WHERE EmployeeId <> 1


When you execute the above code you will see 



As stated earlier, you can also use the != operator to get the same output. 


SELECT * FROM  dbo.EmployeeInfo WHERE EmployeeId!=1





Example 2: Get a list of all EmployeeInfo excluding a specific FirstName


We used MS SQL Not Operator in previous examples and specified a numerical value in the WHERE condition. Suppose you want to exclude an Employee FirstName from the output. You need to use string or varchar data type with a single quote in the where clause.


SELECT * FROM  dbo.EmployeeInfo where FirstName<>'Debin'

In the given output, we do not have EmployeeId 4 as it gets excluded from the output.



You will get the following error message if you do not specify the expression in a single quote. It treats the expressions as a table column name without a single quote.

Msg 207, Level 16, State 1, Line 1 Invalid column name 'Debin'.


Example 3: Specifying multiple conditions using SQL Not Equal operator


You can specify multiple conditions in a Where clause to exclude the corresponding rows from an output.

For example, You want to exclude EmployeeId 1 and FirstName Rahul (having EmployeeId 2 and EmployeeId 6). Execute the following code to satisfy the condition.


SELECT * from dbo.EmployeeInfo WHERE EmployeeId <>1 and FirstName<>'Rahul'

In the output, you do not have EmployeeId 1 and EmployeeId 2 and EmployeeId 6.



Example 4: SQL Not Equal operator and SQL Group By clause


You can use MS SQL Not Equal operator in combination with the SQL Group By clause. In the following query example, we use SQL Group by on the FirstName column to get a count of Employees excluding the FirstName Rahul.


SELECT Count(FirstName) 
FROM dbo.EmployeeInfo
GROUP BY FirstName
HAVING FirstName <>'Rahul'


Performance consideration of SQL Not Equal operator


In this part, you will explore the performance consideration of the MS SQL Not Equal operator. For this part, let’s keep only 8 records in the EmployeeInfo table. It helps to demonstrate the situation quickly.

Execute the following query to delete EmployeeInfo having EmployeeId >8.


DELETE FROM  EmployeeInfo WHERE EmployeeId > 8

We have the following records in the EmployeeInfo 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, compiling, and executing 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.EmployeeInfo where EmployeeId<>1 and FirstName <>'Rahul' and LastName<>'Thapa'

In the message tab, you can see the elapsed time for this query.



Let’s rewrite this query using IN operator. You will get the same number of rows in this as well in comparison with a previous query using MS SQL Not Equal operator.


Set Statistics IO ON
Set Statistics Time On
SELECT * FROM dbo.EmployeeInfo WHERE EmployeeId in(3,4,5,6,7)



Conclusion

In this blog, we explored MS SQL Not Operator along with examples. You also considered its performance implications in comparison with the Equality operators. We should try to use the Equality operator for better query performance. If we have any comments or questions, feel free to leave them in the comments below.

Mahira  khanna

Mahira khanna

I have the skills you need for you company blog, website, or other content materials

Comments



Report Response