SELECT - SQL Command - SELECT Clause

The SQL SELECT clause specifies the fields, constants, and expressions to display in the query results.

For the complete syntax, see SELECT - SQL Command.

The detailed syntax for the SELECT clause is as follows:

SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] Select_List_Item [AS Column_Name] [, ...]

Parameters

• [ALL | DISTINCT]
  Display all rows in the query results, by default, or exclude duplicates of any rows from the query results. You can use DISTINCT only once per SELECT clause.

  Note

  Visual FoxPro does not support using the DISTINCT clause in SQL SELECT statements that contain Memo, General, or Blob fields. Instead, for Memo fields, you can wrap a Memo field expression inside a function such as PADR( ) or ALLTRIM( ). For more information, see PADL( ) | PADR( ) | PADC( ) Functions and ALLTRIM( ) Function.

• [TOP nExpr [PERCENT]]
  Specifies that the query result contain a specific number of rows or a percentage of rows of the query result. For nExpr, you can specify 1 to 32,767 rows, or, if you include the PERCENT option, you can specify 0.01 to 99.99 percent. Visual FoxPro sorts records first and then extracts the TOPnExpr [PERCENT] records.

  Note

  When you include the TOP clause, you must include an ORDER BY clause. The ORDER BY clause specifies the columns for which the TOP clause determines the number of rows to include in the query result.

  Note

  The setting of the SET ENGINEBEHAVIOR command affects the results and performance of the TOP clause. For more information, see SET ENGINEBEHAVIOR Command.

  PERCENT rounds up the number of rows returned in the result to the next highest integer.

  Note

  Rows with identical values for the columns specified in the ORDER BY clause are included in the query result. Therefore, a query result can contain more rows than specified with nExpr. For example, if you specify 10 for nExpr, the query result can contain more than 10 rows when more than 10 rows have identical values as specified with ORDER BY.

• Select_List_Item
  Specifies one or more items to match and include in the query results. Each item in the list generates one column in the query results. Select_List_Item can specify the following items:

  • A constant value that appears in every row of the query results.

  • An expression that can contain user-defined functions or subqueries.

  • [Alias.]Select_Item 

    Alias specifies a table alias when you are retrieving two or more Select_Item fields with the same name from multiple tables. Using a table alias prevents columns from being duplicated. A space between [Alias.] and Select_Item is permitted.

    Select_Item can also be a field from a table in the FROM clause.

  • (Subquery)

    Subquery specifies a SQL SELECT statement within another SQL SELECT statement and must be enclosed within parentheses (()).

    There is no limit on the number of subqueries per SQL SELECT statement. There is no limit on the nesting depth for subqueries in a SQL SELECT statement.

    You can use correlated subqueries up to the immediate parent. A correlated subquery uses fields in the parent query, and the subquery is executed for each candidate row in the parent query.

    Subqueries can contain TOPnExpr clauses for noncorrelated subqueries, multiple join conditions, and GROUP BY clauses. If a subquery does not return any records, it returns NULL.

    Note

    If you include the TOPnExpr clause in a subquery, you must include an ORDER BY clause for that subquery.

  You can use aggregate functions with a Select_List_Item that is a field or an expression involving a field. When specifying aggregate functions associated with Select_Item, use the following syntax:

  AggregateFunction([Alias.]Select_Item)
  

  For more information about using user-defined functions, aggregate functions, and rules applying to column names, see Considerations for SQL SELECT Statements.

• [AS Column_Name]
  Specifies a name for a column in the query output. Column_Name can be an expression, but it cannot contain characters that are not permitted, for example, spaces, in table field names.

  Tip

  When Select_List_Item is an expression or contains a field function, specifying Column_Name is useful when you want to give the column a meaningful name.

Remarks

The following code shows a summary of the main clauses of the SELECT - SQL Command:

SELECT Select_List
   FROM Table_List
...[WITH (BUFFERING = lExpr)]
   [WHERE Conditions]
   [GROUP BY Column_List]
   [HAVING Conditions]
   [UNION Clause]
   [ORDER BY Column_List]
   [INTO Clause | TO Clause ]
   [Additional_Display_Options]

For more information about a particular clause of the SQL SELECT command, see the following topics:

• FROM Clause

• SELECT - SQL Command - WITH Clause

• SELECT - SQL Command - WHERE Clause

• GROUP BY Clause

• HAVING Clause

• UNION Clause

• ORDER BY Clause

• INTO or TO Clause

• Additional Display Options

Examples

Example 1

The following example displays unique data from a query using the DISTINCT option with a join condition in the WHERE clause. The example displays the Company, Order_Date, and Shipped_On fields for only those records with unique data in the Customer and Orders tables. The SELECT statement specifies local aliases for the tables to distinguish the same field name, Cust_ID, in both tables.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT DISTINCT TAlias1.company, TAlias2.order_date, ;
   TAlias2.shipped_on ;
   FROM customer TAlias1, orders TAlias2 ;
   WHERE TAlias1.cust_id = TAlias2.cust_id

Example 2

The following example displays all records from a query in a specified column using the ASColumn_Name clause. The example displays all names in the City field from the Customer table in uppercase using the UPPER( ) function and in column called CityList.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT UPPER(city) AS CityList FROM customer

See Also

Reference

CREATE QUERY Command
CREATE TABLE - SQL Command
MODIFY QUERY Command

Other Resources

Working with Queries
Working with Views (Visual FoxPro)
Query and View Designers