Combine data from two or more sources or datasets - Union

In this article we will walk you through the techniques needed to combine data from two or more datasets. We will explicitly look at the UNION available in SQL to achieve the purpose.

Problem

You have data in two or more sources/spreadsheets, and the analysis you're attempting requires data from the two sources to be combined in some way.

Before going through this article we strongly recommend that you go through the following articles

1. Explaining SELECT Statement

2. Topics on Data Collection

A sneak peeks at the  columns, and the data of these datasets are referred to in the forthcoming section.

Targets_Baseline

Results_Survey

Problem statement for this use case :   We need to compare the Total Number of people trained and got jobs stored in dataset "Result_survey" against the Targets which resides in different dataset "Target_Baseline"?

Solution

To accomplish this, we need to combine data that reside in different datasets into one dataset and then compare the column ( These datasets can be from different sources: surveys, excel file or google sheets too).

 The solution can be achieved using two steps, as shown below :

The result of the output of Step 1 is as shown below : ( After combining both the datasets)

Purpose

This is precisely what the UNION ALL operator does. It provides instructions to combine results of both the datasets/tables ( Targets_Baseline and Results_Survey) and get desired columns into one dataset. In simple terms, it combines the two or more datasets into one dataset. Such datasets are called Virtual datasets.  Diagrammatically, it can be represented as follows :

Step 1 : Fetch specific column information from first dataset i.e.Targets_Baseline

The SELECT statement should look like as shown below

 SELECT "Project","Period",  "Number_people_trained",
"Number_people_got_jobs", "Rep_Type"
FROM "Targets_Baseline" 

Note : Include column names "Project", "Period", "Number_people_trained", "Number_people_got_jobs", "Rep_Type"

Step 2 : Fetch specific column  information from second dataset i.e.Results_Survey 

The SELECT statement should look like as shown below

SELECT "Project Name","Reporting Date" , "Number of people trained",
 "Number of people placed", "Rep_Type"
FROM "Results_survey"

Note : Include column names "Project Name", "Reporting Date", "Number of people trained", "Number of people placed", "Rep_Type"

The Final query to combine both the datasets using UNION ALL should be as follows :

-- Query from Step1

SELECT "Project","Period",  "Number_people_trained", 
"Number_people_got_jobs", "Rep_Type"
FROM "Targets_Baseline" 

UNION ALL

-- Query from Step2

SELECT "Project Name","Reporting Date","Number of people trained", 
"Number of people placed", "Rep_Type"
FROM "Results_survey"

NOTE : In scenarios similar to above, Just replace column name1, column name2, column name3,.... with column names  and with table names according to your datasets keeping an eye on the syntax . At the same time be mindful of points to remember stated below related to number of columns, datatype and order of column.

---

SO LETS GET STARTED to combine the datasets as one dataset Using UNION :

To accomplish this, follow STEPS A to E

STEP A:

Go to SQL Lab as shown below : ( We will be using the two datasets described above that are highlighted. You can see all the datasets from Datasets menu )

Step B:   Navigate to SQL Editor from the Menu bar, Select the appropriate data and the schema as shown below.  This information can be obtained and is highlighted in Red as shown above.

Step C:  Now copy and paste the following query in the Right side space as shown below :

Step D : Click on the RUN button and execute the query and get the desired output. The results circled below is the combined results from both the tables. 

STEP E: To save these results into a new dataset, click on CREATE CHART. It will open up a dialog box, as shown below. Give it an appropriate name. Let's name it "Combined_datasets"

STEP F: Finally, "Combined_datasets" is created, shown below, a virtual dataset. This dataset has a combination of data from both the above-mentioned datasets.

Congratulations! Using the UNION ALL operator, you learnt how to join two tables to generate a virtual dataset.