SQL Comparison Operators (SQL Less than Operator, Equal, Not Equal operators)

Last updated on October 11th, 2022 at 01:27 am

SQL Comparison Operators

The SQL server comparison operators are useful to compare one expression with another expression using mathematical operators.
SQL Comparison operators return  true  or  false  based on the comparison.
The following are the list of different type of comparison operators available in SQL Server.

Consider X is the variable and the value assigned to X = 5 ;

The following are examples of the above SQL comparison operators.

DECLARE @X INT;
SET @X=5;
-- Less Than (<) Operator
SELECT CASE WHEN @X < 10 THEN 'TRUE' ELSE 'FALSE' END AS [Result];
-- returns TRUE

-- Greater Than (>) Operator
SELECT CASE WHEN @X > 10 THEN 'TRUE' ELSE 'FALSE' END AS [Result];
-- returns FALSE

-- Less Than or Equal To (<=) Operator
SELECT CASE WHEN @X <= 10 THEN 'TRUE' ELSE 'FALSE' END AS [Result];
-- returns TRUE

-- Greater Than or Equal To (>=) Operator
SELECT CASE WHEN @X >= 5 THEN 'TRUE' ELSE 'FALSE' END AS [Result];
-- returns TRUE

-- Equal (=) Operator
SELECT CASE WHEN @X = 5 THEN 'TRUE' ELSE 'FALSE' END AS [Result];
-- returns TRUE

-- Not Equal (!=) Operator
SELECT CASE WHEN @X != 5 THEN 'TRUE' ELSE 'FALSE' END AS [Result];
-- returns FALSE

-- Not Equal (!=) Operator
SELECT CASE WHEN @X <>5 THEN 'TRUE' ELSE 'FALSE' END AS [Result];
-- returns FALSE

Let’s try to understand each operator by creating a new “Employee” table by using the below SQL script in the database.

-- Create a new Employee table
CREATE TABLE Employee(EmpId int, EmpName varchar(100),Designation varchar(50),Location varchar(50))

-- Insert following records in the Employee table
INSERT INTO Employee values(1,'Shekh Ali','Software Engineer','Ballia'),
(2,'Rajat','Team Lead','Mumbai'),
(3,'Julie folden','Business Analyst','Poland'),
(4,'Amit Kumar','CA','Noida'),
(5,'Mark Adam','Sr. Software Engineer','America')

-- Select all the records from the Employee table
SELECT * FROM Employee;

Equal (=) Operator in SQL

In SQL Server, the equal (=) operator is used to checking whether the two expressions are equal or not. If it’s equal, then the condition will be true and it will return the matched records from the respective table.

Example of the equal operator in SQL

SELECT * FROM Employee WHERE EmpId = 1;

Once we execute the above SQL statement for the equal operator, it will return records where EmpId is equal to 1.

Not Equal (!=) Operator in SQL

In SQL, the not equal (!=) operator 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. This is just the opposite of the equal operator.

Example of the not equal Operator in SQL

SELECT * FROM Employee WHERE EmpId != 1;

Once we execute the above SQL statement for the not equal (!=) operator, it will return records where EmpId is not equal to 1.

SQL Not Equal (<>) Operators

In the SQL server, The not equal (<>) operator 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. Here the both  !=  and  <>  operators are not equal operators and will return the same result but != operator is not an ISO standard.

Example of not equal operator

SELECT * FROM Employee WHERE EmpId <> 1;

Once we execute the above SQL statement for the not equal (<>) operator, it will return records where EmpId is not equal to 1.

Greater Than (>) Operator in SQL

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

Example of greater than operator

SELECT * FROM Employee WHERE EmpId > 3;

Once we execute the above SQL statement for the greater than (>) operator, it will return records where EmpId is greater than 3

Less Than (<) Operator in SQL

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

Example of Less Than Operator in SQL

SELECT * FROM Employee WHERE EmpId < 2;

Once we execute the above SQL statement for the less than (<) operator, it will return records where EmpId is less than 2.

Greater Than or Equal To (>=) Operator in SQL

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

Example of Less Than Operators in SQL

SELECT * FROM Employee WHERE EmpId >= 3;

Once we execute the above SQL command for the greater than or equal (>=) operator, it will return records where EmpId is greater than or equal to 3.

Less Than or Equal To (<=) Operator in SQL

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

Example of less than or equal to (<=) operator in SQL

SELECT * FROM Employee WHERE EmpId <= 3;

Once we execute the above SQL statement for the less than or equal to (<=) operator, it will return records where EmpIds are less than or equal to 3.

SQL SERVER Not Less Than (!<) Operator

In SQL server, The not less than (!<) operator 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 of not less than (!<) operator in SQL server

SELECT * FROM Employee WHERE EmpId !< 3;

Once we execute the above SQL statement for not less than (!<) operator, it will return records where EmpIds are not less than 3.

Not Greater Than (!>) Operator in SQL

In SQL Server, The Not Greater Than (!>) operator 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 the condition becomes true and it will return the matched records.

Example of not greater than (!>) operator in SQL server

SELECT * FROM Employee WHERE EmpId !> 3;

Once we execute the above SQL statement for the not greater than (!>) operator, it will return records where EmpIds are not greater than 3.

Conclusion:

Here, In this article, we learned about SQL Comparison Operators with multiple examples. I hope you enjoyed this post and found it useful. In case you have any doubts, please post your feedback, questions, or comments below.

References: MSDN- Comparison Operators (Transact-SQL)

Recommended Articles

• Stored Procedure in SQL Server With Examples
• SQL Delete Cascade – SQL query to delete parent-child referenced rows
• CREATE, ALTER, AND DROP DATABASE IN SQL SERVER
• Primary Key Constraint In SQL Server
• View In SQL | Types Of Views In SQL Server
• SQL Server Trigger Update, Insert, Delete Examples

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024