How to use Joins in Sourcetable
Have you ever wanted to combine data only to find that you can’t link the data together? If the full data set isn’t available in one table you can use a join statement to combine the two tables.
Say you are looking for customers who ordered yellow umbrellas but the data table for customers doesn’t have the specific product names. However, the orders data table does contain the specific product information you need but not the customer names or emails. In these circumstances you will have to use a join to start your analysis and combine the data. Sourcetable uses an easy interface with recommendations for joins between tables to ensure success, and can even join data tables from different sources and integrations.
What are joins?
A join is also a statement that connects two or more tables together with a common field and is often used with relational databases like MySQL. They are a way to connect data tables together and analyze the data in one table. The common field is usually a unique identifier like an id or an email address. Sourcetable uses a GUI or user-friendly interface to recommend columns with matching identifiers(ids). Simply click the gear next to the table name. Next click “Create Join” to open the user interface for the joins recommendation window.
Once you click “Create Join” you will see a window with your current data table on the left side and the data table you want to connect to on the right side. Select the id you want to join on and then select the recommended column on the right and click "Create Join."
You will then be able to select the columns that you would like or aggregate the data from any column from both data tables.
Sourcetable has a recommendation engine that searches the data in the column you have selected to join from and finds matching data in the data table you have selected to join on. Once you select the data table and column to join from along with the data table to join to the recommendation engine will recommend a column that is a valid join. As a user you can chose to join on a non-recommended column but Sourcetable cannot be sure that the join will have usable results. Using Sourcetable recommended joins guarantees that the join will be successful and usable.
Why do I need to use joins?
Joins are used when there are two data tables that you want to analyze with a common field. For example, if you want to get the order ids for all of your customer purchases then using a join would be great. Once you have the data tables joined together you can then select the columns you want to access or create aggregates based on data from both data tables
Inner Join
An Inner Join will have all the columns(records) with matching values in both tables. There will be no duplication or new null values since the new data table will only contain values that are in both tables.
Left Join
A Left Join will have all of the columns(records) from the first data table( or left data table) and the matching columns from the second data table(or right table).
Right Join
A Right Join will have all of the columns (records) from the second data table (or right data table) and the matching columns from the first data table (or left table). The result will have all of the data from the second data table.
Outer Join (Full Outer Join)
An Outer Join will have all of the columns (records) from both tables when there is a match in the first or second data table. This join will most likely have a lot of data that is null since it will have a null record for anything that is not in one of the tables.
Conclusion
If you need to connect two or more tables together to analyze your data then use a join. You can connect however many data tables you need as long as the data tables have a common unique field, even combining your data from multiple sources like Shopify, Klaviyo and your databases. Sourcetable allows you to use a handy user interface with a recommendation engine guaranteeing that the join will be successful and usable. This allows you to have unlimited access to what you can analyze across sources and takes the guesswork out of joining tables together.
Ready to try Sourcetable?