Setting 'Inner' and 'Outer' Joins
The join properties, accessed from the relationships dialog allows table joins of the following types;
Option 1 - 'Only include rows where the joined fields from both tables are equal'
This is called an inner join. A query that runs using this option shows only the records from the joined tables or queries that have the same values in the joined fields. When values are the same, the query combines the matching records and displays them as one record in the query’s results. An example of an inner join between a patient and an episode of care table would only show those records where the patient ID appeared in both tables, i.e. it would not show patients who had not had any previous episodes of care.
Option 2 - 'Include ALL records from ‘Patient’ and only those records from ‘Episodes’ where the joined fields are equal'
When you change the default join (above) to either this or the next option, you are establishing an outer join. Here each matching record from the two tables is combined into one record in the query’s results. However when a record from the table that is contributing all of it’s records cannot be matched with a record from the other table the record still appears in the queries results, but with empty cells where there was no matching record from the other table.
Option 3 - 'Include ALL records from ‘Episodes’ and only those records from ‘Patient’ where the joined fields are equal'
As with option two, this outer join will highlight in which direction the table contributing only matching records is set. This is denoted by an arrow which points to the table contributing the matching records, i.e. ‘One’ patient can have – ‘Many’ episodes of care.
It is most common that join properties, certainly when running and producing queries will be set to join type 2 or 3. The key consideration is whether you want to include all records from one table or the other as this has a heavy bearing on whether the query will run a valid data extract or simply output thousands of meaningless or useless records.
It is definitely worth testing your join properties within queries and understanding how an inner and outer join works prior to launching full scale query’s that interrogate hundreds of thousands of records.