O'Reilly logo

Programming ColdFusion by Rob Brooks-Bilson

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

A Quick SQL Primer

Before we go any further, a quick primer on SQL is in order. If you are already an SQL guru, feel free to skip this section. If, however, you are new to SQL, this section quickly covers the basic elements that go into creating an SQL statement. This primer is by no means a substitute for a thorough lesson on SQL. You may want to consult additional SQL references before proceeding, as a good understanding of SQL is an essential element in ColdFusion application design. One of the surest ways to bottleneck your applications is with poorly written SQL. Additionally, SQL is implemented in slightly different ways across various RDBMS platforms. For this reason, it is important to consult the documentation specific to your database to understand these differences.

With the disclaimer out of the way, let’s move on and look at the elements that go into creating an SQL statement for use in a CFQUERY tag. If you don’t completely understand everything we are about to cover, don’t worry. Every aspect (and more) of the SQL we cover in the primer is covered in more detail throughout this and the next chapter.

Most database transactions in a web application can be grouped into one of four categories: selecting, inserting, updating, and deleting data. Not surprisingly, there are four commands in SQL that handle theses tasks. They are SELECT, INSERT, UPDATE, and DELETE, respectively:


Retrieves data from a data source


Inserts new data in a data source


Updates existing data in a data source


Deletes data from a data source

Once you have determined the type of operation you want to perform, the next step is to refine the SQL statement by adding various clauses and operators. Depending on the action you want to perform, the syntax of the SQL statement varies. Here are some common SQL clauses:


The table name or names you want to perform the SELECT, or DELETE action against


Specifies the table name and column names you want to INSERT data into


The values to add to the columns specified in INTO when adding data with an INSERT


Specifies the column names you wish to UPDATE with new values


Specifies one or more conditions governing what data is returned by a SELECT, what data is changed by a UPDATE, or what data is deleted by a DELETE


Determines the sort order for records returned by a SELECT


Groups related data in a SELECT. Frequently used along with aggregate functions (discussed later in the chapter)


Generally used in place of a WHERE clause when using the GROUP BY clause


Used along with a SELECT statement to retrieve data from two or more related tables


Combines the results of two record sets returned by a SELECT statement into a single record set, provided both record sets have the same number of columns, and those columns are of compatible or convertible datatypes

SQL provides a number of operators, such as AND, =, and OR, that can be used to construct compound, conditional, and comparison statements. Some of the more popular operators are shown in Table 4-1.

Table 4-1. Common SQL Operators




Equal to.


Not equal to.


Less than.


Greater than.


Less than or equal to.


Greater than or equal to.


Plus (addition).


Minus (subtraction).


Divided by (division).


Multiplied by (multiplication).


Both conditions must be True.


One or the other condition must be True.


Ignores a condition.


Value is [not] null.


Value is in a list of values.


Value is in the range between one value and another.


Value is like a wildcarded value. Wildcards are % (string)and _ (character).


Used only with subqueries. Tests for a nonempty record set.

Now we can look at some SQL examples. Here’s how to select all the fields from a database table:

FROM TableName

Here’s how to select specific fields from a table where a certain field must meet a specific condition:

SELECT Field1, Field2
FROM TableName
WHERE Field = value

This example shows how to select fields from two different tables:

SELECT TableName1.Field1, TableName2.Field1
FROM TableName1, TableName2

This example selects two fields from a table and orders the result set by the value of Field1 in ascending order:

SELECT Field1, Field2
FROM TableName
WHERE Field1 = Value1 AND Field2 = Value2

To perform the same query but have the result set ordered in descending order use this code:

SELECT Field1, Field2
FROM TableName
WHERE Field1 = Value1 OR Field1 = Value2

To insert a record into a table, use the INSERT clause with the INTO and VALUES operators:

INSERT INTO TableName(Field1, Field2, Field3)
VALUES('value1', value2, 'value3')

If you want to update an existing row, you can do so on a field-by-field basis using the UPDATE clause along with the SET and WHERE operators:

UPDATE TableName
SET Field1 = 'value1',
    Field2 = value2, 
            Field3 = 'value3'
WHERE Fieldx = valuex

This code deletes a single row of data from a table:

WHERE Field = value

If you want to delete multiple rows in one operation, use the IN operator like this:

WHERE field IN (field1,field2,fieldx)

Now you should be primed and ready to jump into embedding SQL statements within the CFQUERY tag. The next section looks at using the CFQUERY tag in conjunction with SQL to retrieve data from a database and display the results in the browser.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required