>
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. |
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 BirthDateWhenever 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:
AS Birth FROM Employees;
SELECT COUNT(EmployeeID)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.
AS HeadCount FROM Employees;
SELECT [BirthDate] AS Birthday FROM Employees;
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 ProposedThis example places the title Name at the top of the LastName column. The title Salary is displayed at the top of the Salary column.
FROM Employees;
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)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.
AS [Average Salary], Max(Salary) AS [Maximum Salary] FROM Employees;
SELECT LastName, 'has a salary of', Salary FROM Employees;