Writing SQL

In between the begin and end cfquery tags, write the SQL that you want the database to execute.

For example, to retrieve data from a database:

Write a SELECT statement that lists the fields or columns that you want to select for the query.
Follow the SELECT statement with a FROM clause that specifies the database tables that contain the columns.

Tip

If you are using ColdFusion Studio, you can use the Query Builder to build SQL statements by graphically selecting the tables and records within those tables that you want to retrieve.

When the database processes the SQL, it creates a data set (a structure containing the requested data) that is returned to ColdFusion Server. ColdFusion places the data set in memory and assigns it the name that you defined for the query in the name attribute of the cfquery tag.

You can reference that data set by name using the cfoutput tag later on the page.

Basic SQL syntax elements

The following sections present brief descriptions of 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

UPDATEw
Changes values in the specified rows

DELETE
Removes the specified rows

Keyword	Description
SELECT	Retrieves the specified records
INSERT	Adds a new row
UPDATEw	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

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

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

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 that you can refer to.
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 in a cfquery, so you are free to use any syntax that is supported by your data source. Check your DBMS documentation for nonstandard SQL usage.

Developing ColdFusion Applications
Querying a Database