Understanding the Different Join Types

In Crystal Reports, the Link tab of the Report Wizard (and Database Expert) provides a visual representation of the relationship between multiple database objects. Defining the appropriate join strategy for any given report should be reflective of the data within the database objects and of how the report needs to read and display that data. Join type settings enable you to control more precisely the query results based on your unique requirements. The following is a list of the most common types of joins and their associated descriptions:

• Inner: The resultset includes all the records in which the linked field value in both tables is an exact match. The Inner join is the standard type of join for most reports, and it is also commonly known as the Equal join.
• Left Outer: The resultset includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the secondary (lookup) table. For example, if you would like your report to display all customers and the orders they have each placedincluding the customers who have not placed any orders at allyou can use a Left Outer join between the Customer and Orders tables. As a result, you would see a row for every customer who has not placed any orders.
• Not Equal: The resultset includes all records in which the linked field value in the primary table is not equal to the linked field value in the secondary (lookup) table. For example, if you needed to report on all orders that were not shipped on the same date that they were ordered, you could use the Not Equal join type to join the OrderDate field in the Orders table with the ShipDate field in the OrderDetails table.
• Full Outer: The resultset includes all records in both of the linked tablesall records in which the linked field value in both tables is an exact match, in addition to a row for every record in the primary (left) table for which the linked field value has no match in the secondary (lookup) table, and a row for every record in the secondary (lookup, or right) table for which the linked field value has no match in the primary table. The Full Outer join is a bidirectional outer join, which essentially combines the characteristics of both the Left Outer and Right Outer joins into a single join type.

NOTE

The capability to enforce links created in a report is new to version 10. Enforcing a link between two tables ensures that this link will be used in the report's respective SQL, regardless of whether fields are required from either or both of the involved tables. The default setting is Un-enforced links, meaning that the link will only be used if the report's respective SELECT statement requires it. Access to the different enforcing options is provided by right-clicking on a link and selecting the Link Options menu item.

After a report is created, select Database Expert from the Database menu to return to the Data Explorer. Here tables and SQL Commands can be added, removed, and renamed just as they could from the Data Explorer in the report creation process.