Join Types

The default join type is Inner, but you can also create Left or Right (Outer) joins.

Open

Selecting a ‘left’ join for example would change the SQL generated to.

Open

‘Left’ is the table to the left of ‘JOIN’ immediately after ‘FROM’.

‘Right’ is the table specified in the JOIN statement, i.e. to the right.

In both a left and right join it will always return the rows where there is a match between the two tables, the left and right logic simply determines whether it will also include items where there is no such match.

A left join would return all rows from the left table, even if there were no matches on the right table, in this example all ukpostcodes even if they didn’t have a CUS_POSTCODE.

A right join would return all rows from the right table, even if there were no matches on the left table, in this example all CUS_POSTCODE, even if they didn’t have any ukpostcodes allocated to them.

The default option of inner join will only return rows that match on both tables. For example, if you had a table ‘AccountManagers’ and a table ‘Sales’ and you wanted to show sales per Account Manager in the dashboard including Account Managers with no Sales then you would need to use an outer join, all Account Managers would show and Sales figures would also be shown where there was a match. Whether this was a left or right join would depend on the table order as described above.

The default inner join would only return Account Managers who had Sales and their Sales figures.

Bear in mind that Outer joins can be more ‘expensive’ (database performance wise), you should only use them where there is a genuine need.

Which Joins will it use?

The dashboard will resolve the minimum joins it needs to use to connect the objects you have placed in a chart, bearing in mind the Table Priority. For example, if you had the following Meta Data.

Open

A chart using an object from ‘ukpostcodes’ and an object from ‘nr_customers’ would only resolve the join between those two tables.

If on the other hand your chart had an object from ‘ukpostcode’ and an object from ‘nr_sales’ then it would resolve the join from ‘ukpostcode’ to ‘nr_customers’ and the join from ‘nr_customers’ to ‘nr_sales’.

The same would apply if your chart had objects from ‘ukpostcode’ and ‘nr_customers’ but you also had ‘nr_sales’ as a category object; when the chart SQL was created initially it wouldn’t join to ‘nr_sales’ , but once a category object on the ‘nr_sales’ table had been selected that table would also be joined on the underlying chart SQL.