Join Conditions for Tables, Queries, and Views

Article
11/14/2006

When retrieving data from tables to create queries and views, you can use join conditions, or joins, to specify relationships between tables, usually based on the primary and foreign keys. 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 set join conditions for queries and views in the Query and View Designers and when using the Visual FoxPro language. For more information, see Join Tab, Query and View Designers and SELECT - SQL Command.

Specifying joins on fields other than the primary and foreign keys can be useful in specific instances; however, these types of joins are not used in most views.

Aspects of Join Conditions

The following list describes some aspects of join conditions and criteria that you can set between tables or in queries and views:

Join type between tables or between table fields in a query or view
Table fields in the join
Comparison condition operators between fields, such as Equal (=), Greater Than (>), and Between
Sequence of joins for more than two tables

Join Types

You can specify join types between tables or fields to expand or narrow the results of your search. For example, choosing a full join expands the results to include records that match and do not match the join condition.

The following table describes join types you can set to expand or narrow your data results.

Join type	Behavior
Inner join	Retrieve only those records from tables on both sides of the join that match the join criteria, or comparison condition between fields involved in the join. Inner joins are the most common type of join.
Left join	Retrieve all records from the table on the left side of the join and only those records that match the join criteria from the table on the right side of the join.
Right join	Retrieve only those records from the table on the left side of the join condition that match the join criteria but all records from the right side of the join condition.
Full join	Retrieve all records from tables on both sides of the join condition regardless of whether records match the join criteria.

For more information about join types you can set between tables, see Join Condition Dialog Box.

Join Conditions for Multiple Tables

When you use two or more tables, you can modify the results by selecting the join order of tables in the Query and View designers or in the FROM clause of the SQL SELECT statement. For more information, see Join Tab, Query and View Designers and SELECT - SQL Command.

For example, suppose you want to find information about customer orders, including information about the customer who placed the order information and the employee who made the sale. You can create a view using the customer, orders, and employee tables and specify inner join conditions for the fields they have in common: both customer and orders tables have a customer ID field; and both the orders and employee tables have an employee ID field.

The following code creates a view using the WHERE clause that retrieves the orders in the orders table matching the customers in the customer table and the employees in the employee table who took those orders:

OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_emp_view AS ;
   SELECT * FROM testdata!customer ;
      INNER JOIN testdata!orders ;
      ON customer.cust_id = orders.cust_id ;
      INNER JOIN testdata!employee ;
      ON orders.emp_id = employee.emp_id

Join Criteria in Queries and Views

In addition to modifying join types between tables, you can also change the join criteria for queries and views. You can use comparison condition operators to control which records are compared and returned, similar to a filter.

For example, you can use the Equal (=) criteria when querying two tables joined on their respective customer ID fields, where Customer.cust_id = Orders.cust_id. The query retrieves only those records in 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.

Joins do not have to be based solely on the exact field matching. You can set up different join relationships using join criteria such as on Like, Exactly Like, More Than, or Less Than.

Join criteria behave similarly to filter criteria in that both compare values and then include records that match the criteria. However, join criteria compare the field value from one table to the field value of the other table, while filter criteria compare a field value to a filter value.

For more information about the join criteria you can set for queries and views, see Join Tab, Query and View Designers.

Non-Matching Records in Results

If you want to include any rows that do not match the join criteria in your results, you can use an outer join between tables. For example, suppose you want to create a view containing a list of all customers, whether or not they have placed an order. In addition, you want order numbers for the customers who have placed orders included in the view. When you use an outer join, the empty fields of the non-matching rows return null values.

The following code illustrates this example by creating a view using a join condition that retrieves all customers from the customer table and only the order numbers from the orders table for those customers matching the customer ID number in both tables:

OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_view AS ;
   SELECT * FROM testdata!customer ;
      LEFT OUTER JOIN testdata!orders ;
      ON customer.cust_id = orders.cust_id

The following table describes join types that you can use to specify non-matching records that a query or view can include.

Join type	Behavior
Inner join	Retrieve only those records from tables on both sides of the join that match the join criteria, or comparison condition between fields involved in the join.
Left outer join	Retrieve all records from the table on the left side of the join and only those records that match the join criteria from the table on the right side of the join.
Right outer join	Retrieve only those records from the table on the left side of the join condition that match the join criteria but all records from the right side of the join condition.
Full outer join	Retrieve all records from tables on both sides of the join condition regardless of whether records match the join criteria.

Join Criteria in the WHERE Clause

When using filters, you can specify join criteria on the Filter tab in the Query and View Designers, which inserts a WHERE clause in the SQL SELECT statement generated for the query or view. However, you cannot specify a join condition as you can with the FROM clause. For remote views, a join condition always appears in the WHERE clause.

OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_emp_view AS ;
   SELECT * FROM testdata!customer, ;
      testdata!orders, testdata!employee ;
   WHERE customer.cust_id = orders.cust_id ; 
   AND orders.emp_id = employee.emp_id

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

Share via