Comparison Operators in SQL Server

In this SQL Server tutorial, I will explain the various comparison operators supported by SQL Server including the non-ISO standard operators. I will explain various operators along with their usage and examples.

SQL Server Comparison operators check if any two expressions are the same or not and return a boolean value. In SQL Server there are three types of boolean values: TRUE, FALSE, and UNKNOWN.

• List of comparison operators in SQL Server
• SQL server equal to
• SQL server equal to null
• SQL server equal to string
• SQL server not equal to
• SQL server not equal multiple values
• SQL Server greater than
• SQL Server greater than or equal to
• SQL server not greater than
• SQL server less than
• SQL server less than or equal to
• SQL server not less than
• SQL Server compare to datetime

List of comparison operators in SQL Server

The following is a list of all the comparison operators supported in SQL Server 2019.

Operator Symbol	Operator Name
=	Equal to
<>	Not equal to
!=	Not equal to(non-ISO standard
>	Greater than
>=	Greater than or equal to
!>	Not greater than(non-ISO standard)
<	Less than
<=	Less than or equal to
!<	Not less than(non-ISO standard)

SQL server equal to

The equal to (=) operator in SQL Server checks if the two expressions are the same or not. If the expressions are the same, the operator returns a TRUE value otherwise returns a FALSE value. For example:

USE BackupDatabase
GO
SELECT City FROM dbo.Students WHERE Student_id = 12

In the above example, the Student_id column values were compared with the provided Student ID i.e 12.

The column where Student ID was equal to 12 returned a TRUE value. As a result, we got the city column of the Student with the Student ID 12 i.e New York, USA.

Similarly, you can also use the equal to operator with an IF-ELSE statement. Hence, In this way, you can use the equal to operator in SQL Server.

Read How to delete a view in SQL Server

SQL server equal to null

In SQL Server, you cannot compare NULL values using any of the comparison operators. You have to use either of the IS NULL or the IS NOT NULL operators.

For example, suppose a table contains NULL values in some columns and we want to generate resultset according to those values. We can do something like this:

SELECT TOP (1000) [CustomerID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[EmailAddress]
      ,[Phone]
  FROM [BackupDatabase].[SalesLT].[Customer] WHERE MiddleName IS NULL

You can see that all the records having a NULL value in the MiddleName column were returned. In this way, you can use the IS NULL or the IS NOT NULL operator to compare two NULL values.

Read View in SQL Server

SQL server equal to string

Now let us compare two string values using the equal to operator. I have created a table containing the list of the top 10 companies and their respective countries in the world by market capitalization. Let us see how many countries are from the USA.

USE BackupDatabase
GO
SELECT * FROM dbo.RankCompany  WHERE Country = 'USA'

You can see that we got the list of all companies belonging to the United States of America by comparing the string “USA“. In this way, you can compare two strings using the equal to operator in SQL Server.

Read SQL Server bulk insert from CSV file

SQL server not equal to

The not equal to operator gives a result opposite to the equal to operator. This means if, in an expression, an equal to operator returns a TRUE, then not equal to operator will return a FALSE value and vice-versa.

There are two signs for using the not equal to operator:

1. <> : It is in accordance with the ISO standards.
2. != : It is not in accordance with the ISO standards.

You should prefer to use the <> symbol as it is in line with the ISO standards.

Let us see an example where I will use a not equal to operator. I will use the table that I used in the above example. This time I will retrieve the list of companies that do not belong to the United States of America:

USE BackupDatabase
GO
SELECT * FROM dbo.RankCompany  WHERE Country <> 'USA'

You can also write the query as:

USE BackupDatabase
GO
SELECT * FROM dbo.RankCompany  WHERE Country != 'USA'

The result will be the same:

You can see this time we got the companies which do not belong to the USA. In this way, you can use the not equal to operator in SQL Server.

Read SQL Server function return table

SQL server not equal multiple values

In this section, I will explain how you can compare multiple values using the not equal to operator in SQL Server.

For comparing multiple values, you have to use logical operators like AND, OR, etc. This is because multiple comparisons will result in multiple boolean outputs for which you have to use the logical operators.

For example, look at the below code:

SELECT TOP (1000) [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
      ,[ProductCategoryID]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE Color <> 'Red' AND Color !='Black'

In the above code, I have specified two expressions and used the AND operator to combine the expressions. As a result, only those items will be returned from this table whose color is not either of the two colors i.e Red or Black.

In this way, you can check multiple conditions using the not equal to operator in SQL Server.

Read Arithmetic operators in SQL Server

SQL Server greater than

The greater than operator(>) checks if the left side expression is greater than the right side expression or not. If the left side expression is greater, it returns a TRUE value, otherwise, it returns a False value.

For example, the given condition 45 > 33 returns a TRUE value and the condition 33 > 45 returns a FALSE value. Also, look at the below query:

SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE ListPrice > 1500

You can see that the product information of all the products having the List Price greater than 1500 is returned as a resultset. In this way, you can use the greater than operator in SQL Server.

Read SQL Operand data type real is invalid for modulo operator

SQL Server greater than or equal to

The greater than or equal to operator (>=) compares two expressions and returns a TRUE value if the left side expression is greater than or equal to the right-side expression.

For example, 45 >= 35 will return a TRUE value.

Also, 45 > = 45 will return a TRUE value.

Let us see how to use it in a table for retrieving a resultset.

SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE ListPrice >=539.99 ORDER BY ListPrice

You can see in the above image that the values in the ListPrice column, equal to 539.00 also got returned in the resultset.

Read How to test stored procedure in SQL Server

SQL server not greater than

When you compare two expressions using the not greater than operator(!>), you will get the result as TRUE if the left side expression is not greater than the right-side expression.

For example, 45 !> 55 will return a TRUE value.

Let us see how you can use this operator in a SELECT statement.

SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE ListPrice !> 539.99 ORDER BY ListPrice

You can see that we got the records where the ListPrice column values were not greater than 539.99 as specified in the SELECT statement. In this way, you can use the not greater than operator in SQL Server.

Read SQL Server scheduled stored procedure

SQL server less than

When you compare any two expressions using the less-than operator(<), the operator will return a TRUE value if the left side expression is smaller than the right-side expression.

For example, 34 < 57 will return a TRUE value.

Now let us see how to use this operator for retrieving specific results from a table.

SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE ListPrice <500 ORDER BY ListPrice DESC

In the above output, you can see that we got the records having the ListPrice column value less than the specified value. In this way, you can use the less-than operator in SQL Server.

Read SQL Server stored procedure case statement

SQL server less than or equal to

When you compare two expressions using the less than or equal to operator(<=), the operator returns a TRUE value if the expression on the left side of the operator is less than or equal to the right-side expression.

For example, 34 <= 35 will return a TRUE value.

Also, 34 <= 34 will return a TRUE value.

Now let us see how we can use this operator for retrieving some specific records from a table. Look at the T-SQL query below:

SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE ListPrice <= 337.22 ORDER BY ListPrice DESC

You can see that we got the records having a ListPrice less than or equal to the specified price. In this way, you can use the less than or equal to operator in SQL Server.

Read Alter Stored Procedure in SQL Server

SQL server not less than

The not less than operator(!<) compares two expressions and returns a TRUE value as a result if the left side expression is not less than the expression on the right side of the operator.

For example, 45 !< 35 will return a TRUE value.

Now let us see how to use this operator in a SELECT statement. Look at the T-SQL query below:

SELECT [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
  FROM [BackupDatabase].[SalesLT].[Product] WHERE ListPrice !< 337.22 ORDER BY ListPrice ASC

In this way, you can use the not less than operator in SQL Server.

Read SQL Server stored procedure if else

SQL Server compare to datetime

In this section, I will demonstrate how you can compare values of a DateTime data type in SQL Server using an example.

For comparing DateTime data type values, you have to use a specific format for the values recognized by SQL Server. For example, look at the T-SQL query below:

SELECT [SalesOrderID]
      ,[RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
      ,[Status]
      ,[SalesOrderNumber]
      ,[PurchaseOrderNumber]
      ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[TotalDue]
  FROM [BackupDatabase].[SalesLT].[SalesOrderHeader] WHERE OrderDate < '2009-06-1 00:00:00.000'

You can when I used a less than operator(<) all the DateTime values of the OrderDate before the specified DateTime value were returned in the resultset.

Similarly, you can use other comparison operators also to compare the DateTime values.

You may like the following SQL server tutorials:

• PostgreSQL vs SQL Server: Detailed Comparison
• SQL Server move database files
• SQL Server stored procedure output parameter
• SQL Server INSERT INTO SELECT
• Difference between table and view in SQL Server
• String or binary data would be truncated in SQL Server

Thus, you might have learned how to use various comparison operators for comparing various data types in SQL Server.

• List of comparison operators in SQL Server
• SQL server equal to
• SQL server equal to null
• SQL server equal to string
• SQL server not equal to
• SQL server not equal multiple values
• SQL Server greater than
• SQL Server greater than or equal to
• SQL server not greater than
• SQL server less than
• SQL server less than or equal to
• SQL server not less than
• SQL Server compare to datetime