SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Monday, August 14, 2006

Why are parentheses () so important?

SQL Apprentice Question
Having finally getting a query to run, I discovered it was down to some ()
around an AND.


This is the particular line:


([ExpireDate] is null or [ExpireDate] > @TheDateToday)


Without the (), the query will not run. Why is that please?


Please not, that the above is 1 of about 8 AND's in a query that joins about
6 tables.


Thanks,


Celko Answers
>> Without the (), the query will not run. Why is that please? <<


Basic Boolean algebra. The order of execution is NOT, AND then OR.
The parens force the OR to execute within them and produce a result
that is passed to the rest of the search condition. If your parameter
name was meaningful, then you might want to use this code instead:

(expire_date IS NULL OR expire_date > CURRENT_TIMESTAMP)

No comments: