T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - Operators



Operators are used to perform operation on two operands. Operators in T-SQL can be categorized as follows:

  • Arithmetic operators
  • Comparison operators
  • Logical operators

T-SQL Arithmetic operators

Arithmetic operators are used to perform arithmetic operations on two operands.

OperatorNameDescriptionExample
+AdditionAdd two valuesMore Info
-SubtractionSubtract one value from anotherMore Info
*MultiplicationMultiply two valuesMore Info
/DivisionDivide one value by anotherMore Info
%ModuloReturns remainder of division operationMore Info

T-SQL Comparison operators

Comparison operators are used to compare values of two operands. It returns true when values matches and false when values does not match.

OperatorDescriptionExample
=Equal toMore Info
!=Not equal toMore Info
<>Not equal toMore Info
>Greater thanMore Info
<Less thanMore Info
!>Not Greater thanMore Info
!<Not Less thanMore Info
>=Greater than or equal toMore Info
<=Less than or equal toMore Info

T-SQL Logical operators

Logical operators are used to create and combine one or more conditions.

Operator Description
ALL Returns true if all of the subquery values meet the condition.
AND Only includes rows where both conditions is true.
ANY Returns true if any of the subquery values meet the condition.
BETWEEN Selects values within a given range.
EXISTS Tests for the existence of records from a subquery.
IN Allows you to specify multiple values in a WHERE clause.
LIKE Searches for a specified pattern in a column.
NOT Only includes rows where a condition is not true.
NOT LIKE Negation of LIKE.
OR Returns True when any of the conditions is true.
IS NULL Tests for null values.
IS NOT NULL Tests for non-null values.
SOME Returns true if any of the subquery values meet the condition.

T-SQL Operators Precedence

Operator precedence (order of operations) is a collection of rules that reflect conventions about which procedures to perform first in order to evaluate a given expression.

For example, multiplication has higher precedence than addition. Thus, the expression 1 + 2 × 3 is interpreted to have the value 1 + (2 × 3) = 7, and not (1 + 2) × 3 = 9. When exponent is used in the expression, it has precedence over both addition and multiplication. Thus 3 + 52 = 28 and 3 × 52 = 75.

The following table lists the precedence of T-SQL operators. Operators are listed top to bottom, in descending precedence. Operators with higher precedence are evaluated before operators with relatively lower precedence.

PrecedenceOperators
8~ (Bitwise NOT)
7* (Multiplication), / (Division), % (Modulus)
6+ (Positive), - (Negative), + (Addition), + (Concatenation), - (Subtraction), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)
5= (Comparison), >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
4NOT
3AND
2ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
1= (Assignment)