Joins and Data Analysis

Introduction

As demonstrated so far and in previous lessons, the main reason for creating queries is to isolate records. This is done using conditions and criteria. Joins enhance this capability because they allow you to consider records from different tables and include them in a common SQL statement.

In the joins we have created so far, we considered all records and let the database engine list them using only the rules of joins built-in the SQL. To make such a list more useful or restrictive, you can pose your own conditions that should be respected to isolate records like a funnel. As done in previous sections about queries, to include a criterion in a SELECT statement, you can create a WHERE clause.

To create a criterion in a query, first select a column to display it in the lower section. Just as reviewed in the previous lessons when creating a query, to specify a criterion, in the Criteria box corresponding to the column, type the condition using any of the operators we reviewed in previous lessons. Here is an example:

After specifying the criteria, the database engine would automatically include it in the SQL statement:

And you can view the result:

Notice that only the Persons records with a Female entry display.

Practical Learning: Using Criteria With Joins

1. Click the Criteria field that corresponds to the Available column and type =True
2. Click the check box of the same column to clear it
    
   
3. Right-click somewhere in the window and click SQL View
    
   
4. To view the result, right-click the title bar of the window and click Datasheet View
5. Save and close the query