Thursday, September 27, 2007

SQL Where clauses: Avoid Case, use Boolean logic

SQL WHERE clauses: Avoid CASE, use Boolean logic As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient. ------------ Apparently Coalesce() in the Where clause removes the queries ability to use an index for evaluating that column. Makes the code a tad bit messier but really good to know when to use boolean logic.

No comments: