SELECT - SQL Command - FROM Clause

Article
07/09/2007

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] ...]

Parameters

[FORCE]
FORCE specifies that the tables in the table list are joined in the order they appear in the FROM clause.

Note

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.
  
  Note
  
  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.

Note

There is no limit on the number of joins per SELECT statement.

Note

The following table describes the different types of joins you can specify with JoinType.

JoinType	Description
INNER	Query result contains only rows from a table that match one or more rows in another table. (Default)
LEFT [OUTER]	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.
RIGHT [OUTER]	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.
FULL [OUTER]	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.

Note

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:

MyUDF(Table1.Field1, Table2.Field1)

The following table lists the operators available for Comparison.

Comparison	Description
=	Equal Note 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.
==	Exactly equal
LIKE	SQL LIKE operation
<>, !=, #	Not equal
>	Greater than
>=	Greater than or equal to
<	Less than
<=	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

-OR-
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.

Option	Description
Expression	FieldName values must meet comparison with an expression for inclusion in the query results.
LIKE "cExpression"	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.
IS NULL	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.
IN (Value_Set)	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. Note 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. Tip 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.

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]
   [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:

Example

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

Partager via

SELECT - SQL Command - FROM Clause

Parameters

Remarks

Example

See Also

Reference

Other Resources

Ressources supplémentaires