I am going to keep the ball rolling with my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.
I will exploring the Comparison Operators today.
These comparison operators are known as the less than <, less than or equal to <=, equal to =, greater than >, and greater than or equal to >= symbols in inequality and equality mathematics.
There are both ANSI standard and Microsoft specific comparison operators. I will be using the tiny integer data type to declare variables in the examples below. This allows us to focus just on the comparison logic instead of worrying about data types and implicit data type conversions.
Each example below shows both a positive and negative test using each ANSI standard operator.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
-- -- Comparision operators - ANSI standard -- -- Declare variables declare @one tinyint = 1; declare @two tinyint = 2; declare @three tinyint = 3; -- Ansi operator - < if @one < @two print 'A01 - one is less than two'; if not (@three < @two) print 'A02 - three is not less than two'; print ' '; -- Ansi operator - > if @two > @one print 'A03 - two is greater than one'; if not (@two > @three) print 'A04 - two is not less than three'; print ' '; -- Ansi operator - = if @two = @two print 'A05 - two equals two'; if not (@two = @three) print 'A06 - two not equal to three'; print ' '; -- Ansi operator - <> if not (@two <> @two) print 'A07 - false - two not equal to two'; if @two <> @three print 'A08 - true - two not equal to three'; print ' '; -- Ansi combo operator - <= if @one <= @two print 'A09 - one is less than or equal to two'; if @two <= @two print 'A10 - two is less than or equal to two'; print ' '; -- Ansi combo operator - >= if @two >= @two print 'A11 - two is greater than or equal to two'; if @three >= @two print 'A12 - three is greater than or equal to two'; print ' '; |
The output of each comparison is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
output: A01 - one is less than two A02 - three is not less than two A03 - two is greater than one A04 - two is not less than three A05 - two equals two A06 - two not equal to three A07 - false - two not equal to two A08 - true - two not equal to three A09 - one is less than or equal to two A10 - two is less than or equal to two A11 - two is greater than or equal to two A12 - three is greater than or equal to two |
The TSQL examples below shows one correct use of each Microsoft specific comparison operator.
I suggest not using them in coding since your code will not be portable between different database engines.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- -- Comparision operators - Microsoft specific -- -- Declare variables declare @four tinyint = 4; declare @five tinyint = 5; -- Microsoft operator - != if @four != @five print 'M01 - four is not equal to five'; -- Microsoft operator - !> if @four !> @five print 'M02 - four is not greater than five'; -- Microsoft operator - !< if @five !< @four print 'M03 - five is not less than four'; |
The output of each comparison is listed below.
|
1 2 3 4 5 |
output: M01 - four is not equal to five M02 - four is not greater than five M03 - five is not less than four |
Next time, I will be using comparison operators with different data types.
