Getting Started with the SQL Not Equal To Operator and Its Use Cases

This article will give an overview of the SQL Not Equal To operator and demonstrate how to use it in your queries requirements.

SQL Server has offered multiple operators for various comparisons like equal to, greater than, less than, not equal to, less than or equal to, greater than or equal to. All these operators have their respective symbols which we use in SQL statements. Here is the list of those symbols we use while doing any comparison between expressions. You might also observe that some of the operators have more than one symbol.

The example column gives a representation of each comparison operator used in the WHERE clause. Queries will return the name of employees by comparing their age based on the expression given in the WHERE clause.

SQL NOT Equal To Operator

This article is primarily focused on the Not Equal To operator, so we will focus on this only. SQL Not Equal To (<> or !=) operator tests one expression is not equal to another expression, we can also say the left-side expression is not equal to the right-side expression of the Not Equal To operator symbol. If you look at the above example for the Not Equal To operator, it means to list all employees whose age is Not Equal To 45.

We can see there are two different symbols for the Not Equal To operator shown in the comparison operator column of the above table. These both operators will return the same output but if you are using NULL either on the left-side or right-side expression or on both sides while using the != operator, you will be getting the output as NULL only whereas if you use <> with NULL at any place left or right or both places then output depends on ANSI_NULLS settings

Now, let me go ahead with a use case with help of an example. I want to search for all employees who are not working on contract or whose employment nature is not equal to 5. I will run the below statements to get the result of our requirements.

select name, [employment nature] FROM employee
where [employment nature] <> 5
Go
select name, [employment nature] FROM employee
where [employment nature] != 5
Go

You can see the output of the above two queries which is the same showing in the below image.

USE Cases of the Comparison Operator Not Equal To

Here I will show you a few use cases of Not Equal To operator using both symbols. Let’s explore them using several examples.

Display a Sales Report of All Employees except Those Working on a Specific Designation

Let’s take the first use case in which I will fetch the sales details of all employees except those who are working in the Manager position. I have joined two tables to get employee names & designation from the employee table and product & invoice details from another table Sales. Finally, I’ve applied a filter on the designation column with the Not Equal To operator to exclude all employees who are working at the Manager designation.

SELECT Name, Designation, s.Product, s.Price
FROM Employee AS e
JOIN Sales AS s
ON e.ID=s.PersonID
where Designation <> ‘Manager’

Here is the output of the above query. We can see employees and their sales details except those who are working as managers.

You will get the same output if you run the below query having another Not Equal To operator (!=).

SELECT Name, Designation, s.Product, s.Price
FROM Employee AS e
JOIN Sales AS s
ON e.ID=s.PersonID
where Designation != ‘Manager’

List all Employees Excluding from a Specific Location and Designation

Here, I will display a list of all employees with an exception to exclude those employees who belong to a specific city “London” and are working at the specific position like “Analyst”

SELECT Name, City, Age, Designation
FROM Employee
where City <> ‘London’
AND Designation <> ‘Analyst’

This is also a use case of using multiple comparison operators in a single query with AND operator. Below is the output of the above query. You can see all employees belong to the rest of all cities but not from London and none of the below employees are working at the Analyst position.

List all SQL Server Processes Excluding a Specific Wait Type and Transaction Status

This is a very useful example of using this comparison operator. If you want to see all SQL Server transactions which lastwaittype is not equal to MISCELLANEOUS and transaction status is not equal to the background from sysprocesses system object. Run the below statements to get the list of all SQL Server processes excluding the one which is running as a background process and their lastwaittype was MISCELLANEOUS.

SELECT *
FROM sysprocesses
where lastwaittype <> ‘MISCELLANEOUS’
AND Status <> ‘background’

Here is the output of the above query. You can modify this query with various other expressions. Such queries are very frequently used in analyzing SQL Server processes during performance troubleshooting.

List All SQL Server Logins Having or Not Having the Specific Privilege

There are various other ways to get the list of all logins having a specific privilege in SQL Server. Here, I will show you how to get the list of all logins having sysadmin server-level privilege using this Not Equal To operator. We all know that SQL Server stores details about all logins in the syslogins system table, so we will use this table to get our result. This system table has a column for each server-level privilege and if any login is granted access to that server-level privilege, its value gets changed from 0 to 1.

Here, I am showing you all logins having sysadmin rights on my SQL Server instance by running the below query. If you want to get the list of logins that is not part of sysadmin privilege, then you can modify your query in the WHERE clause from 0 to 1.

SELECT name, createdate, status, sysadmin
FROM syslogins
where sysadmin <> 0

You can see all logins in the below image and the value for these logins in the sysadmin column is set to 1.

As I explained above, you can also get the list of logins which has not been granted sysadmin access on this instance by just changing the right-side expression value to 1 of the Not Equal To the operator as given in the below query.

SELECT name, createdate, status, sysadmin
FROM syslogins
where sysadmin <> 1

You can use this Not Equal To operator to get various security-related audits as well. You can use this operator if you are looking for all logins except a specific one like sa.

SELECT name, createdate, status, sysadmin
FROM syslogins
where name <> ‘sa’

List All SQL Server Logins Excluding a Specific Create Date

Suppose you want to get a list of all SQL Server logins which were not created this year. We can get it by accessing createdate column of the syslogins system table. I have shown 3 examples for their respective scenario to list all logins which are not created at a specific time. The time could be the year, month, or date.

1. Have a look at the first query where I have used the Not Equal To operator on the createdate column to display all logins which are not created in 2021. You can change the year 2021 to your specific year as you wish.
2. Have a look at the second query given below where I have used logic to not display any login which is created in 3^rd month of any year. We can also get a similar result if we want to fetch a list of logins that are not created in a specific month.
3. Now, the third query is showing results about all SQL Server logins which are not created on a specific date of any year. I have passed 12^th as the date of any month and year to display all logins which are not created on that date.

Here are the queries.

--List all logins which are not created in 2021
SELECT name, createdate
FROM syslogins
where Year(createdate) <> 2021
GO
--List all logins which are not created in the 3rd month of any year
SELECT name, createdate
FROM syslogins
where Year(createdate) <> 2021
GO
--List all logins which are not created on the 12th day of any month and year
SELECT name, createdate
FROM syslogins
where Year(createdate) <> 2021
GO

The output of the above queries is shown in the below image. You can compare them and understand the result.

Conclusion

I have demonstrated an overview and various use cases of the SQL NOT Equal To (<> or !=) operator in this article. You can go ahead and apply it to your business requirements. Feel free to write your feedback in the comment section. Your comments encourage us to improve and deliver better content on regular basis.