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
See Also
Concepts
Join Conditions for Tables, Queries, and Views
Reference
SYS(3054) - Rushmore Query Optimization Level
SYS(3099) - SQL Data Engine Compatibility Mode
Filter Conditions for Queries and Views
Considerations for SQL SELECT Statements