# SQL Comparison Operators to Refine Query Results

By:   |   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.

Operator Meaning
= Equal to
=> = Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
!= Not equal to (not ISO standard)
!> Not greater than (not ISO standard)
!< Not less than (not ISO standard)

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]
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]
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]
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]
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]
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]
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]
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]
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]
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.