Greater than operator
compares two expressions. When you compare two nonnull expression and left hand
operand is greater than right hand operator then the result is TRUE and right
hand operand is greater 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 > ( Greater Than ) Comparison Operator :
expression >
expression
Where expression is any
valid sql expression. Both expression must have implicitly convertible data
types. Greater than operator returns boolean value TRUE or FALSE.
Examples of > ( Greater Than ) Comparison Operator :
Example 1 : Using > operator in where clause
SELECT
ProductName,UnitPrice
FROM products
WHERE unitprice >
18.00
Output
ProductName UnitPrice
Chang
19.00
Chef Anton's Cajun
Seasoning 22.00
Chef Anton's Gumbo
Mix
21.35
Grandma's Boysenberry
Spread 25.00
Uncle Bob's Organic
Dried Pears 30.00
Above query displays
rows having UnitPrice greater than $18.
Example 2 : Use of
greater than operator to compare variables in select query
DECLARE @ActualPrice
INT, @SoldPrice int
SET @ActualPrice = 10
SET @SoldPrice = 20
SELECT Message =
CASE
WHEN @SoldPrice > @ActualPrice THEN 'Sold price is greater than actual price.'
ELSE 'Sold
price is not greater than actual price.'
END
Output
Message
Sold price is greater
than actual price.
Above example describes
use of greater than comparison operator in sql query. We can use greater than
operator in select query to compare variable or fields of table.
Example 3 : Use of greater than operator to compare values of table fields in select query
SELECT ProfitPerProduct =
CASE
WHEN SoldPrice > ActualPrice 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 greater than operator to compare SoldPrice and ActualPrice field values of table.
Example 4 : Comparing value of two variables
DECLARE @ActualPrice INT, @SoldPrice int
SET @ActualPrice = 10
SET @SoldPrice = 20
IF(@ActualPrice > @SoldPrice)
BEGIN
PRINT 'Sold price is greater than actual price.'
END
ELSE
BEGIN
PRINT 'Sold price is not greater than actual price.'
END
Output
Sold price is greater than actual price.
Above example explains use of greater than comparison operator to compare two variables.