Transact-SQL Operators

Operators are symbols or keywords that specify the type of computation or comparison to be performed in a Transact-SQL statement. Transact-SQL supports a variety of operators that can be used in expressions.

Arithmetic Operators

Used to perform mathematical calculations.

+
Addition

Adds two expressions.

SELECT 10 + 5; -- Result: 15
-
Subtraction

Subtracts the second expression from the first.

SELECT 20 - 7; -- Result: 13
*
Multiplication

Multiplies two expressions.

SELECT 6 * 4; -- Result: 24
/
Division

Divides the first expression by the second.

SELECT 30 / 3; -- Result: 10
%
Modulo

Returns the remainder of an integer division.

SELECT 17 % 5; -- Result: 2

Comparison Operators

Used to compare two expressions. They return a Boolean value (TRUE, FALSE, or UNKNOWN).

=
Equal to

Checks if two expressions are equal.

SELECT 'SQL' = 'SQL'; -- Result: TRUE
<>
Not equal to

Checks if two expressions are not equal.

SELECT 10 <> 5; -- Result: TRUE
<
Less than

Checks if the first expression is less than the second.

SELECT 5 < 10; -- Result: TRUE
>
Greater than

Checks if the first expression is greater than the second.

SELECT 10 > 5; -- Result: TRUE
<=
Less than or equal to

Checks if the first expression is less than or equal to the second.

SELECT 5 <= 5; -- Result: TRUE
>=
Greater than or equal to

Checks if the first expression is greater than or equal to the second.

SELECT 10 >= 5; -- Result: TRUE
LIKE
LIKE

Searches for a specified pattern in a column.

SELECT 'SQL Server' LIKE 'SQL%'; -- Result: TRUE
IN
IN

Compares a value to a list of values.

SELECT 10 IN (5, 10, 15); -- Result: TRUE
BETWEEN
BETWEEN

Checks if a value is within a range.

SELECT 12 BETWEEN 10 AND 15; -- Result: TRUE

Logical Operators

Used to combine multiple conditions or to negate a condition.

AND
AND

Combines two conditions; returns TRUE if both conditions are TRUE.

SELECT 5 > 3 AND 10 < 20; -- Result: TRUE
OR
OR

Combines two conditions; returns TRUE if at least one condition is TRUE.

SELECT 5 > 10 OR 10 < 20; -- Result: TRUE
NOT
NOT

Negates a Boolean expression.

SELECT NOT (5 > 10); -- Result: TRUE
ALL
ALL

A comparison operator that is preceded by another operator. It compares a value to each value in a list or subquery.

SELECT > ALL (SELECT 10, 20, 30); -- Result: FALSE (for value 25)
ANY
ANY

A comparison operator that is preceded by another operator. It compares a value to any value in a list or subquery.

SELECT > ANY (SELECT 10, 20, 30); -- Result: TRUE (for value 25)

Bitwise Operators

Used to perform bitwise operations on integer data.

&
Bitwise AND

Performs a bitwise AND operation on two integer operands.

SELECT 6 & 3; -- Binary: 110 & 011 = 010. Result: 2
|
Bitwise OR

Performs a bitwise OR operation on two integer operands.

SELECT 6 | 3; -- Binary: 110 | 011 = 111. Result: 7
^
Bitwise XOR

Performs a bitwise exclusive OR operation on two integer operands.

SELECT 6 ^ 3; -- Binary: 110 ^ 011 = 101. Result: 5
~
Bitwise NOT

Performs a bitwise NOT operation on a single integer operand.

SELECT ~6; -- Result: -7 (two's complement)
<<
Left Shift

Shifts the bits of the first operand to the left by the number of positions specified by the second operand.

SELECT 6 << 2; -- Binary: 110 shifted left by 2 = 11000. Result: 24
>>
Right Shift

Shifts the bits of the first operand to the right by the number of positions specified by the second operand.

SELECT 24 >> 2; -- Binary: 11000 shifted right by 2 = 110. Result: 6

Assignment Operator

=
Assignment

Assigns a value to a variable.

DECLARE @MyVar INT;
SET @MyVar = 10;
SELECT @MyVar; -- Result: 10

Concatenation Operator

+
String Concatenation

Combines two strings into one.

SELECT 'SQL' + ' ' + 'Server'; -- Result: SQL Server

Note: The behavior of some operators might depend on the data types of the operands involved and the collation settings of the server.