Basic SQL syntax elements

The following sections 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 MX 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 the following guidelines in mind:

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.
The data source, columns, and tables that you reference must exist in order to perform a successful query.
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 is supported by your database. Check your DBMS documentation for nonstandard SQL usage.

View comments in LiveDocs