Where Clause: Order of Precedence

[GH]

Here's my WHERE clause on an SQL statement I wrote:

WHERE blnLive = TRUE AND blnVacPromo = TRUE AND (dtToPost <= Now() AND
dtToRemove > Now() )

What I'm wondering is how can I use parentheses (sp?) to invoke this
order or precedence?

1.) Is blnLive equal to TRUE?
2.) Is blnVacPromo equal to TRUE?
3.) Is dtToPost less than or equal to Today?
4.) Is dtToRemove greater than Today?

I'm trying to optimize my WHERE clause as much as I can. I don't want
the SQL engine to waste time trying to determine #2, #3, & #4 if #1
isn't true. Or I don't want it to evaluate #3 & #4 if #1 is true and #2
is false.

Is order of precedence left to right? Inner parenthesis to outer
parenthesis?

Anyone have any insight on this?

Thanks!

[Bob Barrows [MVP]]

GH wrote:

> Here's my WHERE clause on an SQL statement I wrote:
>
> WHERE blnLive = TRUE AND blnVacPromo = TRUE AND (dtToPost <= Now() AND
> dtToRemove > Now() )
>
> What I'm wondering is how can I use parentheses (sp?) to invoke this
> order or precedence?
>
> 1.) Is blnLive equal to TRUE?
> 2.) Is blnVacPromo equal to TRUE?
> 3.) Is dtToPost less than or equal to Today?
> 4.) Is dtToRemove greater than Today?
>
> I'm trying to optimize my WHERE clause as much as I can. I don't want
> the SQL engine to waste time trying to determine #2, #3, & #4 if #1
> isn't true. Or I don't want it to evaluate #3 & #4 if #1 is true and
> #2 is false.
>
> Is order of precedence left to right? Inner parenthesis to outer
> parenthesis?
>
> Anyone have any insight on this?
>
> Thanks!

Hmm, it looks like you're using Access. Please don't make us guess.

Parentheses will not affect the evaluation of these criteria. ALL the
criteria will be evaluated. Evaluation does not stop at the first criterion
to return false. Parentheses will group boolean expressions, nothing more.
Since all your operators are AND, parentheses will have no affect on this
expression. Think of algebra: 1 + (2 + 3) = (1 + 2) + 3

Grouping boolean expressions only affects expressions containing the OR
operator. For example, this expression will evaluate to false:

true_expression AND true_expression OR false_expression

However, this will evaluate to true:

true_expression AND (true_expression OR false_expression)

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[GH]

Bob Barrows [MVP] wrote:

> GH wrote:
>

>>Here's my WHERE clause on an SQL statement I wrote:
>>
>>WHERE blnLive = TRUE AND blnVacPromo = TRUE AND (dtToPost <= Now() AND
>>dtToRemove > Now() )
>>
>>What I'm wondering is how can I use parentheses (sp?) to invoke this
>>order or precedence?
>>
>>1.) Is blnLive equal to TRUE?
>>2.) Is blnVacPromo equal to TRUE?
>>3.) Is dtToPost less than or equal to Today?
>>4.) Is dtToRemove greater than Today?
>>
>>I'm trying to optimize my WHERE clause as much as I can. I don't want
>>the SQL engine to waste time trying to determine #2, #3, & #4 if #1
>>isn't true. Or I don't want it to evaluate #3 & #4 if #1 is true and
>>#2 is false.
>>
>>Is order of precedence left to right? Inner parenthesis to outer
>>parenthesis?
>>
>>Anyone have any insight on this?
>>
>>Thanks!

>
>
> Hmm, it looks like you're using Access. Please don't make us guess.
>
> Parentheses will not affect the evaluation of these criteria. ALL the
> criteria will be evaluated. Evaluation does not stop at the first criterion
> to return false. Parentheses will group boolean expressions, nothing more.
> Since all your operators are AND, parentheses will have no affect on this
> expression. Think of algebra: 1 + (2 + 3) = (1 + 2) + 3
>
> Grouping boolean expressions only affects expressions containing the OR
> operator. For example, this expression will evaluate to false:
>
> true_expression AND true_expression OR false_expression
>
> However, this will evaluate to true:
>
> true_expression AND (true_expression OR false_expression)
>
> Bob Barrows

Sorry, yes, I'm using Access. That makes sense. Thanks for your help!

Gabe