SELECT Statement

Selects data from existing tables or queries.

 

Syntax

 

atom [{UNION | EXCEPT | INTERSECT} [ALL] atom ...] [ORDER BY ...]

 

where atom is:

 

SELECT [ALL | DISTINCT | DISTINCTROW] [SKIP n [PERCENT]]

[TOP n [PERCENT]] column [, column ...]

FROM table [, table ...]

[WHERE ...] [SPLIT BY ...] [LEAVING ...] [GROUP BY ...]

[HAVING ...]

or:

 

TABLE table

 

or:

 

VALUES ({value | NULL}[, {value | NULL} ...]) [, ({value | NULL}[, {value | NULL} ...]) ...] [NAMES (name[, name ...])]

 

The SELECT statement has these parts:

 

Part

Description

column

The name of a column or an asterisk, possibly qualified with the name of a table as in [Table].[Column], or [Table].*, or an expression. Can include an alias. A non-qualified asterisk selects all columns, and must be the only item in the column list.

n

The number of records in SKIP or TOP or percent in SKIP PERCENT or TOP PERCENT.

name

The name to use for a column.

table

The name of a table containing the data for a TABLE variant. The name of a table containing data (with a possible alias, and column filter), or another query, or several tables or queries joined together with one of the join operators, for a SELECT variant.

value

A literal value, or an expression, or a query that returns a single value.

 

Remarks

 

SELECT statements don't change data. The minimum syntax for a SELECT statement is:

 

SELECT columns FROM table;

 

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

 

SELECT * FROM [Employees];

 

We may restrict the table to return only the columns we want with a table filter. The following example restricts the output of the above query to just the "First Name," and "Last Name" columns:

 

SELECT [First Name], [Last Name] FROM [Employees];

 

Alternatively, we may specify the columns we want in the FROM clause. The following example is identical to the above query but uses the FROM clause:

 

SELECT * FROM [Employees] ([First Name], [Last Name]);

 

If a column name is included in more than one table in the FROM clause, precede it with the table name and a dot (.). In the following example, the "Category ID" column is in both the "Products" table and the "Categories" table. The statement selects product names from the "Products" table and category names from the "Categories" table:

 

 

SELECT [Products].[Product Name], [Categories].[Category Name]

FROM [Products] INNER JOIN [Categories]

ON [Products].[Category ID] = [Categories].[Category ID];

 

When a data set is returned in answer to a query, the SQL engine uses column names from the originating tables in the data set returned. If we want a different column name or a name isn't implied by the expression used to generate the column, we can use column aliases. The following example renames the returned column "Birth Date" to just "Birth":

 

SELECT [Birth Date] AS [Birth] FROM [Employees];

 

The AS keyword is optional so we might rewrite the above query as:

 

SELECT [Birth Date] [Birth] FROM [Employees];

 

However, because of the clarity the AS keyword brings to a query it is madness not to use it. Keep in mind that queries we write today might not be so obvious in intent when we reread them in the future.

 

In addition to providing alternate names for columns, we could also provide alternate names for tables. This can be useful for shortening expressions inside the query and for handling complex joins. The following example displays the name of the category for each product shortening the names of the involved tables for clarity:

 

SELECT [P].[Product Name], [C].[Category Name]

FROM [Products] AS [P] INNER JOIN [Categories] AS [C]

ON [P].[Category ID] = [C].[Category ID];

 

Whenever we write queries that return ambiguous or duplicate column or table names, we must use aliases to provide alternate names.

 

The TABLE variant of the SELECT statement returns all data in a given table. The following statements are equivalent:

 

SELECT * FROM [Employees];

TABLE [Employees];

 

The VALUES variant of the SELECT statement synthesizes a table from the supplied values and is typically used with INSERT INTO or with sub-queries. The following statement synthesizes a table with a single row and two columns with values 1 and "ABC" (note the use of the concatenation operator in the second value):

 

VALUES (1, "AB" & "C");

 

The WHERE, UNION, and other components of the SELECT statement restrict and organize the returned data. For more information, see the Help topic for the component being using.

 

Examples

 

Select the "Product Name," and "Unit Price" columns of all records in the "Products" table:

 

SELECT [Product Name], [Unit Price] FROM [Products];

 

Count the total number of products and name the returned column "Total Products":

 

SELECT Count(*) AS [Total Products] FROM [Products];

 

Show the minimum, maximum, and average prices for products:

 

SELECT Min([Unit Price]) AS [Min Price], Max([Unit Price]) AS [Max Price], Avg([Unit Price]) AS [Avg Price] FROM [Products];

 

Create a table with one record and three integer columns with values 1, 2, 3:

 

VALUES (1, 2, 3)

 

Create a table with one integer column and three records with values 1, 2, 3:

 

VALUES (1), (2), (3)

 

Create a table with three records, an integer column and a text column:

 

VALUES (1, "a"), (2, "b"), (3, "c")

 

Create a table with two integer columns named "x" and "y" and three records:

 

VALUES (1, 1), (2, 2), (3, 3) NAMES (x, y)

 

 

See Also

 

ALL, DISTINCT, SKIP, TOP Quantifiers