Join Conditions for Tables, Queries, and Views

Visual FoxPro supports SQL-92 Join syntax, allowing you to create queries that link rows in two or more tables by comparing the values in specified fields. When retrieving data from tables to create queries and views, you can use join operations and join conditions to control how records are compared and which ones are returned and specify relationships between tables, usually based on the primary and foreign keys. Specifying joins on fields other than the primary and foreign keys can be useful in specific instances; however, most queries do not use joins on non-key fields.

This topic contains the following sections:

• Parts of Join Operations

• Join Types

• Join Sequence

• Join Conditions

• Join Considerations

You can specify join operations and join conditions for queries and views in the Query and View designers and by using the Visual FoxPro language. For more information, see Join Tab, Query and View Designers and SELECT - SQL Command.

Parts of Join Operations

The following list contains parts of join operations that you can set between tables in queries and views:

• Table fields in the join.

• Join type between tables or between table fields in a query or view.

• Join sequence.

• Join conditions between fields using operators such as Between, Equal (=), Greater Than (>), and Less Than (<).

Join Types

Because SQL is based on mathematical set theory, each table can be represented as a circle in a Venn diagram. The ON clause in the SQL SELECT statement that specifies join conditions determines the point of overlap for those circles and represents the set of rows that match. For example, in an inner join, the overlap occurs within the interior or "inner" portion of the two circles. An outer join includes not only those matched rows found in the inner cross section of the tables, but also the rows in the outer part of the circle to the left or right of the intersection.

You can expand or narrow the results of your search by specifying the type of join you want between tables. The following table describes join types you can specify.

Exactly Matching Records in Results

If you want to retrieve only those records that match the join condition from the tables on both sides of the operation, use an inner join.

For example, suppose you want to create a query containing only those companies that have orders and display only those order numbers for those companies. The following code example creates a query that retrieves only those company names from the Customer table that have orders in the Orders table and the order numbers for only those companies. The example uses an inner join to display only those records from both tables that match the join condition based on the Cust_ID field.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT TAlias1.company, TAlias2.order_ID ;
   FROM customer AS TAlias1 ;
   INNER JOIN orders AS TAlias2 ;
      ON TAlias1.cust_id = TAlias2.cust_id ;
   ORDER BY TAlias1.company ASC

Multiple Join Operations

You can specify more than one join operation to include data from other tables.

For example, suppose you want to create a query containing only those companies that have orders, only those order numbers for those companies, and the employees who took the orders. The following code example creates a query that retrieves only those company names from the Customer table that have orders in the Orders table, the order numbers for only those companies, and those names belonging to employees who took those orders. The example uses two inner join clauses to display only those records from the three tables that match on the Cust_ID and Emp_ID fields.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT TAlias1.company, TAlias2.order_ID, ;
   TAlias3.first_name, TAlias3.last_name ;
   FROM customer AS TAlias1 ;
   INNER JOIN orders AS TAlias2 ;
      ON TAlias1.cust_id = TAlias2.cust_id ;
   INNER JOIN employee AS TAlias3 ;
      ON TAlias2.emp_id = TAlias3.emp_id ;
   ORDER BY TAlias1.company ASC

Nonmatching Records in Results

If you want to include any rows that do not match the join conditions in your results, you can use a left outer, right outer, or full join between tables. When you use an outer join, the empty fields of the nonmatching rows return null values.

For example, suppose you want to create a query containing a list of all customers, regardless of whether they have placed an order, and order numbers for those customers who have placed orders. The following code example uses a left outer join to display all the records from a field in one table and those records in another table that match on the join condition.

This example creates a query against the Visual FoxPro sample database, TestData.dbc, that retrieves all company names from the Customer table and only those records in the Orders table that have customer ID numbers that match in both tables. However, not all the records that match have order numbers for all the companies in the Customer table.

CLOSE ALL
CLOSE DATABASES

OPEN DATABASE (HOME(2) + 'Data\TestData')

SELECT TAlias1.company, TAlias2.order_id ;

   FROM customer TAlias1 ;

   LEFT JOIN orders TAlias2 ;

      ON TAlias1.cust_id = TAlias2.cust_id

All but one of the company names in the query have order numbers in the Order_ID column of the query. Wenna Wines contains contains a null value in the Order_ID column.

For more information about join types you can set between tables in the Query Designer and View Designer, see Join Condition Dialog Box.

Join Sequence

When you need data from multiple tables, the results you want and the join type you select affect the position and join sequence in which you place the tables in your query. You can specify the order in which tables are joined in the Query and View designers or in the JOIN clause of the SQL SELECT statement.

For example, suppose you want all the records from a field in one table and only those records in another table that match on the join condition. You could use the LEFT JOIN operation and place the first table on the left side and the second table on the right side of the JOIN clause. However, if you selected a RIGHT JOIN operation, you would need to reverse the order of these two tables to obtain the same results.

The following code example creates a query that displays three sets of results, but all of them retrieve all values from the field in the first table and only those values from the field in the second table that meet the join condition. The join condition is specified by the ON clause.

CLOSE ALL
CLOSE DATABASES

OPEN DATABASE (HOME(2) + 'Data\TestData')

For the first SQL SELECT statement, the query retrieves all the names from the Company field in the Customer table and only those order numbers from the Order_ID field in the Orders table that match on the Cust_ID field.

SELECT TAlias1.company, TAlias2.order_id ;

   FROM customer TAlias1 ;

   LEFT JOIN orders TAlias2 ;

      ON TAlias1.cust_id = TAlias2.cust_id

