Operator Precedence – SQL Server Syntax Example: Operator Precedence – T-SQL Example

Putting the Pieces of .NET Together - 8 Part Video Series By Wade Harvey
Introduction
Videos 1-4	1. Servers (3 minutes)	2. .NET Framework (10 minutes)	3. Security (8 minutes)	4. Monitoring Tools (10 minutes)
Videos 5-8	5. Web Servers (6 minutes)	6. SQL Server (6 minutes)	7. Software Develop Tools (10 minutes)	8. Languages (2 minutes)
Download PDF	Putting the Pieces of .NET Together - 48-page PDF

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:

 T-SQL | 
 copy code |
?
 
 
/* 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
 
 
 
 

Sample Output for Operator Precedence Example

Related posts:

1. Or Operator – SQL Server Syntax Example: Or Operator – T-SQL Example Or Operator - SQL Server Syntax Example: Or Operator -...
2. Operators – SQL Server Syntax Example: Operators – T-SQL Example Operators - SQL Server Syntax Example: Operators - T-SQL Example...
3. Logical Operators – SQL Server Syntax Example: Logical Operators – T-SQL Example Logical Operators - SQL Server Syntax Example: Logical Operators -...
4. NULL – SQL Server Syntax Example: NULL – T-SQL Example NULL - SQL Server Syntax Example: NULL - T-SQL Example...
5. NOT – SQL Server Syntax Example: NOT – T-SQL Example NOT - SQL Server Syntax Example: NOT - T-SQL Example...

Related posts brought to you by Yet Another Related Posts Plugin.