Back to: SQL Server Tutorial For Beginners and Professionals

Comparison Operators in SQL Server

In this article we will learn what are the comparison operators in SQL server and uses of comparison operators in SQL server and different type of comparison operators like equal (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), not equal (<>), etc. in SQL server with examples

A comparison operator is a mathematical symbol which is used to compare two values. That means comparison operators are used to comparing one expression with another expression. The result of a comparison can be TRUE, FALSE, or UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).

We can use comparison operators on all the expressions except expressions of type text, ntext or image.

The different types of comparison operators that are available in SQL Server are as follows: 

1. Equal (=) Operator
2. Not Equal (!= or <>) Operator
3. Greater Than (>) Operator
4. Less Than (<) Operator
5. Greater Than or Equal To (>=) Operator
6. Less Than or Equal To (<=) Operator
7. Not Greater Than (!<) Operator
8. Not Less Than (!>) Operator

Use below script to create database EmployeeDB, Employee table and populate Employee table with some test data.

--Create database EmployeeDB
Create database EmployeeDB
Go

Use EmployeeDB
Go

-- Create Employee table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  EmailID VARCHAR(100),
  Gender VARCHAR(100),
  Department VARCHAR(100),
  Salary INT,
  Age INT,
  CITY VARCHAR(100)
)
GO

--Insert some test data into Employee table
INSERT INTO Employee VALUES('Pranaya','Pranaya@g.com','Male', 'IT', 25000, 30,'Mumbai')
INSERT INTO Employee VALUES('Tarun','Tarun@g.com','Male', 'Payroll', 30000, 27,'Odisha')
INSERT INTO Employee VALUES('Priyanka','Priyanka@g.com','Female', 'IT', 27000, 25,'Bangalore')
INSERT INTO Employee VALUES('Preety','Preety@g.com','Female', 'HR', 35000, 26,'Bangalore')
INSERT INTO Employee VALUES('Ramesh','Ramesh@g.com','Male','IT', 26000, 27,'Mumbai')
INSERT INTO Employee VALUES('Pramod','Pramod@g.com','Male','HR', 29000, 28,'Odisha')
INSERT INTO Employee VALUES('Anurag','Anurag@g.com','Male', 'Payroll', 27000, 26,'Odisha')
INSERT INTO Employee VALUES('Hina','Hina@g.com','Female','HR', 26000, 30,'Mumbai')
INSERT INTO Employee VALUES('Sambit','Sambit@g.com','Male','Payroll', 30000, 25,'Odisha')
INSERT INTO Employee VALUES('Manoj','Manoj@g.com','Male','HR', 30000, 28,'Odisha')
INSERT INTO Employee VALUES('Sara',' Sara@g.com','Female', 'Payroll', 28000, 27,'Mumbai')
INSERT INTO Employee VALUES('Lima','Lima@g.com','Female','HR', 30000, 30,'Bangalore')
INSERT INTO Employee VALUES('Dipak','Dipak@g.com','Male','Payroll', 32000, 25,'Bangalore')
GO

Fetch the Records

Select * From Employee

Equal (=) Operator

The equal (=) operator in SQL Server is used to check whether the two expressions are equal or not. If it’s equal then the condition becomes true and it will return the matched records.

Example:

SELECT * FROM Employee WHERE CITY = ‘Mumbai’

The above SQL statement will return records from the Employee table where the CITY is Mumbai.

Following is the output of the above SQL query.

Not Equal (!=) Operator

The not equal (!=) operator in SQL Server is used to check whether the two expressions are equal or not. If it’s not equal then the condition becomes true and it will return the not matched records.

Example:

SELECT * FROM Employee WHERE CITY != ‘Mumbai’

The above SQL statement will return records from the Employee table where the CITY is not equal to Mumbai.

Following is the output of the above SQL query.

Not Equal (<>) Operator

The not equal (<>) operator in SQL Server is used to check whether the two expressions are equal or not. If it’s not equal then the condition becomes true and it will return the not matched records.

Both != and <> operators are not equal operators and will return the same result but != operator is not an ISO standard. 

Example:

SELECT * FROM Employee WHERE CITY <> ‘Mumbai’

The above SQL statement will return records from the Employee table where the CITY is not equal to Mumbai.

Following is the output of the above SQL query.

Greater Than (>) Operator

The Greater Than (>) operator in SQL Server is used to check whether the left-hand operator is higher than the right-hand operator or not. If left-hand operator higher than right-hand operator then the condition becomes true and it will return the matched records.

Example:

SELECT * FROM Employee WHERE ID > 5

The above SQL statement will return records from the Employee table where the ID is greater than 5.

Following is the output of the above SQL query.

Less Than (<) Operator

The less than (<) operator in SQL Server is used to check whether the left-hand operator is lower than the right-hand operator or not. If the left-hand operator lower than the right-hand operator then condition becomes true and it will return the matched records.

Example:

SELECT * FROM Employee WHERE ID < 5

The above SQL statement will return records from the Employee table where the ID is less than 5.

Following is the output of the above SQL query.

Greater Than or Equal To (>=) Operator

The Greater than or Equal To (>=) operator in SQL Server is used to check whether the left-hand operator is higher than or equals to the right-hand operator or not. If the left-hand operator is higher than or equals to the right-hand operator then the condition becomes true and it will return the matched records.

Example:

SELECT * FROM Employee WHERE Salary >= 29000

The above SQL statement will return records from the Employee table where the Salary is greater than or equal to 29000.

Following is the output of the above SQL query.

Less Than or Equal To (<=) Operator

The Less Than or Equal To (<=) operator in SQL Server is used to check whether the left-hand operator is lower than or equals to the right-hand operator or not. If the left-hand operator is lower than or equals to the right-hand operator then the condition becomes true and it will return the matched records.

Example:

SELECT * FROM Employee WHERE Salary <= 29000

The above SQL statement will return records from the Employee table where the Salary is less than or equal to 29000.

Following is the output of the above SQL query.

Not Greater Than (!>) Operator

The Not Greater Than (!>) operator in SQL Server is used to check whether the left-hand operator is not greater than the right-hand operator or not. If the left-hand operator is not greater than the right-hand operator then condition becomes true and it will return the matched records.

Example:

SELECT * FROM Employee WHERE ID !> 5

The above SQL statement will return records from the Employee table where the employee ID is not greater than 5.

Following is the output of the above SQL query.

Not Less Than (!<) Operator

The Not Less Than (!<) operator in SQL Server is used to check whether the left-hand operator is not less than the right-hand operator or not. If the left-hand operator is not less than the right-hand operator then the condition becomes true and it will return the matched records.

Example:

SELECT * FROM Employee WHERE ID !< 5

The above SQL statement will return records from the Employee table where the employee ID is not less than 5.

Following is the output of the above SQL query.

In the next article, I will discuss the Logical Operators in SQL Server.

SUMMARY

In this article, I try to explain the Comparison Operator in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.