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 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.

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 a database table. Because the data returned to ColdFusion by a SELECT statement is in the form of a database table, ColdFusion 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 Accessing and Retrieving Data.

The next example uses a SELECT statement to return only a specific set of columns from a table: 

SELECT LastName, FirstName FROM employees WHERE DeptID=3

Filtering results

The SELECT statement lets you filter the results of a query to return only those records that meet specific criteria. For example, if you want to access all database records for employees in department 3, you use the following query:

SELECT * FROM employees WHERE DeptID=3

You can combine multiple conditions using the WHERE clause. For example, the following example uses two conditions:

SELECT * FROM employees WHERE DeptID=3 AND Title='Engineer'

Sorting results

By default, a database does not sort the records returned from a SQL query. In fact, you cannot guarantee that the records returned from the same query are returned in the same order each time you run the query.

However, if you require records in a specific order, you can write your SQL statement to sort the records returned from the database. To do so, you include an ORDER BY clause in the SQL statement.

For example, the following SQL statement returns the records of the table ordered by the LastName column:

SELECT * FROM employees ORDER BY LastName

You can combine multiple fields in the ORDER BY clause to perform additional sorting:

SELECT * FROM employees ORDER BY DepartmentID, LastName

This statement returns row ordered by department, then by last name within the department.

Returning a subset of columns

You want only a subset of columns returned from a database table, as in the following example, which returns only the FirstName, LastName, and Phone columns. This example is useful if you are building a web page that shows the phone numbers for all employees.

SELECT FirstName, LastName, Phone FROM employees

However, this query does not to return the table rows in alphabetical order. You can include an ORDER clause in the SQL, as follows:

SELECT the FirstName, LastName, Phone  
    FROM employees  
    ORDER BY LastName, FirstName

Using column aliases

You have column names that you do not want to retain in the results of your SQL statement. For example, your database is set up with a column that uses a reserved word in ColdFusion, such as EQ. In this case, you can rename the column as part of the query, as follows:

SELECT EmpID, LastName, EQ as MyEQ FROM employees

The results returned by this query contains columns named EmpID, LastName, and MyEQ.

Accessing multiple tables

In a database, you can have multiple tables containing related information. You can extract information from multiple tables as part of a query. In this case, you specify multiple table names in the SELECT statement, as follows:

SELECT LastName, FirstName, Street, City, State, Zip 
    FROM employees, addresses  
    WHERE employees.EmpID = addresses.EmpID 
    ORDER BY LastName, FirstName

This SELECT statement uses the EmpID field to connect the two tables. This query prefixes the EmpID column with the table name. This is necessary because each table has a column named EmpID. Prefix a column name with its table name if the column name appears in multiple tables.

In this case, you extract LastName and FirstName information from the employees table and Street, City, State, and ZIP information from the addresses table. You can use output such as this is to generate mailing addresses for an employee newsletter.

The results of a SELECT statement that references multiple tables is a single result table containing a join of the information from corresponding rows. A join means information from two or more rows is combined to form a single row of the result. In this case, the resultant recordset has the following structure:

View full size graphic

What is interesting in this result is that even though you used the EmpID field to combine information from the two tables, you did not include that field in the output.