P4.4: Data Modeling in PowerPivot: Relationship JOIN Types

Hello Readers,

In this post we will continue our discussion (from P4.3) on the types of JOINS we can use when creating relationships, especially through SQL queries. As usual, we will be using the AdventureWorks dataset from the Ferrari book. Let us begin.

JOINS

When we join two tables together, we have to determine what we want in the resulting table. Whether we need all of the rows in a specific table or just the matches in both, specific joins enable us to manipulate the merging of two tables in different ways. Subsequently, the left and right tables we choose have positional distinctions when we select a specific type of join method. 

Detailed below are the different types of joins. (Diagrams from stackoverflow.)

INNER JOIN

When two tables are joined using an inner join, only the rows in table A which match the related rows in table B will be included in the resulting table. This is why in the previous post, P4.4, the joining of DimProduct with 606 entries and DimProductSubcategory tables yielded only 397 entries. There were only 397 product rows which had both an EnglishProductCategoryName and EnglishProductSubcategoryName.

LEFT OUTER JOIN

The left join is similar, but includes the all the entries in table A, and records NULL for entries in table A with no corresponding entries in table B.

RIGHT OUTER JOIN

The right outer join is the mirror image of the left outer join. All entries in Table B will be included, and those rows without a corresponding row in Table A will show NULL.

FULL OUTER JOIN

Next is the full outer join where both Tables A and B are preserved, with entries in Table A with none in B, and entries in Table B without entries in A will show as NULL.

LEFT EXCLUDING JOIN

Using left exclusion join we can modify the join to yield only entries in Table A which do not match in the right Table B.

RIGHT EXCLUDING JOIN

Likewise with a right excluding join, the all entries in Table will be included except for those which have corresponding entries relative to entries in Table A.


Here is a helpful link!


Thanks for reading,


Wayne Liu