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:

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 you want to retrieve. See Using ColdFusion Studio for details.

When the database processes the SQL, it creates a data set 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 begin CFQUERY tag.

You may reference that data set by name using the CFOUTPUT tag further down on the page.

Basic SQL Syntax elements

The following sections present brief descriptions of the main SQL command elements.

Statements

These keywords identify commonly-used SQL commands:

Basic SQL Statements 
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

These keywords are used to refine SQL statements:

Basic SQL Clauses 
Keyword Description
FROM Names the data source 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

These specify conditions and perform logical and numeric functions:

Basic SQL Operators 
Operator Description
AND Both conditions must be met, such as Paris AND Texas
OR At least one condition must be met, such as Smith OR Smyth
NOT Exclude the condition following, such as Paris NOT France
= 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

Keep the following in mind when writing SQL in ColdFusion: