0

I have the following query:

SELECT * FROM MUFFIN_TYPES MT
  WHERE MT.flavor IS NULL 
    OR MT.flavor IS NOT NULL 
    AND MT.additionDate IS NOT NULL

Without clarifying parenthesis this is confusing. Would MT.flavor IS NULL OR MT.flavor IS NOT NULL always evaluate to true? Or would it evaluate as MT.flavor IS NULL (OR MT.flavor IS NOT NULL AND MT.additionDate IS NOT NULL)?

I couldn't find any documentation on the matter. I the latter of the two options, but I'm not sure.

Note: I'm using Oracle Pl/SQL.

SyntaxRules
  • 2,056
  • 23
  • 32
  • Your example is plain SQL, there is no PL/SQL in your question. –  Jan 28 '14 at 22:48
  • 1
    As you `couldn't find any documentation on the matter` you must be searching the wrong places as this is a pretty crucial subject. – fvu Jan 28 '14 at 22:48
  • 2
    The concept is called `operator precedence` or `order of operations`. – Brian Jan 28 '14 at 22:51
  • This can easily be worked out with just a couple of test queries. It's good manners to at least Try for yourself first. – MatBailie Jan 28 '14 at 22:54
  • 1
    Add parentheses for clarity, no matter if precedence matters or not. If you had to ask whether AND or OR had higher precedence, chances are another programmer who comes along later will wonder as well. Be explicit about your intent. – Andy Lester Jan 28 '14 at 23:03
  • Note that the Oracle docs coins this concept `Condition Precedence` – SyntaxRules Jan 30 '14 at 16:58

1 Answers1

6

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:

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.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • Well, this is for MySQL and OP uses Oracle. But the precedence is standardized on SQL-level, so no change here. [See Oracle Database documentation](http://docs.oracle.com/cd/E11882_01/server.112/e26088/conditions001.htm#SQLRF52103) – Simon Jan 28 '14 at 22:51
  • No, that link is for MySQL 5.6. There's no mention of MySQL on the page, but 12.3.1 is the page number, and if you go up a few levels, you wind up at the MySQL table of contents. – Brilliand Jan 28 '14 at 23:00
  • @Brilliand I stand corrected - I'll edit my post with the right links. – D Stanley Jan 28 '14 at 23:02
  • 1
    AND comes before OR - that's common in Boolean algebra. However, unless you work heavily in boolean algebra every day, you should put brackets in your exporession, no matter if they are requried or not. It's easier to understand and readable for other people. – Wernfried Domscheit Jan 29 '14 at 08:01