Do SQL's `AND` and `OR` Operators Have Different Precedence, and How Does This Affect Query Results?

SQL logical operator precedence: AND and OR

Question:

Consider the following SQL statement:

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

and

<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 two statements equivalent? If not equivalent, how do we prioritize them?

Answer:

Priority of AND and OR operators

In SQL, operator precedence determines the order in which logical operators in the WHERE clause are evaluated. The precedence of AND and OR operators is as follows:

• AND operator has higher priority than OR operator.

This means that even if the AND operator appears to the right of the OR operator, it will be evaluated first.

Apply priority to statement

In the given statement:

• The first statement only uses the AND operator, so precedence is simple.
• The second statement uses a combination of OR and AND operators, where the OR operator precedes the AND operator.

Therefore, the second statement will be evaluated as follows:

<code class="language-sql">WHERE (some_col IN (1,2,3)) OR (some_col IN (4,5) AND some_other_expr)</code>

Truth table verification

Here is a truth table illustrating the calculated differences:

some_col	some_other_expr	语句 1	语句 2
1	0	False	True
2	1	True	True
3	0	False	True
4	0	False	False
5	1	False	True

As we can see, the truth values ​​of these two statements differ in the first line, which shows that they are not equivalent.

Use parentheses to override priority

If you want to change the order of calculation, you can use parentheses to override the default priority. For example, to make the second statement equivalent to the first, it would be written as follows:

<code class="language-sql">WHERE (some_col IN (1,2,3) OR some_col IN (4,5)) AND some_other_expr</code>

The above is the detailed content of Do SQL's `AND` and `OR` Operators Have Different Precedence, and How Does This Affect Query Results?. For more information, please follow other related articles on the PHP Chinese website!