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

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:

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.

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:

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

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Code Smells

There is no additional info for this rule.

Other Resources