There are three common ways you can join any two or more tables together we’ll talk about first: Outer Join, Inner Join, and Left Join. Using the example User and Event tables above, let’s look at some examples of joins…
Let’s say you want to have a table that contains all your user and event table data together.
You would use an Outer Join to join the tables together. An outer join combines the columns from all tables on one or more common dimension when possible, and includes all data from all tables.
For a more detailed look at the Outer Join click here.
What if you want to have a table that contains only users that have done an action?
You would use an Inner Join to join the tables together. An inner join combines the columns on a common dimension (the first N columns) when possible, and only includes data for the columns that share the same values in the common N column(s). In the example, the User ID would be the common dimension used for the inner join.
For a more detailed look at the Inner Join click here.
Now, what if you want to have a table that contains all the users’ data and only actions that those users have done? Actions performed by other users not in the users table should not be included?
You would use a Left Join to join the tables together. A left join combines the columns on a common dimension (the first N columns) when possible, returning all rows from the first table with the matching rows in the consecutive tables. The result is NULL in the consecutive tables when there is no match. In this case, we would make the User Table the first (left table) to use for the left join.
For a more Detailed look at the Left Join click here.
In addition to these common join types, there are some methods which will result in additional rows in your output table as well as more columns. Two of these join types are called Union and Cross Join. These join types probably wouldn’t be as appropriate for our example tables above, but for the sake of this article we can still use them to see how these joins function. A Union Join will stack tables on top of each other resulting in new rows.
For a more detailed look at the Union Join click here.
A good use case for this would be if you’re looking to combine two tables by appending them rather than joining them. A Cross Join would result in a table with all possible combinations of your tables’ rows together. This can result in enormous tables and should be used with caution.
For a more detailed look at the Cross Join click here.
Cross Joins will likely only be used when your tables contain single values that you want to join together without a common dimension.