Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Operator RSS Feeds

< ( Less Than ) Comparison Operator

Posted By: Sarita Patel     Category: SQL Server     Views: 2665

This article describes use of < ( Less Than ) comparison operator with different examples.

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.

 

 

  
Share: 


Didn't find what you were looking for? Find more on &lt; ( Less Than ) Comparison Operator Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of < ( Less Than ) Comparison Operator is from United States.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!