Understanding SQL JOINs

Administrators can setup any required default join relationships via the Administration interface. When this is done, simply selecting the desired datasets from the Data Source tab of the Report Designer will enable them to form the specified join automatically. However, you may at times need to modify these defaults or create new joins depending on your reporting needs so gaining an understanding of how they work can be beneficial.

A JOIN clause is used to combine rows from two or more datasets, based on a related column between them.

The extent of the overlap, if any, is determined by how many records in Table A match the records in Table B. Depending on what subset of data we would like to select from the two tables, the four join types can be visualised by highlighting the corresponding sections of a Venn diagram.

Inner Join

Select all records from Table A and Table B, where the join condition is met.

Example scenario: Only show Risks that have Risk Actions.

In this case, you would perform an Inner Join between the Risk Details and Risk Action Details datasets using the RISKID available in both as the connecting field.

 Left Join

Select all records from Table A, along with records from Table B for which the join condition is met (if at all).

Example scenario: Show all Risks and for Risks that have Risk Actions, show those too.

In this case, you would perform a LEFT Join between the Risk Details and Risk Action Details datasets using the RISKID available in both as the connecting field.

Right Join

Select all records from Table B, along with records from Table A for which the join condition is met (if at all).

If we convert the above mentioned Left Join to a Right Join it would produce a list of all Risks that have Risk Actions. It would not include Risks with no Risk Actions.

Full Join

Select all records from Table A and Table B, regardless of whether the join condition is met or not.

Example scenario: Show all Actions and all Projects regardless of their linkages with each other.

In this case, you would perform a FULL Join between the Action Details and Project Details datasets where all actions and all projects will be listed. If an Action doesn’t have a linked project, the Project name for that Action row will show as null. If a Project doesn’t have a linked Action the Action name for that Project row will display as null.