Rediger

Del via

Operator precedence (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

When a complex expression contains multiple operators, operator precedence determines the sequence of operations. The order of execution can significantly affect the resulting value.

Operators have the precedence levels shown in the following table. An operator on a higher level is evaluated before an operator on a lower level. In the following table, 1 is the highest level and 8 is the lowest level.

Level Operators
1 ~ (bitwise NOT)
2 * (multiplication), / (division), % (modulus)
3 + (positive), - (negative), + (addition), + (concatenation), - (subtraction), & (bitwise AND), ^ (bitwise exclusive OR), | (bitwise OR), << (bitwise left shift), >> (bitwise right shift)
4 =, >, <, >=, <=, <>, !=, !>, !< (comparison operators)
5 NOT
6 AND
7 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
8 = (assignment)
Level Operators
1 ~ (bitwise NOT)
2 * (multiplication), / (division), % (modulus)
3 + (positive), - (negative), + (addition), + (concatenation), - (subtraction), & (bitwise AND), ^ (bitwise exclusive OR), | (bitwise OR)
4 =, >, <, >=, <=, <>, !=, !>, !< (comparison operators)
5 NOT
6 AND
7 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
8 = (assignment)

When two operators in an expression have the same precedence level, the evaluation happens from left to right based on their position in the expression. For example, in the expression used in the following SET statement, the subtraction operator is evaluated before the addition operator.

DECLARE @MyNumber AS INT;
SET @MyNumber = 4 - 2 + 27;

-- Evaluates to 2 + 27 which yields an expression result of 29.
SELECT @MyNumber;

Use parentheses to override the defined precedence of the operators in an expression. Everything within parentheses is evaluated to yield a single value. Any operator outside those parentheses can use that value.

For example, in the expression used in the following SET statement, the multiplication operator has a higher precedence than the addition operator. The multiplication operation is evaluated first. The expression result is 13.

DECLARE @MyNumber AS INT;
SET @MyNumber = 2 * 4 + 5;

-- Evaluates to 8 + 5 which yields an expression result of 13.
SELECT @MyNumber;

In the expression used in the following SET statement, the parentheses cause the addition to be evaluated first. The expression result is 18.

DECLARE @MyNumber AS INT;
SET @MyNumber = 2 * (4 + 5);

-- Evaluates to 2 * 9 which yields an expression result of 18.
SELECT @MyNumber;

If an expression has nested parentheses, the most deeply nested expression is evaluated first. The following example contains nested parentheses, with the expression 5 - 3 in the most deeply nested set of parentheses. This expression yields a value of 2. Then, the addition operator (+) adds this result to 4, which yields a value of 6. Finally, the 6 is multiplied by 2 to yield an expression result of 12.

DECLARE @MyNumber AS INT;
SET @MyNumber = 2 * (4 + (5 - 3));

-- Evaluates to 2 * (4 + 2) which then evaluates to 2 * 6, and
-- yields an expression result of 12.
SELECT @MyNumber;

Related content

  • Last updated on