SELECT - SQL Command - FROM Clause
The FROM clause specifies one or more tables containing the data that the query retrieves from.
For the complete syntax, see SELECT - SQL Command.
The detailed syntax for the FROM clause is as follows:
FROM [FORCE] Table_List_Item [, ...] [[JoinType] JOIN DatabaseName!]Table [[AS] Local_Alias] [ON JoinCondition [AND | OR [JoinCondition | FilterCondition] ...]
FORCE specifies that the tables in the table list are joined in the order they appear in the FROM clause.
If FORCE is omitted, Visual FoxPro attempts to optimize the query. However, the query might be executed faster by including the FORCE keyword to disable Visual FoxPro query optimization.
- Table_List_Item [, ...]
Specifies one or more tables that contain the data the query retrieves from.
Table_List_Item can have the following syntaxes:
[DatabaseName!]Table [[AS] Local_Alias]
DatabaseName! specifies the name of a database containing the table if the table is in a noncurrent database. If the table is in a noncurrent database, you must include the name of database. Use an exclamation point (!) as a delimiter immediately following the database name and preceding the table name.
Table specifies the name of the table you want to retrieve data from. If no table is open, Visual FoxPro displays the Open dialog box so you can specify the file location. After the table opens, it remains open when the query is complete.
Local_Alias specifies a temporary name for the table specified in Table. If you specify a local alias, you must use the local alias instead of the table name throughout the SELECT statement.
There is no limit on the number of tables and aliases per SELECT statement. You can also specify multiple tables by using one or more JOIN clauses.
(Subquery) AS Subquery_Alias
Subquery specifies a SELECT statement within another SELECT statement.
In the FROM clause, each subquery requires an alias.
Subqueries in the FROM clause do not have the restrictions that apply to subqueries in filter conditions and can use any SQL SELECT clauses, including UNION clauses. All subqueries in the FROM clause are executed before the main SELECT statement is evaluted.
- [[ JoinType] JOIN DatabaseName!] Table[[AS] Local_Alias]
Specifies a JOIN clause for retrieving data from more than one table. Visual FoxPro supports nested joins.
There is no limit on the number of joins per SELECT statement.
The following table describes the different types of joins you can specify with JoinType.
Query result contains only rows from a table that match one or more rows in another table. (Default)
Query result contains all rows from the table to the left of the JOIN keyword and only matching rows from the table to the right of the JOIN keyword. The OUTER keyword is optional; include it to clarify that an outer join is created.
Query result contains all rows from the table to the right of the JOIN keyword and only matching rows from the table to the left of the JOIN keyword. The OUTER keyword is optional; it can be included to clarify that an outer join is created.
Query result contains all matching and nonmatching rows from both tables. The OUTER keyword is optional; you can include it to clarify that an outer join is created.
For more information about the [DatabaseName!]Table [[AS] Local_Alias] clause, see the FROM clause. For more information about join operations, see Join Conditions for Tables, Queries, and Views.
- [ON JoinCondition| FilterCondition[AND | OR [ JoinCondition| FilterCondition] ...]
Specifies conditions on which tables in a SQL SELECT statement are joined or results are filtered. Join conditions can also include filter conditions. For multiple join or filter conditions, you must use the AND or OR operator to connect those conditions. You can include subqueries and nested subqueries in the ON clause.
If you include more than one table in a query, you should specify a join condition for every table after the first.
JoinCondition specifies conditions that can contain the following:
A comparison condition between fields from different tables, for example:
FieldName1 Comparison FieldName2
FieldName1 specifies a field name from one table, and FieldName2 specifies a field name from another table.
A comparison condition containing expressions involving fields from different tables, for example:
Table1.Field1 + Table2.Field1 Comparison Table3.Field1
A user-defined function, for example:
The following table lists the operators available for Comparison.
When you use the equal (=) operator with strings, it functions differently depending on the setting of SET ANSI. When SET ANSI is set to OFF, Visual FoxPro compares strings only to the end of the shorter string. When SET ANSI is set to ON, Visual FoxPro follows ANSI standards for string comparisons. For more information, see SET ANSI and SET EXACT.
SQL LIKE operation
<>, !=, #
Greater than or equal to
Less than or equal to
For more information about join conditions, see Join Conditions for Tables, Queries, and Views.
FilterCondition specifies a logical expression that describes filter criteria that records must meet to be included in the query results. You can include multiple filter conditions in a query by connecting them with the AND or OR operator. To reverse the value of a logical expression, use the NOT operator. To check for an empty field, use the EMPTY( ) function.
FilterCondition can use the syntaxes described for JoinCondition except the fields specified are in the same table. In addition, FilterCondition can specify the following syntaxes:
FieldName Comparison Expression
FieldName [NOT] LIKE cExpression | IS [NOT] NULL | [NOT] BETWEEN Start_Range AND End_Range | [NOT] IN Value_Set
For more information about valid operators for Comparison, see the description for JoinCondition.
The following table describes other options available when specifying FilterCondition.
FieldName values must meet comparison with an expression for inclusion in the query results.
FieldName values must match the character expression, cExpression, which can contain SQL wildcard characters, such as percent (%) and underscore (_), for inclusion in the query results.
The % character represents any sequence of unknown characters in the string. The _ character represents a single unknown character in the string. You can use the ESCAPE clause to specify wildcard characters as literals.
The LIKE "sometext%" filter condition is fully optimized.
FieldName value must be null for inclusion in the query results.
BETWEEN Start_Range AND End_Range
FieldName values must be within a specified range of values for inclusion in the query results.
FieldName must contain one of the values or expressions specified in Value_Set for inclusion in the query results. When listing items in the value set, separate each item with commas.
The number of values or expressions you can specify for Value_Set is affected by the setting of SYS(3055) – FOR and WHERE Clause Complexity.
Visual FoxPro might stop evaluating values and expressions in the Value_Set list when the first match is found. Therefore, if the IN clause is not Rushmore optimized, you can improve performance by placing values most likely to match in the beginning of the Value_Set list.
For more information and examples about filter conditions, see Filter Conditions for Queries and Views.
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] [UNION Clause] [HAVING Conditions] [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:
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