When working with multiple WHERE conditions, it is important to take into account the order in which conditions are evaluated. If you are not at least aware of the order, then you might not correctly anticipate the results. Conditions are evaluated in the following order:
• Parentheses ()
It is also helpful to note that AND operates faster than OR.
SQL and ColdFusion share many similar functions. Most ColdFusion developers find it easier to just use the Cold Fusion functions to format their data, as this does not require the learning of a new set of SQL functions. But, just as it is much more efficient for SQL to filter your records than it is for Cold Fusion to filter them using CFIF statements; formatting your data with SQL functions is usually much faster than formatting them in your ColdFusion template with Cold Fusion functions.
There are many SQL functions. A detailed list of most SQL functions can be found in Appendix E “SQL Function Reference.” SQL functions can be used in both the SELECT and WHERE clauses of your SQL statement. You have already seen . the use of several SQL functions when dealing with aggregate functions and the GROUP BY clause. Mainly, SQL functions are used to format data in a column before it is returned in the query results. Not all SQL functions that are available work with all versions of SQL. Check your database documentation. Listing 10.19 shows the use of two common SQL functions, UCASEO and YEARO. UCASEO converts a string to all uppercase characters, while YEARO extracts the year from a valid date va!ue.
Using SQL Functions