SQL Comparison Operators

The SQL Server comparison operators are useful for comparing two operands or values against each other to filter the result set. In general, we use them for comparing the column values against a valid expression. The result will be TRUE or FALSE, and based on the result, we get a specific set of records.

In SQL Server, we can use these comparison operators (=, <>, >, <, <=, and >=) inside the WHERE clause or HAVING clause to filter the data. It means the SELECT statement will only retrieve records that meet the given condition on the right side of the comparison. Apart from that, you can use them in JOINS to perform different kinds of joins.

All the above-mentioned comparison operators can be used in the SQL SELECT statement to retrieve records in a table, the UPDATE command to update existing values, and the DELETE statement to remove a specific record. So, understanding this article is very important and crucial for your further learning. For instance, retrieve the customer records from the table who reside in France. Here, we used the equal to (=) operator.

SELECT * FROM Customers
WHERE Country = 'France'

Available SQL Comparison Operators

As we mentioned earlier, the SQL Server comparison operators are useful for comparing two or more expressions and returning a Boolean TRUE or FALSE. So, if the expression is evaluated to TRUE, it returns the rows matching that expression; otherwise, it returns nothing (empty result set). Apart from that, it also returns UNKNOWN if there is any NULL record in either of the expressions (left or right).

The following table shows the list of available comparison operators, which includes both the ISO standard and non- ISO standard ones. Among them, equal to is the most commonly used comparison operator in both WHERE and HAVING clauses.

SQL Comparison Operators	Description	Example
= Equal to	Check two operands are equal	Age = 30
> Greater than	Check whether the left operand is greater than the right.	Age > 25
>= Greater than or Equal to	Check whether the left operand is greater than or equal to the right.	Age >= 28
< Less than	Check whether the left operand is less than the right.	Age < 18
<= Less than or Equal to	Check whether the left operand is less than or equal to the right.	Age <= 50
<> NOT Equal to	Check whether the left operand is not equal to the right.	Age <> 60
!= Not Equal to (Not ISO Standard)	Same as <>	Age != 60
!> Not Greater than (Not ISO Standard)	Check whether the left operand is not greater than the right.	Age !> 18
!< Not Less than(Not ISO Standard)	Check whether the left operand is not less than the right.	Age !< 60

SQL Comparison Operators Examples

The following are the most common and simple examples of Comparison operators and the query explanation. For this, we use the AdventureWorks 2022 and AdventureWorksDW 2022 databases.

Equal To (=) Operator

In SQL Server, the Equal to operator is one of the most commonly used comparison operators. The = operator checks whether the two expressions (left and right) are equal or not and returns a Boolean TRUE or FALSE. If TRUE, then it will display the records whose column value is exactly equal to the given expression.

In the query below, we used the = operator to check whether the StateProvinceName is equal to ‘New York’. It means the following = comparison operator query will find all the Customers present in the DimCustomers table who reside in New York. Here, there are only three customers in the Adventure Works table.

SELECT City, [FirstName],[LastName],[BirthDate]
      ,[Gender]
      ,[EmailAddress]
      ,[YearlyIncome]
FROM [DimGeography] JOIN DimCustomer ON
DimGeography.GeographyKey = DimCustomer.GeographyKey
WHERE [StateProvinceName] = 'New York'

Similarly, if you want the Employees whose department is HR. You can write the following query, and it will display the employees belonging to the HR department.

SELECT * FROM Employee WHERE Department = 'HR'

SQL Comparison Operators - Equal and Not equal to

Comparison of NULL and NON-NULL values

While working with the SQL Server comparison operators, NULL records will give strange results. So, you should be careful of working with NULL values. Generally, people are tempted to use = and <> comparison operators to check whether a column is equal to NULL or not. When you compare with NULLs, the result will always depend on the server’s ANSI_NULLS settings.

NULL values are UNKNOWN and cannot be compared with any other value, like 0 or blank. If you explicitly set the ANSI_NULLS ON before the query, it returns UNKNOWN.
In general, the server’s default settings are ANSI_NULLS OFF. It returns NULL for comparing NULL against NULL, and if you compare NULL with a non-null value, it returns FALSE (so an empty result set).

You can try the following queries to understand the above two points. The first query returns an empty row set.

SELECT * FROM [DimCustomer]
WHERE MiddleName = NULL

The below query returns all the customers in the table whose Middle name is NULL.

SET ANSI_NULLS OFF

SELECT * FROM [DimCustomer]
WHERE MiddleName = NULL

Alternatively, you can use the IS NULL operator, which is better and produce accurate result.

SELECT * FROM [DimCustomer]
WHERE MiddleName IS NULL

Greater Than (>) Operator

The > comparison operator compares the two operands, and if the left operand is greater than the right, it returns TRUE. Otherwise, it returns Boolean FALSE. It meansthe Greater than operator will display the rows whose column value is higher than the given expression. For example, the following query uses the SalesTerritory table.

Here, the SQL Server comparison operator checks whether the SalesYTD (year to Date) is greater than 6000000. If True, print the Territory name, group, and sales information. If the expression evaluates to FALSE, it returns an empty result set.

SELECT [TerritoryID],[Name],[CountryRegionCode]
      ,[Group],[SalesYTD],[SalesLastYear]
FROM [Sales].[SalesTerritory]
WHERE SalesYTD > 6000000

Similarly, you can try the following query to find the sales territories whose current year sales are greater than last year’s sales.

SELECT [TerritoryID],[Name],[CountryRegionCode]
      ,[Group],[SalesYTD],[SalesLastYear]
FROM [Sales].[SalesTerritory]
WHERE SalesYTD > SalesLastYear

Less Than (<) Operator

The SQL Server < comparison operator tests whether the left operand is less than (lower value) the right operand and returns Boolean TRUE or FALSE. If it is TRUE, it returns the result set. For instance, finding the products or regions that have fewer sales this year compared to last year.

The following Less than comparison Operators query checks whether the sales YTD are less than the sales last year. Next, it will display the records whose SalesYTD column value is Less than SalesLastYear.

SELECT [TerritoryID],[Name],[CountryRegionCode]
      ,[Group],[SalesYTD],[SalesLastYear]
FROM [Sales].[SalesTerritory]
WHERE SalesYTD < SalesLastYear
Similarly, you can use the following query to show the salesperson's information.
SELECT [TerritoryID],[SalesQuota],[Bonus]
    ,[CommissionPct],[SalesYTD],[SalesLastYear]
FROM [Sales].[SalesPerson]
WHERE SalesYTD < SalesLastYear

Greater Than or Equal to >=

The SQL greater than or equal to (>=) comparison operator tests whether the left operand is higher than or equal to the right operand. If it is higher, it returns TRUE and displays the result set. Otherwise, it returns FALSE and displays an empty table. For instance, finding whether the user’s age is greater than or equal to 18.

SELECT * FROM people WHERE age >= 18

The following query will find the sales information from the salesperson’s table whose sales quota is greater than or equal to 300000.

SELECT [TerritoryID],[SalesQuota],[Bonus]
    ,[CommissionPct],[SalesYTD],[SalesLastYear]
FROM [Sales].[SalesPerson]
WHERE SalesQuota >= 300000

Similarly, the below query will return the purchase order details of the products whose order quantity is greater than or equal to 6000.

SELECT [PurchaseOrderID],[OrderQty],[ProductID]
  ,[UnitPrice],[LineTotal],[ReceivedQty],[StockedQty]
FROM [Purchasing].[PurchaseOrderDetail]
WHERE OrderQty >= 6000

Less Than or Equal to (<=) Operator

The SQL Less Than or Equal to (<=) comparison operator will test whether the left operand value is lower than the right operand. If TRUE, the query will display the records; otherwise, it displays an empty set. For instance, finding the employees whose age is less than are equal to 60 for retirement.

The following query will find all the Employee details whose sick leave hours are less than or equal to 20.

SELECT [LoginID],[JobTitle],[BirthDate],[MaritalStatus]
      ,[Gender],[HireDate],[SickLeaveHours]
FROM [HumanResources].[Employee]
WHERE SickLeaveHours <= 20

Similarly, the below example finds the salesperson’s information whose bonus is <= 500.

SELECT [TerritoryID],[SalesQuota],[Bonus]
    ,[CommissionPct],[SalesYTD],[SalesLastYear]
FROM [Sales].[SalesPerson]
WHERE Bonus <= 500

Not Equal to (<>) Operator

While performing the comparison, the SQL <> not equal to operator acts exactly opposite to the =. The <> operator compares two operands and tests whether the left operand is not equal to the right. If both are not equal, it returns TRUE, and so does the result set. Otherwise, it returns FALSE and an empty set. For instance, finding the sales of all products except a particular color, sales information excluding the Europe region, etc.

The below query will find the sales of each territory along with other information, whose territory group is not equal to North America.

SELECT [TerritoryID],[Name],[CountryRegionCode]
      ,[Group],[SalesYTD],[SalesLastYear]
FROM [Sales].[SalesTerritory]
WHERE [Group] <> 'North America'

Similarly, you can use this not equal to comparison operator (<>) to compare the column values against the numeric values. For instance, the below query displays salespersons’ information whose quota is not equal to 250000.

SELECT [BusinessEntityID],[TerritoryID],[SalesQuota]
      ,[Bonus],[CommissionPct],[SalesYTD],[SalesLastYear]
FROM [Sales].[SalesPerson]
WHERE SalesQuota <> 250000

SQL Comparison Operators - Greater than, less than or equal to

The following three the non standard ISO operators to perform the comparison.

Not Equal To (!=) Operator

The SQL Not Equal to (!=) comparison operator is the same as the above-mentioned <> and returns the same result. However, != is not a standard ISO operator.

To demonstrate this we use the DW database with a simple joins. The below query will display the sum of the sales information for each country. The != (not equal to) operator in the WHERE clause will filter the United States country from displaying. So, it display total sales for each country except USA.

SELECT [SalesTerritoryRegion],[SalesTerritoryCountry],
    SUM([OrderQuantity]) AS Orders,
    SUM(TotalProductCost) AS Cost,
    SUM([SalesAmount]) AS Sales
FROM DimSalesTerritory JOIN [FactInternetSales]
ON DimSalesTerritory.SalesTerritoryKey = FactInternetSales.SalesTerritoryKey
WHERE [SalesTerritoryCountry] != 'United States'
GROUP BY[SalesTerritoryRegion],[SalesTerritoryCountry]

Not Greater Than (!>) Operator

The SQL Server Not Greater Than Comparison Operator (!>) acts exactly opposite to the >. It compares two operands and checks whether the left operand is not greater than the right. If TRUE, display those rows. Here, it returns the same results as the less than or equal to (<=) operator.

The following query joins the Fact Internet Sales table and the Dim Products of the Adventure Works DW 2022 database. Next, it will display the products whose sum of the order quantity is not greater than 45. It means products with orders less than or equal to 45.

SELECT [EnglishProductName], Color,
    SUM([OrderQuantity]) AS Orders,
    SUM(DealerPrice) AS DealerCost
    ,SUM([SalesAmount]) AS Sales
  FROM DimProduct JOIN [FactInternetSales]
  ON DimProduct.ProductKey = FactInternetSales.ProductKey
  GROUP BY [EnglishProductName], Color
  HAVINg SUM(OrderQuantity) !> 45

Not Less Than ( !<) Operator

The SQL Server Not Less than Operator (!<) is exactly opposite to the < comparison operator. While comparing two operands, it tests whether the left operand is not less than the right operand. If TRUE, display those rows. Here, it returns the same results as the greater than or equal to (>=) operator.

The below shown query will display all the products with the product name and color group, and the sum of the sales and orders. Here, the comparison operator in the WHERE clause checks whether the sum of the order quantity is not less than 2200. SO, it displays the records whose total orders are greater than or equal to 2200.

SELECT [EnglishProductName], Color,
    SUM([OrderQuantity]) AS Orders,
    SUM(DealerPrice) AS DealerCost
    ,SUM([SalesAmount]) AS Sales
  FROM DimProduct JOIN [FactInternetSales]
  ON DimProduct.ProductKey = FactInternetSales.ProductKey
  GROUP BY [EnglishProductName], Color
  HAVINg SUM(OrderQuantity) !< 2200

Multiple SQL Comparison Operators using AND OR

In Server, you can use the Logical operators such as AND, OR< and NOT to combine multiple comparison operators to check the table further. It is very helpful for displaying a precise result set to the end user.

The following query will display all the customers in the DimCustomer table whose Education is Bachelor’s and Marital Status of Single, and Gender is not equal to M (meaning only Females), and income is greater than 120000, and the birth year is greater than 1970.

Here, we used multiple comparison operators to give you an idea of how we can use them. I suggest you try more combinations to better understand them.

SELECT [FirstName],[LastName],[BirthDate],[MaritalStatus]
      ,[Gender],[EmailAddress],[YearlyIncome],[EnglishEducation]
FROM [DimCustomer]
WHERE EnglishEducation = 'Bachelors' AND MaritalStatus = 'S' AND Gender <> 'M'
  AND YearlyIncome > 120000 AND YEAR(BirthDate) > 1970

SQL Comparison Operators in UPDATE and DELETE

In all the above queries, we used the comparison operators to filter the data returned by the SELECT statement. However, you can use them in the UPDATE and DELETE commands to restrict the data to be updated or deleted.

For instance, the below SQL query will update the employee’s salary by 20% (hike) whose sales orders are greater than 2000.

UPDATE Employee 
SET YearlyIncome = YearlyIncome + YearlyIncome * 0.20
WHERE Sales > 2000

Similarly, you can use the comparison operators to perform deletion on a set of values instead of all.

DELETE FROM [Employee]
WHERE [Status] = 'Terminated'

SQL Comparison Operators Best Practices

Using comparison operators in the WHERE clause helps to filter the result set and see the rows that are only required.
These comparison operators can handle numeric, text, and date fields for data filtering.
Always use the = operator for exact comparison.
Use logical AND, OR, and NOT operators to combine multiple comparison operators for more complex queries.
While testing multiple conditions, please check whether the range values starts from least.
Always prefer the index columns for the comparison to improve the query performance.
Keep a check on case sensitivity while comparing the text information.
Don’t use SQL comparison operators against the NULL values. Please use IS NULL or IS NOT NULL to compare NULL values against regular ones.
Use the BETWEEN operator to display records in a range. Instead of writing the combination of <= and >=, use a simple BETWEEN operator.
Similarly, use the IN operator when possible.
While testing multiple conditions, use parentheses.