SQL Comparison Operators to Refine Query Results

By: Joe Gavin   |   Updated: 2023-06-09   |   Comments   |   Related: > TSQL

Problem

I need a better understanding of different ways I can use SQL comparison operators like = or >, but I'm not exactly sure what all of the options are or how the SQL query syntax should be written.

Solution

SQL Comparison Operators are reserved words used in SQL statement clauses that compare two values. They are represented by mathematical symbols (=, >, <) and an exclamation point (!). They compare two values to see if they are equal to, greater than, less than, greater than or equal to, less than or equal to, or not equal to. The result of a comparison can be TRUE (1), FALSE (0), or unknown (NULL).

In this SQL tutorial, we'll step through an example using each operator and show the result sets.

The following table lists the operators and what they mean.

The following SQL statements will query the [AdventureWorks2019].[Sales].[vStoreWithDemographics] view in the free AdventureWorks2019 relational SQL database.

Examples of Standard SQL Comparison Operators

Equal To

We'll start by looking at all our stores that are 80,000 square feet in size. Select the name, square footage, and number of employees for those stores using the Equal To operator (=). Equal To only returns TRUE for records where both sides of the operator are equal to each other.

-- 80,000 square food stores
SELECT [Name],
       [SquareFeet],
       [NumberEmployees]
FROM [AdventureWorks2019].[Sales].[vStoreWithDemographics]
WHERE [SquareFeet] = 80000
ORDER BY [Name];
GO

All seven stores are equal to 80,000 square feet, as shown below.

Greater Than

Next, build on the first SQL SELECT query by adding to the WHERE clause. Select the same records with the stores that equal 80,000 square feet and have more than 99 employees using Greater Than (>) operator. Greater Than only returns TRUE for records where the left side of the operator is greater than the right side.

-- 80,000 square foot stores with more than 99 employees  
SELECT [Name],
       [SquareFeet],
       [NumberEmployees]
FROM [AdventureWorks2019].[Sales].[vStoreWithDemographics]
WHERE [SquareFeet] > 79000
      AND [NumberEmployees] > 99
ORDER BY [Name];
GO

Here, we've narrowed the result set to only one store, Nonskid Tire Company.

Greater Than or Equal To

To see the stores that have 99 or more employees, change from the Greater Than (>) operator to the Greater Than or Equal To (>=) operator. Similar to Greater Than, Greater Than or Equal To returns TRUE if the value on the left side of the operator is greater than the right side or equal to it.

-- 80,000 square foot stores with 99 or more employees
SELECT [Name],
       [SquareFeet],
       [NumberEmployees]
FROM [AdventureWorks2019].[Sales].[vStoreWithDemographics]
WHERE [SquareFeet] > 79000
      AND [NumberEmployees] >= 99
ORDER BY [Name];
GO

National Manufacturing has 99 employees and has been included in the result set.

Less Than

Going the other way, find the stores with less than 100 employees with the Less Than (<) operator. Less Than returns TRUE if the value on the left side of the operator is less than the value on the right.

-- 80,000 square foot stores with less than 100 employees
SELECT [Name],
       [SquareFeet],
       [NumberEmployees]
FROM [AdventureWorks2019].[Sales].[vStoreWithDemographics]
WHERE [SquareFeet] > 79000
      AND [NumberEmployees] < 100
ORDER BY [Name];
GO

Here are the six stores with less than 100 employees.

Less Than or Equal To

Change the Less Than (<) to the Less Than or Equal To (<=) operator to include the stores with 100 or fewer employees. Less Than or Equal To returns TRUE if the value on the left side of the operator is less than the right side or is equal to it.

-- 80,000 square foot stores with 100 or less employees
SELECT [Name],
       [SquareFeet],
       [NumberEmployees]
FROM [AdventureWorks2019].[Sales].[vStoreWithDemographics]
WHERE [SquareFeet] > 79000
      AND [NumberEmployees] <= 100
ORDER BY [Name];
GO

Nonskid Tire Company, with 100 employees, has been added to the results.

Not Equal To

The Not Equal To (<>) operator returns values that are not equal to another. Here, we look for stores with more than or less than 100 employees but not precisely 100.

-- 80,000 square foot stores that do not have 100 employees
SELECT [Name],
       [SquareFeet],
       [NumberEmployees]
FROM [AdventureWorks2019].[Sales].[vStoreWithDemographics]
WHERE [SquareFeet] > 79000
      AND [NumberEmployees] <> 100
ORDER BY [Name];
GO 

The Nonskid Tire Company is no longer on the list.

Examples of Non-Standard SQL Comparison Operators

So far, we've seen Comparison Operators that are part of the ISO Standard.

The following SELECT statements have functionally equivalent operators to those shown above, but are not part of the ISO standard.

Not Equal To

Not Equal To (!=) is functionally equivalent to (<>).

- 80,000 square foot stores that do not have 100 employees
SELECT [Name],
       [SquareFeet],
       [NumberEmployees]
FROM [AdventureWorks2019].[Sales].[vStoreWithDemographics]
WHERE [SquareFeet] > 79000
      AND [NumberEmployees] != 100
ORDER BY [Name];
GO

Here are the stores with more than, or less than, but not 100 employees that we saw above.

Not Greater Than

The Not Greater Than (!>) operator is functionally equivalent to Less Than or Equal To (<=).

-- 80,000 square foot stores that do not have 100 or more employees
SELECT [Name],
       [SquareFeet],
       [NumberEmployees]
FROM [AdventureWorks2019].[Sales].[vStoreWithDemographics]
WHERE [SquareFeet] > 79000
      AND [NumberEmployees] !> 100
ORDER BY [Name];
GO

These stores have 100 employees or less.

Not Less Than

And Not Less Than (!<) is functionally equivalent to Greater Than or Equal To (>=).

-- 80,000 square foot stores that do not have 100 employees
SELECT [Name],
       [SquareFeet],
       [NumberEmployees]
FROM [AdventureWorks2019].[Sales].[vStoreWithDemographics]
WHERE [SquareFeet] > 79000
      AND [NumberEmployees] !< 100
ORDER BY [Name];
GO

This store has 100 employees or more.

Next Steps

Here are additional tips with more examples of using Comparison Operators.

• Learn about SQL NULL values and how SQL Server handles NULL in Queries
• Deciding between COALESCE and ISNULL in Microsoft SQL Server
• SQL Server NOT Equal Operators
• Learn how to write SQL Queries with AND, OR, and NOT Logical Operators
• SQL String functions in SQL Server, Oracle and PostgreSQL
• SQL Server DAX Data Types and Operators
• R Functions and Operators in SQL Server
• A T-SQL Model for Contrasting Two Different Sets of Measurement
• Compare SQL Server Features - Decimal vs Numeric, Timestamp vs Rowversion, Unique Index vs Unique Constraint
• SQL Server Profiler Filters Explained
• Differences Between SQL Server Temp Tables, Table Variables, Subqueries, Derived Tables, CTEs and Physical Tables
• SQL Server Filtered Indexes What They Are, How to Use and Performance Advantages
• PowerShell for the DBA - If Else and Switch Statement

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips