User Guide

Using SQL 455
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.
Reading data from a database
You use the SQL SELECT statement to read data from a database. The SQL statement has the
following general syntax:
SELECT column_names
FROM table_names
[ WHERE search_condition ]
[ GROUP BY group_expression ] [HAVING condition]
[ ORDER BY order_condition [ ASC | DESC ] ]
The statements in square brackets are optional.
Note: There are additional options to SELECT depending on your database. For a complete syntax
description for SELECT, see the product documentation.
This section describes options to the SELECT statement.
Results of a SELECT statement
When the database processes a SELECT statement, it returns a record set containing the requested
data. The format of a record set is a table with rows and columns. For example, if you write the
following query:
SELECT * FROM employees WHERE DeptID=3
The query returns the following table:
Since the data returned to ColdFusion MX by a SELECT statement is in the form of a database
table, ColdFusion MX lets you write a SQL query on the returned results. This functionality is
called query of queries. For more information on query of queries, see Chapter 20, “Accessing and
Retrieving Data,” on page 465.