The SELECT Statement


The SELECT Statement

SELECT is the most frequently used SQL statement. It retrieves data from one or more tables. At a minimum, SELECT takes two clauses: the data to be retrieved and the location to retrieve it from.

NOTE

Technically, SELECT statements don't even need table names. It is perfectly valid (although uncommon) to do something like this:

 SELECT 100 AS id 

This statement returns a single row with a single columndata is not retrieved from any table at all.


In practice, however, most basic SELECT statements are made up of four parts that must appear in this order:

  • The data to retrieve

  • The location to retrieve it from

  • Filtering conditions (to restrict the data being retrieved)

  • Sort order (to specify how returned data is sorted)

Specifying the Data to Retrieve

You specify the data to be retrieved by listing the required table column names as the first clause (right after the keyword SELECT). At least one column must be specified.

NOTE

The maximum number of columns (or the width of a retrieved row) that may be retrieved varies from one DBMS (Database Management System) to the next. There is no standard size or limitation.


The following example retrieves a single column (named product_id) from a table (named products):

 SELECT product_id FROM products 

To retrieve multiple columns, you must separate the column names with commas:

 SELECT product_id, product_name FROM products 

NOTE

To retrieve unique values, use the DISTINCT keyword before the column name.


You also can retrieve all columns without listing them individually. You do so by using the wildcard character * as follows:

 SELECT * FROM products 

TIP

For performance reasons, it is generally not a good idea to use SELECT * (unless you actually need every column). As a rule, retrieve just what you need and nothing more.


You can also rename columns when retrieving them by assigning aliases to them. You do so by using the AS keyword, which lets you specify an alternative name for a column, as follows:

 SELECT product_id AS id, product_name AS name FROM products 

In this example, the returned columns are id and name, even though the actual table names are product_id and product_name.

TIP

You most often use aliases when working with aggregate functions (covered in Chapter 46, "Aggregates"), but another important use allows you to rename illegally named columns. For example, some databases allow you to include spaces and special characters (such as the # sign or plus and minus signs) in column names, and these characters could render the column names unusable in other applications (such as ColdFusion). Using aliases, you can rename the columns, giving them safe (and legal) names when they are retrieved.


Specifying the Table

The table name is always specified using the FROM keyword, as follows:

 SELECT product_id, product_name FROM products 

Data can be retrieved from multiple tables, in which case commas must separate the table names. You usually do so only in join operations.

Joins are covered in Chapter 45, "Joins."


NOTE

Some databases require that table names be fully qualified (with a prefix that indicates the table owner and database).


Filtering

Retrieved data is filtered using the WHERE clause, which must contain one or more filter conditions using supported operators. Table 44.1 lists the operators supported by most SQL implementations.

Table 44.1. WHERE Clause Operators

OPERATOR

DESCRIPTION

=

Equality

<>

Non-equality

<

Less than

<=

Less than or equal to

>

Greater than

>=

Greater than or equal to

IN

One of a set of

LIKE

Wildcard match

BETWEEN

Between two specified values

IS NULL

Is a NULL value

AND

Combine clauses

OR

Or clauses

NOT

Negate clauses


The basic mathematical type operators are used as follows:

 SELECT product_id, product_name FROM products WHERE product_id=1 

You use the IN operator to specify multiple values, which must be separated by commas and enclosed within parentheses:

 SELECT product_id, product_name FROM products WHERE product_id IN (1,3,7,18,45) 

TIP

You can use lists and the list functions to pass values to the IN operator. See Chapter 13, "Lists," for more information.


You use the LIKE operator for wildcard searches. The SQL specification supports three wildcards, as shown in Table 44.2 (although some databases do not support them all).

Table 44.2. Wildcard Operators

OPERATOR

DESCRIPTION

%

Match zero or more characters

_

Match a single character

[]

Match one of a set of characters


You use wildcard searches to search for patterns within column text. The following example finds all products beginning with the letter s:

 SELECT product_id, product_name FROM products WHERE product_name LIKE 's%' 

Wildcards can be used anywhere within a string, not just at the beginning. The following example finds all products that contain the text widget:

 SELECT product_id, product_name FROM products WHERE product_name LIKE '%widget%' 

TIP

Wildcard matches are generally the slowest form of filter. This is particularly true of wildcards used in the start of a search pattern. And as such, they should not be overused unnecessarily.


You can combine searches by using the AND and OR operators. The following example finds all products with the text widget in the name and a cost of $5 or more:

 SELECT product_id, product_name FROM products WHERE product_name LIKE '%widget%' AND product_price >= 5 

CAUTION

When using multiple search conditions (using AND and OR), you should use parentheses to group clauses appropriately. They prevent ambiguity and prevent clauses from being evaluated in an unexpected order.


To negate a condition, you use the NOT operator before the condition as follows:

 SELECT product_id, product_name FROM products WHERE NOT product_id IN (1,3,7,18,45) 

Sorting

Retrieved data can be sorted using the ORDER BY clause. Data can be sorted in one or more columns .If more than one column is specified, data is sorted by the first column and then by the second, if multiple rows have the same first column value, and so on.

The following example retrieves all products and sorts them by name:

 SELECT product_id, product_name FROM products ORDER BY product_name 

Data is sorted in ascending or descending order, which you specify by using the ASC or DESC keywords, respectively. If neither keyword is provided, ASC is assumed by default.



Macromedia ColdFusion MX 7 Certified Developer Study Guide
Macromedia ColdFusion MX 7 Certified Developer Study Guide
ISBN: 0321330110
EAN: 2147483647
Year: 2004
Pages: 389
Authors: Ben Forta

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net