Basic and Complex Uses of Not Equal Comparison Operator in T-SQL

This article is focused on the T-SQL Not Equal comparison operator (<>) and its uses in basic to slightly complicated SQL scripting tasks.

The article also highlights the importance of understanding the correct use of Not Equal comparative operator with expressions.

The Not Equal comparative operator is a very common operator used in T-SQL, however, it is important to understand how to use it effectively in different scenarios.

Basics of T-SQL Not Equal <> Comparison Operator

Let us first get familiar with the Not Equal T-SQL operator in the light of Microsoft documentation.

Microsoft Definition

A Not Equal To <> is a comparison operator which compares two expressions and returns a Boolean type result.

Boolean Type Result

Boolean type result is the type of result which returns True or False.

In the context of T-SQL under normal circumstances, Boolean type returns one of the three values:

1. True
2. False
3. NULL

Comparison Operator in SQL

A comparison operator in simple words compares two expressions and based on the comparison it tells us whether they are same or not.

Please note that Not Equal comparison operator (<>) is a binary operator which means an operator which compares two expressions.

Expression

According to Microsoft documentation, an expression is a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value.

Simple expressions can be a single constant, variable, column, or scalar function.

In other words, the simplest form of expression is a constant (fixed number) such as 1.

Example – Operator and Expressions

The simplest example of an expression with operators is an arithmetic operation such as 1+2.

Would you like to guess what is the operator and what is the expression in the above example?

Please refer to Not Equal comparison operator (<>) definition which states, “Not Equal comparison operator (<>) compares two expressions…” so + (addition) is the operator in this example and 1 (constant) and 2 (constant) are expressions.

Example – Comparison Operator and Expressions

Considering the example of arithmetic operation 1+2, simply replacing the arithmetic operator + with Not Equal <> comparison operator, it becomes a comparison operation in T-SQL.

For example, 1<>2 is a comparison operation with comparison operator <> between two expressions 1 and 2.

T-SQL Not Equal Operator “<>” vs “!=”

There is another way to express Not Equal comparison operator and that is by using exclamation mark followed by equal sign (!=).

Please note that both “<>” and “!=” represent Not Equal comparison operator, except that the <> sign complies with the ISO standard, whereas the Not Equal comparison operator != does not comply with the ISO standard.

Compatibility

According to Microsoft documentation, the Not Equal comparison operator (<>) is compatible with the following SQL database versions:

1. SQL Server 2008 and upper versions
2. Azure SQL Database
3. Azure SQL Data Warehouse
4. Parallel Data Warehouse

Syntax

expression (constant, variable, column etc.) <> expression (constant, variable, column etc.)

Pre-requisites

This article is basically written for beginners and there are some pre-requisites before basic and complex uses of Not Equal comparison operator (<>) are discussed.

T-SQL Familiarity

This article assumes that the readers have basic knowledge of  T-SQL and are capable of writing and running basic SQL scripts.

Setup Sample Database (SQLDevBlogV2)

This article requires the following sample database to run examples of basic and advanced uses of the Not Equal comparison operator (<>).

The sample database consists of three tables:

1. Article
2. Author
3. Category

Please set up the sample database named SQLDevBlogV2 by writing the following code:

-- Create sample database (SQLDevBlog)
CREATE DATABASE SQLDevBlogV2;
GO


USE SQLDevBlogV2;

-- (1) Create Author table in the sample database
CREATE TABLE Author (
  AuthorId INT PRIMARY KEY IDENTITY (1, 1)
 ,Name VARCHAR(40)
 ,RegistrationDate DATETIME2
 ,Notes VARCHAR(400)
)

-- (2) Create Article Category table in the sample database
CREATE TABLE Category (
  CategoryId INT PRIMARY KEY IDENTITY (1, 1)
 ,Name VARCHAR(50)
 ,Notes VARCHAR(400)
)

-- (3) Create Article table in the sample database
CREATE TABLE Article (
  ArticleId INT PRIMARY KEY IDENTITY (1, 1)
 ,CategoryId INT
 ,AuthorId INT
 ,Title VARCHAR(150)
 ,Published DATETIME2
 ,Notes VARCHAR(400)  
)

-- Adding foreign keys for author and article category
ALTER TABLE Article ADD CONSTRAINT FK_Category_CategoryId FOREIGN KEY (CategoryId) REFERENCES Category (CategoryId)
ALTER TABLE Article ADD CONSTRAINT FK_Author_AuthorId FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId)

GO


-- (5) Populating Author table
INSERT INTO Author (Name, RegistrationDate, Notes)
  VALUES ('Sam', '2017-01-01', 'Database Analyst'),
  ('Asif', '2017-01-02', 'Database and Business Intelligence Developer'),
  ('Sadaf', '2018-01-01', 'Database Analyst Programmer')

-- (6) Populating Category table
INSERT INTO Category (Name, Notes)
  VALUES ('Development', 'Articles about database development'),
  ('Testing', 'Database testing related articles'),
  ('DLM', 'Database lifecycle management')

-- (7) Populating Article 
INSERT INTO Article (CategoryId, AuthorId, Title, Published, Notes)
  VALUES (1, 1, 'Fundamentals of SQL Database Development', '02-01-2018', ''),
  (1, 2, 'Advanced Database Development', '02-01-2018', ''),
  (2, 3, 'All About Database Testing', '03-01-2018', '');
GO

Basic Uses of Not Equal Operator (<>)

In this section, we are going to explore some basic uses of the Not Equal comparison operator (<>) in T-SQL.

Please remember that the upcoming examples run against the sample database SQLDevBlogV2 which must be created if you would like to run the scripts of examples as mentioned in the pre-requisites.

Authors List Excluding the First Author

We have the Author table in the sample database which contains a list of all the registered authors.

