Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Are brackets in the WHERE clause standard sql

Tags:

sql

standards

The course that I am currently doing uses brackets in its WHERE clauses like so:

SELECT bar
FROM   Foo
WHERE (CurrentState = 'happy');

Is this standard sql ?
If not then why use them?

Doesn't seem to be used in the Date & Darwen book I have.


EDIT

Just to clarify - I'm referring to 1992 sql standards

like image 641
whytheq Avatar asked Apr 20 '13 16:04

whytheq


2 Answers

Yes. You can use parenthesis to bind components of where clauses. This isn't necessary in your example, but if you had multiple and and or components, you might need parenthesis to either ensure correct order of operations or simply to self-document the query.

Example 1:

select *
from foo
where 
   (class='A' and subclass='B')
   or (class='C' and subclass='D')

In example 1, the parens aren't strictly required because and binds more tightly than or, but if you had multiple or conditions tied by and you would need it to get correct results, as in example 2 below.

Example 2:

select *
from foo
where 
   (class='A' or class='B')
   and (subclass='C' or subclass='D')

I use them in either case, because I don't like having to parse the sql in my head the same way the query optimizer does -- I'd rather be explicit about it and more quickly understand what the intent is.

like image 174
PaulProgrammer Avatar answered Oct 08 '22 23:10

PaulProgrammer


They are optional. They make sense for more complex WHERE statements.

... WHERE (created > '2012-10' AND created < '2013-01') 
OR (modified > '2012-10' AND modified < '2013-01')
like image 36
Claudio Bredfeldt Avatar answered Oct 08 '22 22:10

Claudio Bredfeldt