For the second SQL SELECT statement, the LEFT JOIN operation is replaced with a RIGHT JOIN operation, but the sequence of tables has not changed. However, the result set is different. The query retrieves all the order numbers from the Order_ID field in the Orders table for those records that match on the Cust_ID field in both tables. In this example, no order number should exist without a matching customer record, so no null values appear in the Company column. However, only those names from the Company field in the Customer table appear where the Cust_ID field in the Customer table matches the Cust_ID field in the Orders table.

SELECT TAlias1.company, TAlias2.order_id ;

   FROM customer TAlias1 ;

   RIGHT JOIN orders TAlias2 ;

      ON TAlias1.cust_id = TAlias2.cust_id

For the third SQL SELECT statement, the RIGHT JOIN operation remains but the sequence of tables is reversed. However, the results are the same as the first SQL SELECT statement.

SELECT TAlias1.company, TAlias2.order_id ;

   FROM orders TAlias2 ;

   RIGHT JOIN customer TAlias1 ;

      ON TAlias1.cust_id = TAlias2.cust_id

For more information, see Join Tab, Query and View Designers and SELECT - SQL Command.

Join Conditions

Join conditions specify conditions on which the tables in a SQL SELECT statement are joined. For example, you can use join conditions to compare values in two tables that have common fields and return only those records where those fields have the same values.

You can customize the join condition for queries and views by using comparison operators to control how records are compared and which ones are returned, similar to using a filter condition. However, join conditions compare the field value in one table to the field value in another other table, while filter conditions compare a field value to a filter value. For information about filter conditions, see Filter Conditions for Queries and Views.

For example, you can use the equal (=) operator when querying two tables joined on their respective customer ID fields, such as Customer.cust_id = Orders.cust_id. This query retrieves only those records on which these two fields match and meet any other filter you set in the query. As another example, if you are using a date field in a join, you can use a comparison condition operator to include only records before or after a certain date.

The following table lists the comparison operators available for join conditions.

You can specify join conditions with either the ON clause or the WHERE clause of a SQL SELECT statement but not both.

For example, suppose you want all the records from a field in one table and only those values for two fields in another table that meet a join condition and a filter condition. The following code example uses the ON clause to specify a join condition, which includes a filter condition specified by the AND clause.

This example creates a query that retrieves all the names in the Company field from the Customer table and only those records that match on the Cust_ID field in the Orders table. However, not all records that match in the Orders table contain dates in the Order_Date and Shipped_On fields that pass the filter criteria, having an order date earlier than 02/16/1994. If a company does not have order dates that meet the filter condition, the Order_Date and Shipped_On columns show null values.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT TAlias1.company, TAlias2.order_date, TAlias2.shipped_on ;
   FROM customer TAlias1 ;
   LEFT JOIN orders TAlias2 ;
      ON TAlias1.cust_id = TAlias2.cust_id ;
         AND TAlias2.order_date < {^1994-02-16}

For more information about the join conditions you can set for queries and views, see SELECT - SQL Command and Join Tab, Query and View Designers.

Join Conditions in the WHERE Clause

When writing SQL SELECT statements, you can specify join conditions in the WHERE clause along with filter conditions. When specifying filters for queries and views in the Query and View designers, the Filter tab inserts a WHERE clause into the SQL SELECT statement generated for the query or view. You can specify a join condition in the Filter tab or edit the WHERE clause in the SQL SELECT statement by viewing and editing it in the SQL window. For remote views, a join condition always appears in the WHERE clause.

For example, suppose you want customer order information, including all information about the customer and the employee who took the order. The following code example uses the WHERE clause to specify a join condition matching records on the Cust_ID field in the Customer and Orders tables and, in addition, a join condition matching the employee number in the Emp_ID field for the Orders and Employee tables.

This example retrieves only those customer records in the Customer table that match orders in the Orders table with employee records in the Employee table.

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT * FROM customer TAlias1, orders TAlias2, employee TAlias3 ;
   WHERE TAlias1.cust_id = TAlias2.cust_id ; 
   AND TAlias2.emp_id = TAlias3.emp_id

For more information about the WHERE clause, see Filter Tab, Query and View Designers and SELECT - SQL Command.

Join Considerations

When creating join operations, be aware of the following considerations:

• If you include two tables in a query and do not specify a join condition, every record in the first table is joined with every record in the second table as long as the filter conditions are met. Such a query can produce lengthy results.

• Use caution when using functions such as DELETED( ), EOF( ), FOUND( ), RECCOUNT( ), and RECNO( ), which support an optional alias or work area. Including an alias or work area in these functions might yield unexpected results. SQL SELECT does not use your work areas; it performs the equivalent of USE ... AGAIN. Single-table queries that use these functions without an optional alias or work area return proper results. However, multiple-table queries that use these functions, even without an optional alias or work area, might return unexpected results.

• Use caution when joining tables that contain empty fields because Visual FoxPro matches empty fields. For example, suppose you perform a join on the Customer.zip and Invoice.zip fields in the Customer and Invoice tables in the sample database, TestData.dbc. If the Customer table contains 100 empty zip codes, and the Invoice table contains 400 empty zip codes, the query output contains 40,000 extra records resulting from the empty fields. To eliminate empty records from the query output, use the EMPTY( ) function.

See Also

Tasks

How to: Optimize Filters and Joins

Reference

CREATE SQL VIEW Command

SELECT - SQL Command

Other Resources

General Reference

Working with Queries

Working with Views (Visual FoxPro)

Query and View Designers