First, we need to create our sample tables and then populate them with data.

The scenario of this exercise is that our employees generate sales both offline in the stores and online via various online marketing mechanisms.

-- Sales by employee in stores
create table store_sales
(   
	EmployeeName varchar(20) NOT NULL,
	Sales double NOT NULL
);

-- Sales by employee on the internet
create table online_sales
(   
	EmployeeName varchar(20) NOT NULL,
	Sales double NOT NULL
);

-- Add sales data for store sales
insert into store_sales values('Joe',516.30),
('Gary',1136.32),
('Jill',589.54),
('Ted',986.54),
('Bob',656.66),
('Mick',887.36),
('Pam',1258.13);

-- Add sales data for online sales
insert into online_sales values('Sam',784.35),
('Gary',1589.22),
('Jeff',223.12),
('Martin',1456.99),
('Mick',2435.78),
('Jay',565.4),
('Jill',600.23);

We are given a task to generate a report with 3 columns where it lists employee names, their total sales by store, and totals sales online. Because some employees only do stores or online sales and some of them do both, our report needs to aggregate all the sales for them. If an employee does only store sales, his/her online sales will display 0, and vice versa.

The inner query below uses UNION to combine store sales with online sales, so it vertically appends the two types of sales. Note that for records from store sales table, an online sales column is created with value 0 assigned. For records from online sales table, a store sales column is created with value 0 assigned. The outer query aggregates the two types of sales so for those employees who work for both store and online, their sales are shown side-by-side with the actual total sales listed.

select EmployeeName, sum(StoreSales) as StoreSalesTotal, sum(OnlineSales) as OnlineSalesTotal
from
(
	select EmployeeName, Sales as StoreSales, 0 as OnlineSales
	from store_sales
	UNION
	select EmployeeName, 0 as "StoreSales", Sales as "OnlineSales"
	from online_sales
) as x
group by EmployeeName
order by EmployeeName;

11 records returned:

The pseudo code for MySQL FULL JOIN

If we're able to use FULL JOIN in MySQL, the code would be looking like below if we want it to return an identical result as above.

select case when a.EmployeeName=b.EmployeeName then a.EmployeeName
    when a.EmployeeName is null then b.EmployeeName
    when b.EmployeeName is null then a.EmployeeName
    end as EmployeeName,
    case when sum(a.Sales) is null then 0 else sum(a.Sales) 
    end as StoreSalesTotal, 
    case when sum(b.Sales) is null then 0 else sum(b.Sales) 
    end as OnlineSalesTotal
from store_sales as a
FULL JOIN store_sales as b on a.EmployeeName=b.EmployeeName
group by case when a.EmployeeName=b.EmployeeName then a.EmployeeName
    when a.EmployeeName is null then b.EmployeeName
    when b.EmployeeName is null then a.EmployeeName
    end
order by case when a.EmployeeName=b.EmployeeName then a.EmployeeName
    when a.EmployeeName is null then b.EmployeeName
    when b.EmployeeName is null then a.EmployeeName
    end;

Happy Coding!

Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Another Example for Outer Joins (three tables)

4. Using Self Joins to Combine Data from the Same Table

5. SQL Set Operators - a Visual Guide to UNION, UNION ALL, MIMUS/EXCEPT, INTERSECT

6. Using UNION to Append Result Sets

7. Create working tables to demonstrate how to mimic set operators MINUS, EXCEPT, INTERSECT in MySQL

8. How to do MINUS/EXCEPT and INTERSECT in MySQL

9. How to Simulate Full Join in MySQL - Part 1: return both matched and unmatched rows

10. How to Simulate Full Join in MySQL - Part 2: return unmatched rows only

11. Use Cross Join to Combine Data in MySQL

12. Differences Between Join and Union

Back to Tutorial Index Page