SELECT is usually the first word in an SQL statement. Most SQL statements are either SELECT or SELECT ... INTO statements.
SELECT specifies the columns that are to be included in the query results. Basic syntax is:
SELECT columnItems FROM tableName
wherein columnItems can be any of the following:
- *
- ALL or DISTINCT
- a specified columnItem to include in the query results, which can be one or more of the following:
- the name of a FIELD from a TABLE in the FROM clause.
- a FIELD to contain the results of an expression.
- a FIELD to contain the results of an SQL aggregate function.
FROM is required, and follows any columnItem specified. It identifies the source of the data, specifically the table or view in which the query is to be applied.
Each item you specify with columnItem generates one column in the query results.
The asterisk ( * ) denotes to include ALL FIELDS from the specified table in the query.
SELECT * FROM tableName
The keywords ALL and DISTINCT restricts the number of RECORDS returned. If none is specified, the default is ALL.
SELECT [ All | DISTINCT ] FROM tableName
DISTINCT excludes duplicates of any rows from the query results. When used with a given field, DISTINCT excludes duplicates of any records with the same value for the given field.
strSQL="SELECT DISTINCT ProductType FROM Products"
Part II: Selecting information from a database describes an example of using DISTINCT to display only unique values ( no duplicates ) from a given field, the results of which we use in a selectable list-box to accept the user input.
Note that you can use DISTINCT only once per SELECT clause.
In most cases, SELECT is used to define a specific set of FIELDS to include in the query results:
SELECT fieldName1, fieldName2 FROM tableName
in which fieldName refers to the name( s ) of the field( s ) containing the data you want to retrieve. If you include more than one field, they are retrieved in the order listed.
The dbselect sample also uses this type of query, to return a result set based on the user input.
SELECT ProductType, ProductName, ProductDescription, UnitPrice
FROM Products . . .
SELECT also allows to create a columnItem that returns the results of a calculation defined with an expression.
The following SQL statement includes a CALCULATED field named TotalOrder in the query results, the values of which are the result of the expression Quantity*UnitPrice.
SELECT OrderID, ProductID, Quantity, UnitPrice,
Quantity*UnitPrice AS TotalOrder
FROM Order_Details
When a Recordset object is created, the provider uses the table's field name as the Field object name in the Recordset object.
SQL provides for using column names that may be different from the actual field names. This is useful when a columnItem name is not implied by the expression used to generate the field, or contains a field function, and you want to give the column a meaningful name.
This is done by including the AS keyword, as shown in the calculated field sample above.
SELECT . . . Quantity*UnitPrice AS TotalOrder . . .
AS specifies the substitute heading for a column in the query output. The given name can be any valid expression that is permitted in table field names.
In some providers, such as MS Access, the given name can contain characters not normally allowed in database field names ( for example, spaces ), as long as the name is enclosed in brackets.
SELECT . . . Quantity*UnitPrice AS [Total Order] . . .
Whenever you use aggregate functions or queries that return ambiguous or duplicate Field object names, you must use the AS clause to provide an alternate name for the Field object.
The following sections describe other clauses you can use in a SELECT statement to further confine and organize the returned data. Depending on the SQL functionality supported by the provider, some SQL command clauses may not be available.