Let us first view all the authors as follows:

-- View all authors
SELECT [AuthorId], [Name], [RegistrationDate] FROM dbo.Author

Now, if we want to view all the authors except the first one, we are going to use the Not Equal comparison operator (<>) as follows:

-- View all authors excluding first one
SELECT [AuthorId], [Name], [RegistrationDate]
FROM Author where AuthorId<>1

Here, the Not Equal comparison operator (<>) is used to filter the results based on the desired criteria.

Please note that in the WHERE clause of the SQL script (where AuthorId<>1), the AuthorId being a column that fulfills the definition of the expression and 1 is a constant, is also an expression so the use complements its syntax (expression <> expression).

Authors List Not Registered in 2017

Let us say we want to see the list of authors who were not registered in 2017.

This can be achieved by using the Not Equal comparison operator (<>) as follows:

--Authors list not registered in 2017.sql
SELECT [AuthorId], [Name], [RegistrationDate] FROM Author where Year(RegistrationDate)<>2017

Keeping the expression <> expression syntax in mind, this time expression on the left Year(RegistrationDate) is a function that returns year (number) compared with the expression on the right which is 2017 (number).

Please remember that we have already mentioned that the simplest form of expression is a number and in this example, we saw it in action.

Articles List Excluding Testing Category

If you are interested to see the list of all the articles except for the testing category stored in the sample database then you would require the Not Equal comparison operator (<>).

If you refer to the sample database diagram at the beginning of this article, it is obvious that the Article table is connected with Category through foreign key relationship, so, first of all, we need to see the categories with articles by using INNER JOIN as follows:

-- Articles with categories list
SELECT art.ArticleId, cat.Name AS Category, art.Title,art.Published FROM  Article art INNER JOIN Category cat
on art.CategoryId=cat.CategoryId

Now, we need to exclude the Testing category to see all the articles except the testing articles using the Not Equal comparison operator (<>) as follows:

-- All articles with categories excluding testing category
SELECT art.ArticleId, art.Title,cat.Name AS Category, art.Published FROM  Article art INNER JOIN Category cat
on art.CategoryId=cat.CategoryId
WHERE cat.Name<>'Testing'

Running the script produces the following output:

Complex Uses of T-SQL Not Equal Comparison Operator (<>)

We are now going to look at some slightly complex examples, which involve the use of the Not Equal comparison operator (<>).

Authors List Excluding Current Year Authors

A slightly complex scenario is when you are asked to get all the authors except those who were registered in the current year.

Since the current year is dynamic and it changes from year to year, this makes the script slightly complex as we cannot simply use a fixed year number with the comparison operator.

The solution is to get the current year from the current date and then get the list of all the authors who were not registered in the current year.

This is achieved by the following code:

-- Authors list excluding current year authors
SELECT a.AuthorId,a.Name,a.RegistrationDate FROM Author a
WHERE YEAR(a.RegistrationDate)<>YEAR(GETDATE())

The result is as follows:

Please note that since all the authors in the sample database were registered in 2017 and 2018 so all the authors are shown without any exclusion.

Authors List Excluding the Author(s) with Most Articles

The requirement is to view the authors’ list without the top author(s) makes the script slightly more complex.

We have to divide the problem into smaller parts:

1. Getting top author(s) (author with most articles)
2. Excluding top author(s) using the Not Equal comparison operator (<>)
3. Viewing all authors excluding top author(s)

The top author is an author who has more written articles than any other author.

Let us first add one more article for the author Sadaf to make her the top author by running the following script:

--Add one more article for Sadaf (AuthorId: 3)
INSERT INTO Article (CategoryId, AuthorId, Title, Published, Notes)
  VALUES (1, 3, 'Database Development with SQL Server Data Tools (SSDT)', '10-01-2019', '')

Now, build a script to view authors with the total number of articles ordered by authors with most articles first:

--Authors with total number of articles order by author with most articles first
SELECT ath.name as Author_NAME,count(*) as Total_Articles FROM Article art
INNER JOIN Author ath
on art.AuthorId=ath.AuthorId
GROUP BY ath.AuthorId,ath.Name
ORDER BY COUNT(*) DESC

The output of the script is as follows:

Now first we need to find out the most articles by an author and put the result into a variable and then we can list all the authors excluding the ones with most articles by using Not Equal comparison operator (<>) with the variable as follows:

--Authors list excluding the author(s) with most articles
DECLARE @MaxArticles INT=(SELECT TOP 1 count(*) as Total_Articles FROM Article art
INNER JOIN Author ath
on art.AuthorId=ath.AuthorId
GROUP BY ath.AuthorId,ath.Name
ORDER BY COUNT(*) DESC)

-- Authors excluding the author with most articles
SELECT ath.Name,ath.RegistrationDate,Count(*) as Total_Articles FROM Author ath
INNER JOIN Article art
ON ath.AuthorId=art.AuthorId
GROUP BY ath.Name,ath.RegistrationDate
HAVING COUNT(*)<>@MaxArticles

Running the script shows us the following result set:

Congratulations, you have successfully learned how to use the Not Equal comparison operator (<>) in simple and slightly complex scenarios.

Things to do

Now that you are familiar with basic and complex uses of the Not Equal comparison operator (<>), you can improve your skills further by trying the following things:

1. Please try to find out the list of all the articles excluding the articles which were written last year, which should be calculated dynamically.
2. Please try to create SQL script to get the list of all the authors who were not registered last year, where the last year should be calculated dynamically.
3. Please try finding out the list of all the articles, where the category was not development using the sample database created in this article.
4. Please try to build a SQL script to get all the categories excluding the top category (category with most articles written).
5. Please try creating a scalar-valued function and use it with the Not Equal comparison operator (<>) to explore further.