Less than operator
compares two expressions. When you compare two nonnull expression and left hand
operand is less than right hand operator then the result is TRUE and right hand
operand is less than left hand operand 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 < ( Less Than ) Comparison Operator :
expression <
expression
Where expression is any
valid sql expression. Both expression must have implicitly convertible data
types. Less than operator returns boolean value TRUE or FALSE.
Examples of < ( Less Than ) Comparison Operator :
Example 1 : Using <
operator in where clause
SELECT
ProductName,UnitPrice
FROM products
WHERE unitprice >
18.00
Output
ProductName UnitPrice
Aniseed
Syrup 10.00
Konbu 6.00
Genen
Shouyu 15.50
Pavlova 17.45
Teatime
Chocolate Biscuits 9.20
Above query displays
rows having UnitPrice less than $18.
Example 2 : Use of
less than operator to compare values of table fields in select query
SELECT ProfitPerProduct
=
CASE
WHEN ActualPrice < SoldPrice THEN '$' + CONVERT(VARCHAR, (SoldPrice -
ActualPrice)) + ' profit on ' + ProductName
ELSE '$' +
CONVERT(VARCHAR, (ActualPrice - SoldPrice)) + ' loss on '
+
ProductName
END
FROM Products
Output
ProfitPerProduct
$21 profit on Chai
$2 loss on Chang
$3 profit on Aniseed
Syrup
$31 profit on Chef
Anton's Cajun Seasoning
$21.35 loss on Chef
Anton's Gumbo Mix
Above example describes
use of less than operator to compare SoldPrice and ActualPrice field values of
table.
Example 3 : Comparing
value of two variables
DECLARE @ActualPrice
INT, @SoldPrice int
SET @ActualPrice = 10
SET @SoldPrice = 20
IF(@ActualPrice < @SoldPrice)
BEGIN
PRINT 'Actual price is less than sold price.'
END
ELSE
BEGIN
PRINT 'Actual price is not less than sold price.'
END
Output
Actual price is less
than sold price.
Above example explains
use of less than comparison operator to compare two variables.
Example 4 : Use of less
than operator to compare variables in select query
DECLARE @ActualPrice
INT, @SoldPrice int
SET @ActualPrice = 10
SET @SoldPrice = 20
SELECT Message =
CASE
WHEN @ActualPrice < @SoldPrice THEN 'Actual price is less than sold price.'
ELSE
'Actual price is not less than sold price.'
END
Output
Message
Actual price is less
than sold price.
Above example describes
use of less than comparison operator in sql query. We can use less than
operator in select query to compare variable or fields of table.