SA0034 : Use parentheses to improve readability and avoid mistakes because of logical operator precedence

This rule addresses the issue of unexpected query results due to improper understanding of operator precedence in SQL.

Description

The order in which operators are evaluated can lead to unintended results if not properly managed. Logical operators such as NOT, AND, and OR follow a specific precedence that can affect the outcome of queries. For instance, NOT has the highest precedence, followed by AND, and then OR. Additionally, arithmetic and bitwise operators are evaluated before logical operators. Failing to account for this can lead to logical errors in your queries.

For example:

SQL
1-- Example of problematic query
2SELECT * FROM Employees
3WHERE Age > 30 AND Salary < 50000 OR Department = 'HR';

In the above query, the lack of parentheses means that the AND condition is evaluated before the OR condition, which may not align with the intended logic. This can result in including unintended records, leading to inaccurate query results, such as retrieving all employees over 30 and also all employees in the HR department, regardless of their salary.

  • Operator precedence may cause logical conditions to be evaluated in an unintended order, leading to incorrect query results.

  • Using parentheses can explicitly define the desired order of evaluation, making queries easier to read and reducing the risk of logical errors.

  • There is no significant performance penalty for using parentheses, so they should be used to improve clarity and reduce errors.

How to fix

To ensure correct evaluation of logical operations in your SQL queries, it is important to use parentheses to explicitly define the order of operations.

Follow these steps to address the issue:

  1. Review your queries for any logical conditions that may be impacted by operator precedence, especially those involving NOT, AND, and OR operators.

  2. Add parentheses to group conditions and specify the desired order of evaluation. For example, use (‘Condition1 AND Condition2’) OR Condition3 to ensure the correct logic is applied.

  3. Test your queries to verify that they produce the expected results after applying the changes.

For example:

SQL
1-- Corrected query with explicit operator precedence
2SELECT * FROM Employees
3WHERE (Age > 30 AND Salary < 50000) OR Department = 'HR';

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1-- OR clause used without parenthesis
 2SELECT     title_id ,
 3           type ,
 4           price
 5FROM       pubs.dbo.titles
 6WHERE      type = 'physics'
 7        OR type = 'architecture'
 8       AND advance > 30
 9
10SELECT     title_id ,
11           type ,
12           price
13FROM       pubs.dbo.titles
14WHERE      type = 'physics'
15        OR ( type = 'architecture'
16             AND price > 30 )
17
18SELECT     title_id ,
19           type ,
20           price
21FROM       titles
22WHERE      ( type = 'physics'
23              OR type = 'architecture' )
24       AND advance > 30

Analysis Results
  Message Line Column
1 SA0034 : Use parentheses to improve readability and avoid mistakes because of logical operator precedence. 7 8
See Also

Other Resources