Visual SQL

Layers and Joins

Layers & Joins: Combining and Comparing Data from Different Tables and Databases

Previously, when discussing Data Sources and Schemas we mentioned the notion of Joins and Join paths. In this tutorial we will go into more detail on what that means and how Visual SQL treats joins. Most importantly we will discuss how you can use Visual SQL to Join and compare disparate data sources to complete complex data investigations.

The term Join describes how two different tables (or subsets of tables) are merged into one table for the end goal of manipulation and/or visualization of complex data sets. These two tables can come from two different databases or they can be from two different tables in the same database. The thing to keep in mind is that we are always after creating one single table of data in order to create a chart and that Joins simply merge two or more tables from anywhere into one table that can be manipulated and/or visualized.

There are also multiple different types of joins that can be used to merge your different tables.

Outer Join

Combines the columns from all layers on one or more common dimension when possible, and includes all data from both layers.

outer join icon
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).

inner join icon
Left Join

Combines the columns on a common dimension (the first N columns) when possible, returning all rows from the first layer with the matching rows in the second layer. The result is NULL in the second layer when there is no match.

left join icon
Union

A Union merge will stack the layer results on top of each other without grouping or combining the data. Unions can be used to generate lists of data to be printed or viewed in table format. To remove duplicate rows, check the Distinct checkbox.

union join icon
Cross Join

The result of the Cross Join will be a table with all possible combinations of your layers together. This can result in enormous tables and should be used with caution. Cross Joins will likely only be used when your layers are returning single values.

cross join icon

So how can you use this information and Visual SQL to accomplish your goal of comparing two data sets against each other in one table. It’s actually very easy. In an example where you want to compare two different databases that do not already have a Join path set up in the Schema that we discussed earlier, you can use the Layering function of Visual SQL in Chartio to join (or merge) these two tables into one for further manipulation or visualization.

In our example fictitious company we have been analyzing MRR over time. Let’s take a look at how our user sign up rates compare with the web traffic we are experiencing.

These two metrics come from two totally different data sources that can not be joined in the schema the way we discussed before. Joining two disparate databases is the truly powerful aspect of Layering in Visual SQL.

We will be comparing a Google Analytics Database, that tracks website activity, with our Company’s internal database that holds our company’s customer and performance information.

First we need to get the user sign up rate metric, we will call it USR, from the Digisign internal database from the past week.

alt_text

Now to add in the website traffic over the same 1 week time period we need to add a layer. In Visual SQL clicking the “+” or ‘plus’ symbol in the Editor will add in a second layer, that we can edit in the Visual SQL Editor the same way we did the USR layer.

alt_text

These two Visual SQL queries have now create two different tables that have been merged into one table and visualized as a line graph. You might notice that when creating the second layer the Merge step was created for us and the join type was defaulted to an Outer Join and the tables were joined on the first column, in our case it was the Month column. Clicking into the Merge Layers step you will be able to select from a dropdown of the join types we outlined above and select the appropriate join type for your use case.

In the next tutorial, we will explain how to transform data in multiple steps using an intuitive, visual interface.. If you would like to use Visual SQL, you can try it out with a Chartio free trial.