The Basics of Joining Tables

By George Bennett

Whether you are using tableau, SQL, or excel, or google sheets. It is a good Idea to know how to make joins.

Joins are used when you have two tables containing information that can be linked together. Many times in databases there will be identifier columns which act similar to an index. These are created for the sole purpose of connecting tables together if need be. Lets take an example of a business. They keep one table full of customer information like address, phone-number, and money spent. They put a customer ID in that table that is unique to that customer. There are 5000 recorded customers, so that table has 5000 rows. Then they have another table full of transactions. It has transaction time, amount, and location, along with a customer ID. Lets say you had an accident and need to get the phone numbers of all customers that shopped in your store today. Now the goal is to have all the customers at your location at this date and to have their phone number. The key is to join on the customer ID column.

There are three main types of joins. Inner, outer, and left/right. An inner join will create a row for every match of values in either table. The left will do the same except if there are rows that can’t be matched, it will leave empty cells to ensure all the rows from the first table you input still exist. A right join does the same except it works with the second table you input. An outer join will make sure every row from both the first and second table will be represented, possibly leaving a lot of empty cells and inconsistency whether any column is free of empty cells. In this example we would use an inner join. This is the default. After that we would use a “Where” clause to filter out the correct time and location.