Home > Oracle, SQL > The Importance of Parenthese in SQL Select Where’s

The Importance of Parenthese in SQL Select Where’s

September 27, 2006

I’ve been trying to figure out an inconsitancy in my SQL querying to pinpoint exactly the information I want.  My problem?  The results varied and seemed to include parts of the WHERE clause where I didn’t expect it to.

The original query:

select
  term as year,
  status,
  school_id as id
from data_plans
where
  school_id = 231
  or status = 2
  and term != 2005

The return:

year, status, term
2005 2 231
2006 2 150
2006 2 342
2006 2 353

Now, because I’m a bit slow at SQL, why do I have a 2005 results if I said that I do not want records where term != 2005.  Because, it appears, that the WHERE clause is in order of priority.  Since the record meets the explicit school_id = 231, it is included.  So, how to fix this.

The new query:

select
  term as year,
  status,
  school_id as id
from data_plans
where
(  school_id = 231
  or status = 2 )
  and term != 2005

How I read this new query is for that one 2005 record:

  1. Is school_id = 231 OR status = 2, TRUE
  2. AND is the term !=2005, FALSE
  3. Throw it out, a “false” exists so the entire row must be false.

I’m sure the actual processing logic is written somewhere, but Google has let me down.

Categories: Oracle, SQL
%d bloggers like this: