SQL Comparison Operators |SQL Less than, Greater than, Equal, Not Equal operators

 Introduction  : In this SQL Operators article, we will learn all about SQL Comparison operators in details with multiple examples.

SQL Server Comparison Operators

The SQL 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 ;

OperatorDescriptionExample
<Less Than (<) OperatorX < 10 (returns true)
>Greater Than (>) OperatorX > 10 (returns false)
<=Less Than or Equal To (<=) OperatorX <= 10 (returns true)
>=Greater Than or Equal To (>=) OperatorX >=5 (returns true)
=Equal (=) OperatorX = 5 (returns true)
!=Not Equal (!=) OperatorX != 5 (returns false)
<>Not Equal (<>) OperatorX <>5 (return false)
SQL Comparison operators

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;
image-SQL Comparison operators output

Equal (=) Operator in SQL

In SQL Server, the equal (=) operator is used to check 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 equaled to 1.

image-equal operator in SQL
equal operator in SQL

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.

image -not equal (!=) operator in SQL output
not equal (!=) operator in SQL

SQL Not Equal (<>) Operators

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

not equal (<>) operator in SQL server
not equal (<>) operator in SQL server

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

greater than operators in SQl
greater than (>) operator in SQL server.

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.

less than (<) operators in SQL server
less than (<) operator in SQL server

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 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 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 equals to 3.

Greater than or equal (>=) operators in SQL server
Greater than or equal (>=) operator in SQL server

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 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 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 EmpId’s are less than or equal to 3.

image- less than or equal to (<=) operator in SQL output
less than or equal (<=) to the operator in SQL

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 EmpId’s are not less than 3.

image-not less than (!<) operator in sql output

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 not greater than (!>) operator, it will return records where EmpId’s are not greater than 3.

not greater than operator in SQL server
not greater than operator in SQL server

Recommended Articles

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

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 doubt, please post your feedback, questions, or comments below.

Thanks for visiting 🙂

Leave a Reply