Not Equal to operator compares two expressions. When you compare two nonnull expression and left hand operand is not equal to right hand operator then the result is TRUE and both operands are equal then result is FALSE. When you set ANSI_NULLS ON and any of the expressions are null then the result is null. When you set ANSI_NULLS OFF and any of the expressions are null then the result set is FALSE. But if both expressions are null then the result set is TRUE.
Syntax of <> or != ( Not Equal To ) Comparison Operator :
expression <> expression
Where expression is any valid sql expression. Both expression must have implicitly convertible data types. Not Equal to operator returns boolean value TRUE or FALSE.
Examples of <> or != ( Not Equal To ) Comparison Operator :
Example 1 : Use of not equal to operator to compare values of table fields.
SELECT ProfitPerProduct =
CASE
WHEN SoldPrice <> ActualPrice THEN 'SoldPrice and ActualPrice are different of ' + ProductName
ELSE 'SoldPrice and ActualPrice can be equal of ' + ProductName
END
FROM Products
Output
SoldPrice and ActualPrice are different of Chef Anton's Cajun Seasoning
SoldPrice and ActualPrice are different of Chef Anton's Gumbo Mix
SoldPrice and ActualPrice are different of Grandma's Boysenberry Spread
SoldPrice and ActualPrice are different of Uncle Bob's Organic Dried Pears
SoldPrice and ActualPrice are different of Northwoods Cranberry Sauce
SoldPrice and ActualPrice are different of Mishi Kobe Niku
SoldPrice and ActualPrice can be equal of Ikura
Above example describes use of not equal to operator to compare SoldPrice and ActualPrice field values of table.
Example 2 : Comparing value of two variables.
DECLARE @Unit1 INT, @Unit2 int
SET @Unit1 = 15
SET @Unit2 = 35
IF(@Unit1 <> @Unit2)
BEGIN
PRINT 'Unit1 is not equal to Unit2.'
END
ELSE
BEGIN
PRINT 'Unit1 is can be greater, lesser or equal to Unit2.'
END
Output
Unit1 is not equal to Unit2.
Above example explains use of not equal to comparison operator to compare two variables.
Example 3 : Use of less than or equal to operator in select query.
DECLARE @Unit1 INT, @Unit2 INT
SET @Unit1 = 15
SET @Unit2 = 35
SELECT Message =
CASE
WHEN @Unit1 <> @Unit2 THEN 'Unit1 is not equal to Unit2.'
ELSE 'Unit1 is can be greater, lesser or equal to Unit2.'
END
Output
Message
Unit1 is not equal to Unit2.
Above example describes use of not equal to comparison operator in sql query. We can use not equal to operator in select query to compare variable or fields of table.