Operator Precedence – SQL Server Syntax Example: Operator Precedence – T-SQL Example
Operator Precedence – SQL Server Syntax Example: Operator Precedence – T-SQL Example
Purpose: – Illustrates the SQL Server syntax for operator precedence.
SYNTAX:
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
column_list
[ INTO new_table ]
[ FROM { table_source } [ ,…n ] ]
[ WHERE expression1 operator expression2 — eval highest level operators first
[ GROUP BY ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ]
PURPOSE:
Operator precedence determines the sequence that operations are performed when evaluating a complex expression.
Here is a table of the levels of various types of operators:
Level | Operator |
1 | inner parenthesis |
2 | outer parenthesis |
3 | ~ (Bitwise NOT) |
4 | * (Multiply), / (Division), % (Modulo) |
5 | + (Positive), – (Negative), + (Add), (+ Concatenate), – (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR) |
6 | =, >, < , >=, < =, <>, !=, !>, !< (Comparison operators) |
7 | NOT |
8 | AND |
9 | ALL, ANY, BETWEEN, IN, LIKE, OR, SOME |
10 | = (Assignment) |
NOTE: When operators are at same level, proceed left to right – just as in reading.
Code Sample for Opearator Precedence:
/* Operator Precedence example from http://idealprogrammer.com PURPOSE: Operator precedence determines the sequence that operations are performed when evaluating a complex expression. SYNTAX: Start with highest level operators Here is a table of the various levels of operators: Level Operators 1 inner parenthesis 2 outer parenthesis 3 ~ (Bitwise NOT) 4 * (Multiply), / (Division), % (Modulo) 5 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR) 6 =, >, < , >=, < =, <>, !=, !>, !< (Comparison operators) 7 NOT 8 AND 9 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME 10 = (Assignment) NOTE: When operators are at same level, proceed left to right - just as in reading SELECT [ ALL | DISTINCT ] [TOP ( expression ) [PERCENT] [ WITH TIES ] ] column_list [ INTO new_table ] [ FROM { table_source } [ ,...n ] ] [ WHERE expression1 operator expression2] [ GROUP BY ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] */ CREATE TABLE people( ID int, firstname varchar (20), lastname varchar (20), statecode varchar (2), alive bit, height int ) GO INSERT INTO people (ID, firstname, lastname, statecode, alive) --height is null VALUES (1, 'Paul', 'Revere', 'AL', 0) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (2, 'Pat', 'Lennon', 'NY', 0, 69) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (3, 'Peter', 'Martin', 'NY', 1, 75) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (4, 'George', 'Washington', 'VA', 0, 75) GO -- Select entire table SELECT 'Entire Table', * FROM people -- 1. Example of using parentheses SELECT 'Example #1' as Example, firstname, lastname, height FROM people p WHERE (firstname = 'Paul' and lastname = 'Revere') OR (firstname = 'George' and lastname = 'Washington') GO -- 2. Example of using inner parenthesis DECLARE @InnerParentheses int SET @InnerParentheses = 8 * (5 + (6 - 2) ) -- Evaluates to 8 * (5 + 4) which then reduces to 8 * 9 SELECT 'Example #2' AS Example, @InnerParentheses As InnerParentheses DROP TABLE people; GO |