SQL AND/OR Operator Precedence: Are These SQL Statements Equivalent?

SQL Logical Operator Precedence: Understanding the Hierarchy of AND and OR

In SQL, the logical operators "AND" and "OR" are the building blocks for building conditional expressions that filter data based on specified conditions. However, their precedence, or the order in which they are evaluated, is critical in determining the results of these expressions.

Question:

Consider the following two SQL statements:

<code class="language-sql">SELECT [...]
FROM [...]
WHERE some_col in (1,2,3,4,5) AND some_other_expr</code>

<code class="language-sql">SELECT [...]
FROM [...]
WHERE some_col in (1,2,3) or some_col in (4,5) AND some_other_expr</code>

Are these statements equivalent? Is there a tool like a truth table that can help verify their equivalence?

Answer:

No, these two statements are not equivalent . "AND" has higher precedence than "OR", which means that the expression some_col in (1,2,3,4,5) AND some_other_expr is evaluated first, and then the result of that evaluation is applied to the OR operator.

To illustrate this, consider the following example:

<code class="language-sql">Declare @x tinyInt = 1
Declare @y tinyInt = 0
Declare @z tinyInt = 0

Select Case When @x=1 OR @y=1 And @z=1 Then 'T' Else 'F' End -- 输出 T
Select Case When (@x=1 OR @y=1) And @z=1 Then 'T' Else 'F' End -- 输出 F</code>

The first statement evaluates @x=1 OR @y=1 And @z=1 and the result is "T" because the expression @y=1 And @z=1 returns "False". The "OR" operator then combines this result with "@x=1" (which is "True"), ultimately outputting "T".

However, the second statement evaluates (@x=1 OR @y=1) And @z=1 differently. The "OR" operator first combines "@x=1" and "@y=1", both of which are "True", and the result is "True". This "True" result is then evaluated against "@z=1" (which is "False"), ultimately outputting "F".

Therefore, the order of evaluation is important and parentheses can be used to override precedence rules. To make these two statements equivalent, you can add parentheses like this:

<code class="language-sql">SELECT [...]
FROM [...]
WHERE (some_col in (1,2,3) or some_col in (4,5)) AND some_other_expr</code>

For those seeking further reference material on logical operator precedence in SQL, the following resources are recommended:

• Microsoft Transact-SQL operator precedence
• Oracle MySQL 9 operator precedence
• Oracle 10g condition priority
• PostgreSQL operator precedence
• SQL understood by SQLite

The above is the detailed content of SQL AND/OR Operator Precedence: Are These SQL Statements Equivalent?. For more information, please follow other related articles on the PHP Chinese website!