Home > Data Access / ADO.NET > Structured Query Language (SQL) Basics > SQL Select: Specifying Data to be Included in a Query

---

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)
• DISTINCT
• a specified item to include in the query results, which can be one or more of the following:
  • the name of a COLUMN from a TABLE in the FROM clause.
  • a COLUMN to contain the results of an expression.
  • a COLUMN 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 columns from the specified table in the query.

SELECT * FROM tableName

SELECT * Example Run Sample | View Source

The keyword DISTINCT restricts the number of records returned.

SELECT DISTINCT * FROM tableName

or

SELECT DISTINCT columnName FROM tableName

DISTINCT excludes duplicates of any rows from the query results. When used with a given column, DISTINCT excludes duplicates of any records with the same value for the given column.

The following demonstrates using DISTINCT to display only unique values ( no duplicates ) from a given column, the results of which we use in a selectable list-box to accept user input.

SELECT DISTINCT Example Run Sample | View Source

NOTE: DISTINCT can only be used once per SELECT clause.

In many cases, SELECT is used to define a specific set of columns to include in the query results:

SELECT column1Name, column2Name FROM tableName

in which columnName refers to the name( s ) of the column( s ) containing the data you want to retrieve. If you include more than one column, they are retrieved in the order listed.

SELECT ProductCode, ProductType, ProductName, ProductDescription FROM Products

SELECT ColumnNames Example Run Sample | View Source

By default, SQL returns the data source’s actual column names as headers in a query result set.

In certain cases, though, we may want to use different column headers from the actual table column names.

This is useful when a columnItem name is not implied by the expression used to generate the column, and we want to give the column a meaningful name.

This is done by including the AS keyword, as shown below.

SELECT au_id AS AuthorID, au_lname AS LastName, au_fname AS FirstName, ...

AS specifies the substitute heading for a column in the query output.

Using SELECT ... AS Run Sample | View Source

The given name can be any valid expression that is permitted in table column names.

Whenever queries include aggregate functions or table joins that return ambiguous or duplicate column names, the AS keyword must be used to provide an alternate name for the column header.

SELECT also allows to create a columnItem that returns the results of a calculation defined with an expression.

The following SELECT statement includes a calculated column named TotalAmount in the query results, the values of which are the result of the expression Quantity * UnitPrice.

SELECT OrderID, ProductID, Quantity, UnitPrice, Quantity * UnitPrice AS TotalAmount FROM [Order Details]

Note that table names can contain spaces not normally allowed, as long as the table name is enclosed in brackets.

SELECT with Calculated Expression Run Sample | View Source

Similarly, we must use the AS keyword to specify a relevant heading, particularly since the column contains a calculated expression.

Likewise, the given name can contain spaces, as long as the column name is enclosed in brackets.

SELECT ... Quantity * UnitPrice AS [Total Amount] ...

SELECT ... INTO creates a make-table query.

SELECT column1, column2 INTO newTable FROM sourceTable

The name of the newTable must not be an existing table, otherwise an error occurs.

sourceTable is the existing table from which records are selected, and can be single or multiple tables.

SELECT ... INTO provides a quick way to make a database table, without having to declare each column name, size, and data type such as when doing a standard CREATE TABLE query.

This is particularly useful for queries that include only a limited subset of data from a large table.

For example, you can easily produce a monthly or yearly sales or orders table by running a SELECT ... INTO query that defines the specified month or year in the WHERE clause, as discussed in the next section.

NOTE: The columns in the new table inherit the data type and size of each column in the query’s underlying table(s), but no other column or table properties are transferred, such as primary keys and constraints.

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.

SQL WHERE: Specifying A Filter Condition