Joins between 2 or more tables
With SQL, one can combine selective or all columns from multiple datasets and create a new combined report using a single command - JOIN.
It requires at least 1 column content to be common among the data sets (Just like using VLOOKUP function in Excel).
​
Inner Join - To display rows from table 1 with common contents only.
Left Outer join - To display all columns from Table1 and only common columns from Table2.
Right Outer Join - To display all columns from Table2 and common column from Table1.
Let’s create 2 more sample datasets in MySQL. The Common columns will be Emp_ID.
The illustrations on this page show how can we create a combined dataset from multiple files, stored in MySQL.
Collection Data set containing customer data with Op.Balance, Current due, paid Amount & Net Outstanding amount to be collected by assigned employee IDs.
Sales Data set containing customer data with Sale Amount and sales lead employee IDs.
01
Identify Customer wise Sale status of all employees.
We can see the data have Sales Employees only but the original dataset 'Emp' have sales as well as collection employees.
This is because the command Inner Join combined all columns from both the table (Collection & Emp) only for common Emp_IDs.
select * from Emp INNER JOIN Sales on Emp.Emp_ID = Sales.Emp_ID;
​
(Display all columns from Table 1 'Emp' dataset with Emp_ID matching the Table 2 'Collection' dataset.)
02 (a)
Identify Customer wise Collection status of all employees.
Observe that here we have all the employees as per 'Emp' dataset listed (Sales as well as Collection) in reference to Emp_ID in 'Collection' dataset.
​
For Employees of Sales Department, the Columns from 'Collection' dataset have NULL values too.
This is because the command Left Outer Join displayed all the rows and columns from Table 1 and Table 2. But Table 2 'Collection' dataset have Emp_IDs of Collection Department only.
​
select * from Emp LEFT OUTER JOIN Collection on Emp.Emp_ID = Collection.Emp_ID;
​
(Display all columns from Table 1 'Emp' (on the left of the command) and all Common Emp_ID tables from 'Collection' dataset.)
02 (b)
From the above illustration, let's query MySQL to display data only for Collection Department
Notice there are 2 Collection employees with NULL Values too. This is because these employee IDs are not existing in the Collection data.
Either these 2 employees have not done any customer collection yet or there may be rectifications required in the base data.
This is how MySQL query not only helps store massive data but also help combine information from multiple datasets and provide output for management to work on.
​
select * from Emp LEFT OUTER JOIN Collection on Emp.Emp_ID = Collection.Emp_ID WHERE Emp.Department = 'Collection';
​
(Here we used the same command as 2(a) and added a condition that Table 1 'Emp' should have Department= Collection only.)
03
Display the list of Top 5 Employees with more than Average Collected Amount and their Incentives.
We can see that employees with IDs 7, 8 & 2 have collected more than the average collection amount.
​
select emp.Emp_ID, emp.Emp_Name, emp.commission, emp.Department, CUST_ID, CUST_NAME, CUST_CITY, CUST_COUNTRY, PAYMENT_AMT from collection LEFT OUTER JOIN Emp on emp.Emp_ID = Collection.Emp_ID where PAYMENT_AMT > (SELECT AVG(PAYMENT_AMT) from Collection) order by PAYMENT_AMT desc limit 5;
(In the above illustration, we have queried MySQL to display data only for selective columns which are marked in red above, from Data 'Emp' and 'Collection', for Top 5 rows with more than average collection amount. Also, arranged in descending order of Payment_Amt)
04
Display Employee wise Sales data
Here we used the same query as 2(b) above but replaced Table 2 with 'Sales'.
Since Sales data only have Emp_ID of Sales Department, the result shows NULL values.
​
select * from Emp LEFT OUTER JOIN Sales on Emp.Emp_ID = Sales.Emp_ID WHERE Emp.Department = 'Collection';
(Here we queried all data from Table 1 'Emp' relevant to Table 2 'Sales' dataset Emp_ID and Department = Collection
05
Let's see if we can alter query 4 above to get employee wise Sales data
We now have all the Employee details from Table 1 'Emp' consolidated customer wise in Table 2 'Sales' data set.
Notice the MySQL query here is very similar to 2(a) above, but results are different.
​
That's because we use Right Outer Join on 'Sales' dataset which obviously has Emp_ids of only Sales employees.
​
We can use the same set of commands with slight changes and it is that simple to get the dataset sorted in MySQl as per our requriements.
​
select * from Emp RIGHT OUTER JOIN Sales on Emp.Emp_ID = Sales.Emp_ID;
6
Display all Employee details with Collection and Sales achivement
The below table reflects the comparative view of all employees (from 'Emp' dataset) targets and achivements:
- Total Sales Amount achieved by each employee from 'Sales' dataset,
- Total Collection Amount (Payment_Amt) achieved by each employee and
- Total outstanding amount, expected to be collected by the respective employee, from 'Collection' data set.
​
​
SELECT a.Emp_ID, Emp_Name, commission, Department, b.SALE_AMT, c.PAYMENT_AMT, c.OUTSTANDING_AMT FROM Emp a LEFT OUTER JOIN Sales b ON b.Emp_ID = a.Emp_ID LEFT OUTER JOIN Collection c ON c.Emp_ID = a.Emp_ID GROUP By a.Emp_ID order by Department;
​
Here we queried MySQL to JOIN 3 datasets - Table 1 as 'a.Emp' Table 2 as 'b.Collection' and Table 3 as 'c.Sales'
and provide output for selective columns only.
We can either export this data in csv form or upload it directly to MySQL-supported Data Visualisation tool, and prepare visualisations on the same for employee KPI reports and Dashboards.
