SELECT Statement

Description

Instructs the Microsoft Jet database engine to return information from the database as a set of records.

Syntax

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2
[AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

The SELECT statement has these parts.

Part	Description

predicate	One of the following predicates: ALL, DISTINCT, DISTINCTROW, or TOP. You use the predicate to restrict the number of records returned. If none is specified, the default is ALL.
*	Specifies that all fields from the specified table or tables are selected.
table	The name of the table containing the fields from which records are selected.
field1, field2	The names of the fields to retrieve data from. If you include more than one field, they are retrieved in the order listed.
alias1, alias2	The names to use as column headers instead of the original column names in table.
tableexpression	The name of the table or tables containing the data you want to retrieve.
externaldatabase	The name of the database containing the tables in tableexpression if not in the current database.

Remarks

To perform this operation, the Jet database engine searches the specified table or tables, extracts the chosen columns, selects rows that meet the criterion, and sorts or groups the resulting rows into the order specified.

SELECT statements don't change data in the database.

SELECT is usually the first word in an SQL statement. Most SQL statements are either SELECT or SELECT... INTO statements.

The minimum syntax for a SELECT statement is:

SELECT fields FROM table

You can use an asterisk (*) to select all fields in a table. The following example selects all of the fields in the Employees table:

SELECT * FROM Employees;

If a field name is included in more than one table in the FROM clause, precede it with the table name and the . (dot) operator. In the following example, the Department field is in both the Employees table and the Supervisors table. The SQL statement selects Department from the Employees table and SupvName from the Supervisors table:

SELECT Employees.Department, Supervisors.SupvName
FROM Employees INNER JOIN Supervisors
WHERE Employees.Department = Supervisors.Department;

When a Recordset object is created, the Jet database engine uses the table's field name as the Field object name in the Recordset object. If you want a different field name or a name isn't implied by the expression used to generate the field, use the AS reserved word. The following example uses the title Birth to name the returned Field object in the resulting Recordset object:

SELECT BirthDate
AS Birth FROM Employees;

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 example uses the title Head Count to name the returned Field object in the resulting Recordset object:

SELECT COUNT(EmployeeID)
AS HeadCount FROM Employees;

You can use the other clauses in a SELECT statement to further restrict and organize your returned data. For more information, see the topic for the clause you're working with.

See Also

ALL, DISTINCT, DISTINCTROW, TOP Predicates; DELETE Statement, FROM Clause, GROUP BY Clause, HAVING Clause, IN Clause, INSERT INTO Statement, ORDER BY Clause, SELECT...INTO Statement, SQL Aggregate Functions, UNION Operation, UPDATE Statement, WHERE Clause, WITH OWNERACCESS OPTION Declaration.

Specifics (Microsoft Access)

In Microsoft Access, if you are working in SQL view in the Query window, field names (field1, field2) are used as column headings in Datasheet view. To display a different column heading for a column in the Datasheet, use the AS reserved word. If you use the AS reserved word, then the alias1, alias2 arguments provide the column names to use in displaying the retrieved data in Datasheet view. Using the AS clause is equivalent to setting the Alias property in the field list property sheet in query Design view.

The following example uses the title "Birthday" to head the column in the resulting datasheet.

SELECT [BirthDate] AS Birthday FROM Employees;

When using aggregate functions or queries that return ambiguous or duplicate field names, you must use the AS clause to provide an alternate name for the field. The following example creates the column heading "Head Count" in Datasheet view.

SELECT COUNT([EmployeeID])
AS [HeadCount] FROM Employees;

If you are working with data access objects in Visual Basic code, the field1, field2 arguments are used to name the Field objects in the Recordset object returned by the query. If you include the AS keyword, then the alias1, alias2 arguments provide the column headings to return as Field object names in the resulting Recordset object.

Example

Some of the following examples assume the existence of a hypothetical Salary field in an Employees table.

This example selects the LastName and FirstName fields of all records in the Employees table.

SELECT LastName, FirstName FROM Employees;

This example selects all fields from the Employees table.

SELECT Employees.* FROM Employees;

This example counts the number of records that have an entry in the PostalCode field and names the returned field Tally.

SELECT Count(PostalCode) AS Tally FROM Customers;

This example shows what the salary would be if each employee received a 10 percent raise. It doesn't change the original salary amounts.

SELECT LastName, Salary AS Current, Salary * 1.1 AS Proposed 
FROM Employees;

This example places the title Name at the top of the LastName column. The title Salary is displayed at the top of the Salary column.

SELECT LastName AS Name, Salary FROM Employees;

This example shows the number of employees and the average and maximum salaries.

SELECT Count(*) AS [Total Employees], Avg(Salary) 
AS [Average Salary], Max(Salary) AS [Maximum Salary] FROM Employees;

For each record, this example shows the LastName and Salary in the first and last fields. The string "has a salary of" is returned as the middle field of each record.

SELECT LastName, 'has a salary of', Salary FROM Employees;