Kbase 21322: A Brief Explanation of Joins - Inner vs Outer, Left vs Right
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
2/15/2007 |
|
Status: Unverified
GOAL:
Which is the proper join for a given case.
GOAL:
Explanation of Joins - Inner vs Outer, Left vs Right
FIX:
In any join, one table is the master table. The master table is the main table that your queries will be preformed against, and that the other table will be joined to. In the example given here, the customer table is the master table.
Basic Inner Join
Here we join the invoice table to the customer table.
Before the join:
---------------- ----------------
| | | |
| Customer | | Invoice |
| (master) | | |
---------------- ----------------
After the Join;
--------------------------------
| |XXX| |
| Customer |XXX| Invoice |
| (master) |XXX| |
--------------------------------
In the diagram the X's represent the overlap between the two tables, Customer and Invoice.
Left Outer Join Right Outer Join
---------------- -----------------
| |XXX| |
| Customer |XXX| Invoice |
| (master) |XXX| |
---------------- -----------------
^
Inner Join
Types of joins
Left Outer Join
A Left Outer Join states that the query should return all records in the Master Table (Customer) and any corresponding records in the right table (Invoice). In our example you would see all customers and their invoices.
Right Outer Join
This query returns only the records from the master table that have corresponding data in the right table. So our example will only display all invoices and the related customers.
Inner Join
This type of query displays all the records that meet both criteria. So in our example it would return only customers that have invoices.
When "?"'s appear in a browse
In some instances after performing a query in a browse you will receive "?"'s as returned values. This is an indication that you have retrieved data that does not correspond to the master table. For example if there was a WHERE clause on the master table that states Where customer.state = "MA" and there is a Right Outer Join on the two tables, you would see "?"'s for invoices that do not correspond to the master table.
You can set the types of joins in the DEFINE QUERY statement in TTY browses or in the Option Radio-Set of the Define Query window in GUI.