ColdFusion 9.0 Resources

Basic SQL syntax elements

Contents [Hide]

Statements
Statement clauses
Operators
Case sensitivity with databases
SQL notes and considerations

The following tables briefly describe the main SQL command elements.

Statements

A SQL statement always begins with a SQL verb. The following keywords identify commonly used SQL verbs:

Keyword	Description
SELECT	Retrieves the specified records.
INSERT	Adds a new row.
UPDATE	Changes values in the specified rows.
DELETE	Removes the specified rows.

Statement clauses

Use the following keywords to refine SQL statements:

Keyword	Description
FROM	Names the data tables for the operation.
WHERE	Sets one or more conditions for the operation.
ORDER BY	Sorts the result set in the specified order.
GROUP BY	Groups the result set by the specified select list items.

Operators

The following basic operators specify conditions and perform logical and numeric functions:

Operator	Description
AND	Both conditions must be met
OR	At least one condition must be met
NOT	Exclude the condition following
LIKE	Matches with a pattern
IN	Matches with a list of values
BETWEEN	Matches with a range of values
=	Equal to
<>	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
+	Addition
-	Subtraction
/	Division
*	Multiplication

Case sensitivity with databases

ColdFusion is a case-insensitive programming environment. Case insensitivity means the following statements are equivalent:

<cfset foo="bar"> 
<CFSET FOO="BAR"> 
<CfSet FOO="bar">

However, many databases, especially UNIX databases, are case sensitive. Case sensitivity means that you must match exactly the case of all column and table names in SQL queries.

For example, the following queries are not equivalent in a case-sensitive database:

SELECT LastName FROM EMPLOYEES 
SELECT LASTNAME FROM employees

In a case-sensitive database, employees and EMPLOYEES are two different tables.

For information on how your database handles case, see the product documentation.

SQL notes and considerations

When writing SQL in ColdFusion, keep in mind the following guidelines:

If you use a ColdFusion variable in your SQL expression, and the variable value is a string that contains single quotes, place the variable in a PreserveSingleQuotes function to prevent ColdFusion from interpreting the quotation marks. The following example shows this use:
```
<cfset List = "'Suisun', 'San Francisco', 'San Diego'"> 
<cfquery name = "GetCenters" datasource = "cfdocexamples"> 
    SELECT Name, Address1, Address2, City, Phone 
    FROM Centers 
    WHERE City IN (#PreserveSingleQuotes(List)#) 
</cfquery>
```
There is a lot more to SQL than what is covered here. It is a good idea to purchase one or several SQL guides for reference.
To perform a successful query, the data source, columns, and tables that you reference must exist.
Some DBMS vendors use nonstandard SQL syntax (known as a dialect) in their products. ColdFusion does not validate the SQL; it is passed on to the database for validation, so you are free to use any syntax that your database supports. Check your DBMS documentation for nonstandard SQL usage.