SQL Logic Operator Precedence: and and Or

SQL Logic Operator Precedence: And and Or

And has precedence over Or, so, even if a <=> a1 Or a2

Where a And b 

is not the same as

Where a1 Or a2 And b,

because that would be Executed as

Where a1 Or (a2 And b)

and what you want, to make them the same, is the following (using parentheses to override rules of precedence):

 Where (a1 Or a2) And b

Here's an example to illustrate:

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 -- outputs T
Select Case When (@x=1 OR @y=1) And @z=1 Then 'T' Else 'F' End -- outputs F

For those who like to consult references (in alphabetic order):

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

SQL Server ANDs and ORs precedence

As is, it will be parsed like

WHERE 
(T1.C1 = @VAR1 AND T1.C2 = @VAR2 AND T1.C3 NOT IN(@VAR3))
OR
(T2.C1 = @VAR2)
OR
(T3.C1 = @VAR2)

Logical operator AND having higher order of precedence than IN

Call me a n00b, but I always use parentheses in nontrivial compound conditions.

SELECT Song, Released, Rating
FROM Songs
WHERE
    (Released IN (1967, 1977, 1987))
AND
    SongName = ’WTTJ’

Edited (Corrected, the point remains the same.)

Just yesterday I got caught by this. Started with working code:

WHERE x < 0 or x > 10

Changed it in haste:

WHERE x < 0 or x > 10 AND special_case = 1

Broke, because this is what I wanted:

WHERE (x < 0 or x > 10) AND special_case = 1

But this is what I got:

WHERE x < 0 or (x > 10 AND special_case = 1)

SQL Logic Operator Precedence Or and ()

Yes, with or without the brackets, the result should be the same, in all SQL products that conform with the SQL standard.

The precedence of comparison operators, like <>, is higher than logical operators, like AND.

SQL 'AND' or 'OR' comes first?

AND has higher precedence than OR:

From Oracle 12.1 docs (precedence for AND and OR is consistent to at least version 7 - probably further but I don't have documentation links)

Prior versions:

• Version 8
• Version 7

Operator precedences are shown in the following list, from highest
precedence to the lowest. Operators that are shown together on a line
have the same precedence.

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
= (assignment), :=

However I would HIGHLY encourage the use of parentheses both for clarity and to ENSURE that the operators are evaluated in the order that you intend.

Order of operation for AND and OR in SQL Server queries

The page on Operator Precedence tells you:

When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value.

And that AND has a higher precedence than OR.

However, it's not correct. In SQL, you tell the system what you want, not how to do it, and the optimizer is free to re-order operations, provided that the same logical result is produced.

So, whilst operator precedence tells you how the operators are logically combined, it does not, in fact, control the order in which each piece of logic is actually performed. This means that idioms which may be safe in other languages because of guarantees of execution order are not in fact safe in SQL. E.g. a check such as:

<String can be parsed as an int> && <convert the string to an int and compare to 20>

Can be perfectly safe in languages such as C#. The same logic in SQL is not safe since the optimizer may choose to perform the string to int conversion before it evaluates whether the string can be parsed as an int and so can throw an error about a failed conversion. (Of course, it can also work as you may have expected and not produce an error)

What is Difference Between AND,OR operator in MYSQL ,While Retrieving the Rows?

It's to do with intersecting truth tables.

In the first query the or is inside brackets so it will execute first and return true if the country is either USA or France. Then the And will execute and compare the result of the OR to the result of creditlimit > 100000.

In the second query operator precedence takes over, so whether the Or or the And is executed first. According to this site https://www.mysqltutorial.org/mysql-or/

Operator precedence
When you use more than one logical operator in an expression, MySQL always evaluates the OR operators after the AND operators. This is called operator precedence which determines the order of evaluation of the operators. MySQL evaluates the operator with higher precedence first.

This means that in this query the database will check to see if the Country is France and the creditlimit > 10000 before then running the OR statement between the result and whether the country is USA.

ETA: in plain english the second query, will return all results where the country is USA regardless of the credit limit, it will also return any results where the country is france and the credit limit > 10000.

Seemingly incorrect Operator Precedence

Good question. I think that perhaps the language on the documentation page that you shared is a touch imprecise, which is probably contributing to your confusion. Here's a quote (emphasis mine):

When a complex expression has multiple operators, operator precedence
determines the sequence in which the operations are performed. The
order of execution can significantly affect the resulting value.

The strong implication here is that precedence and order of execution are essentially the same thing, but they're not. Eric Lippert explains this better than I ever could (emphasis in the original).

Precedence rules describe how an underparenthesized expression should
be parenthesized when the expression mixes different kinds of
operators. For example, multiplication is of higher precedence than
addition, so 2 + 3 x 4 is equivalent to 2 + (3 x 4), not (2 + 3) x 4.

Associativity rules describe how an underparenthesized expression
should be parenthesized when the expression has a bunch of the same
kind of operator. For example, addition is associative from left to
right, so a + b + c is equivalent to (a + b) + c, not a + (b + c). In
ordinary arithmetic, these two expressions always give the same
result; in computer arithmetic, they do not necessarily. (As an
exercise can you find values for a, b, c such that (a + b) + c is
unequal to a + (b + c) in C#?)

Now the confusing one.

Order of evaluation rules describe the order in which each operand in
an expression is evaluated. The parentheses just describe how the
results are grouped together; “do the parentheses first” is not a rule
of C#. Rather, the rule in C# is “evaluate each subexpression strictly
left to right”.

Read the whole thing. Eric uses C# and C++ as his examples but his comments apply a lot more generally than that. As he suggests, if you think of operator precedence rules as controlling how the components of an expression are grouped rather than the order in which they're executed, the behavior you're seeing makes a lot more sense. Here's a simplified version of one of the queries that you understandably expected to fail, but which actually succeeded:

declare @t table (x int);
insert @t values (0);

select * from @t where 2 / x > 0 and x > 0;

SQL Server's operator precedence rules simply mean that the above query is equivalent to:

-- Same query as above with operator precedence shown explicitly.
select * from @t where ((2 / x) > 0) and (x > 0);

The interesting thing about AND in this context is that if you know one of its operands is false, then you know that the entire expression is false without even evaluating the other operand. Some programming languages specify the order in which the operand evaluation will occur. For instance, the && operator in C# evaluates its left-hand operand first, and if it's false, it does not evaluate its right-hand operand at all. But the AND operator in T-SQL makes no claims one way or the other; it is up to the optimizer to choose.

There have been a lot of articles devoted to exploring this behavior in detail; for instance, here is a pretty detailed one. Does this help at all?

Grouping AND and OR conditionals in PostgreSQL

In SQL the AND operator takes "precedence" over OR operator. PostgreSQL adheres to the spec here. You can the exact precedence in PostgreSQL in the docs Lexical Structure: Operator Precedence.

So in your case, the result will be the same. However, it's much easier, and cleaner to simply use the parentheses.

---

Related Topics