SELECT - SQL Command

Switch on low bandwidth view

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2005/.NET Framework 2.0

Other versions are also available for the following:

Visual FoxPro 9.0 SP2

SELECT - SQL Command

Retrieves data from one or more tables. When you use SQL SELECT to create a query, Visual FoxPro parses the query and retrieves the specified data from the tables. You can create a SQL SELECT query from the Command window, in a Visual FoxPro program, or using the Query Designer. Please review the Considerations for SQL SELECT Statements help topic for more information on using SQL SELECT.

The full syntax for the SQL SELECT command appears as follows:

SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] Select_List_Item [, ...]
   FROM [FORCE] Table_List_Item [, ...]
      [[JoinType] JOIN DatabaseName!]Table [[AS] Local_Alias]
      [ON JoinCondition [AND | OR [JoinCondition | FilterCondition] ...] 
   [WITH (BUFFERING = lExpr)]
   [WHERE JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] ...]
   [GROUP BY Column_List_Item [, ...]] [HAVING FilterCondition [AND | OR ...]]
   [UNION [ALL] SELECTCommand]
   [ORDER BY Order_Item [ASC | DESC] [, ...]]
   [INTO StorageDestination | TO DisplayDestination]
   [PREFERENCE PreferenceName] [NOCONSOLE] [PLAIN] [NOWAIT]

Remarks

The following code shows a summary of the main clauses:

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

The following sections describe the detailed syntax and parameters for each clause of the SQL SELECT command:

When you issue SET TALK ON and SET NOTIFY ON before executing SELECT, Visual FoxPro displays the length of time the query took to execute and the number of records in the results. _TALLY contains the number of records in the query results. For more information, see SET TALK Command, SET NOTIFY Command, and _TALLY System Variable.

Note
The length of time might not display if the query execution time is very short.

SELECT does not respect the current filter condition specified with SET FILTER. For more information, see SET FILTER Command.

Examples

The following code examples show many ways of retrieving data with the SQL SELECT command and use the sample TestData.dbc database included in Visual FoxPro.

Example 1

The following example displays data from field in a table using the FROM clause. The example displays all the company names in the Company field from the Customer table:

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT customer.company ;
   FROM customer

Example 2

The following example displays all records from a query in a specified column using the AS Column_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

Example 3

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 4

The following example displays three fields from two tables, but only those records meeting join and filter conditions in the WHERE clause. The example joins the Customer and Orders table on the Cust_ID field and displays the Company, Order_Date, and Shipped_On fields for only those records that have an order date earlier than 02/16/1994. 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 TAlias1.company, TAlias2.order_date, TAlias2.shipped_on ;
   FROM customer TAlias1, orders TAlias2 ;
   WHERE TAlias1.cust_id = TAlias2.cust_id ;
   AND TAlias2.order_date < {^1994-02-16}

Example 5

The following example organizes results of a query using the ORDER BY Order_Item clause. The example displays the Country, PostalCode, and Company fields for those records in the Customer table in ascending order, by default.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT country, postalcode, company ;
   FROM customer ;
   ORDER BY country, postalcode, company

Example 6

The following example stores the contents of a query into a table using INTO TABLE TableName clause. The example stores the contents of the Company, Order_Date, and Shipped_On fields from the Customer and Order tables, which are joined on the Cust_ID field, in a third table called CustShip. The example then opens a Browse window for the new table. 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 TAlias1.company, TAlias2.order_date, TAlias2.shipped_on ;
   FROM customer TAlias1, orders TAlias2 ;
   WHERE TAlias1.cust_id = TAlias2.cust_id ;
   INTO TABLE custship.dbf
BROWSE

Example 1

Example 2

Example 3

Example 4

Example 5

Example 6

Reference

Concepts

Other Resources