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

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.

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](f4saeycb\(v=vs.90\).md). 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] > <P>The number of values or expressions you can specify for Value_Set is affected by the setting of <A href="43cab295(v=vs.90).md">SYS(3055) – FOR and WHERE Clause Complexity</A>.</P> > [!TIP] > <P>Visual FoxPro might stop evaluating values and expressions in the Value_Set list when the first match is found. Therefore, if the <STRONG>IN</STRONG> clause is not Rushmore optimized, you can improve performance by placing values most likely to match in the beginning of the Value_Set list.</P>

  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:

  • SELECT 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

  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
  

  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