Use MySQL Union Clause For Reporting

Published on February 20, 2021•Updated on November 21, 2023

Introduction

The MySQL UNION statement combines the results of multiple SELECT statements. The clause is useful when a report must retrieve records from different tables.

When designing a database application, you should consider separating it into distinct sections. Such a design pattern should adhere to the principle of separation of concerns. However, when implementing the reporting part, you might feel the need to combine data from different tables into a single report. This is where the MySQL UNION clause comes into action.

In this guide, you'll create a hypothetical store database for recording payments made by customers and those paid to vendors. While you'll record both the customer's and vendors' activities in different tables, you will use the UNION clause later in the tutorial to generate a useful report that shows the cash flow in your store.

Prerequisites

To follow along with this tutorial, ensure you have the following:

An Vultr Ubuntu 20.04 server.
A sudo user.
A MySQL Database. You can also follow this tutorial on a Vultr One-Click LAMP server which has MySQL pre-installed.

Create a Sample Point of Sale Database

SSH your server and sign in to MySQL as a root user.

$ sudo mysql -u root -p

When prompted, enter the root password of your MySQL server and press Enter to proceed. After the mysql> prompt is displayed, create a sample_pos database.

mysql> CREATE DATABASE sample_pos;

Switch to the sample_pos database.

mysql> USE sample_pos;

Create a Customers Table

Create a customers table. This table stores the customers' unique identifiers (customer_id) alongside their names(first_name and last_name).

mysql> CREATE TABLE customers (
       customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       first_name  VARCHAR(50),
       last_name  VARCHAR(50)
       ) ENGINE = InnoDB;

Add some records to the customers table.

mysql> INSERT INTO customers (first_name, last_name) VALUES ('JANE', 'DOE');
mysql> INSERT INTO customers (first_name, last_name) VALUES ('RICHARD', 'ROE');
mysql> INSERT INTO customers (first_name, last_name) VALUES ('JOE', 'SMITH');

Execute the SELECT command below against the customers table to confirm the entries above.

mysql> SELECT
       customer_id,
       first_name,
       last_name
       FROM customers;

You should see a list of customers as shown below.

+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
|           1 | JANE       | DOE       |
|           2 | RICHARD    | ROE       |
|           3 | JOE        | SMITH     |
+-------------+------------+-----------+
3 rows in set (0.00 sec)

Create Vendors Table

Just like customers, every vendor in your store should be identified with a unique value(vendor_id). However, you'll just require one column for the vendors' names. Create the vendors table by running the command below.

mysql> CREATE TABLE vendors (
       vendor_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       vendor_name  VARCHAR(50)
       ) ENGINE = InnoDB;

Then, insert a few records to the vendors table.

mysql> INSERT INTO vendors (vendor_name) VALUES ('XYZ DIGITAL SUPPLIERS');
mysql> INSERT INTO vendors (vendor_name) VALUES ('ABC 24HRS DISTRIBUTORS');
mysql> INSERT INTO vendors (vendor_name) VALUES ('JKL QUICK SERVICES');

Query the vendors table to confirm the inserted data.

mysql> SELECT
       vendor_id,
       vendor_name
       FROM vendors;

Your vendors' list should be displayed as shown below.

+-----------+------------------------+
| vendor_id | vendor_name            |
+-----------+------------------------+
|         1 | XYZ DIGITAL SUPPLIERS  |
|         2 | ABC 24HRS DISTRIBUTORS |
|         3 | JKL QUICK SERVICES     |
+-----------+------------------------+
3 rows in set (0.00 sec)

Create a Sales Table

Create a sales table. You'll use this table to record the amount paid by each customer(money in) and the date when the transaction is executed. You'll identify each sale by a unique sales_id column. To associate each sale with a customer, you'll use the customer_id column that refers back to the customers table.

Create the sales table.

mysql> CREATE TABLE sales (
       sales_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       customer_id BIGINT,
       sales_date DATE,
       amount DECIMAL(17, 2)
       ) ENGINE = InnoDB;

Enter some sample records to the sales table.

mysql> INSERT INTO sales (customer_id, sales_date, amount) VALUES ('1', '2021-01-23', '8550.60');
mysql> INSERT INTO sales (customer_id, sales_date, amount) VALUES ('2', '2021-01-23', '3940.50');
mysql> INSERT INTO sales (customer_id, sales_date, amount) VALUES ('3', '2021-01-23', '4320.20');
mysql> INSERT INTO sales (customer_id, sales_date, amount) VALUES ('1', '2021-01-23', '1860.20');

Execute the query below to double-check the entries.

mysql> SELECT
       sales_id,
       customer_id,
       sales_date,
       amount
       FROM sales;

You should see the sales list as shown below.

+----------+-------------+------------+---------+
| sales_id | customer_id | sales_date | amount  |
+----------+-------------+------------+---------+
|        1 |           1 | 2021-01-23 | 8550.60 |
|        2 |           2 | 2021-01-23 | 3940.50 |
|        3 |           3 | 2021-01-23 | 4320.20 |
|        4 |           1 | 2021-01-23 | 1860.20 |
+----------+-------------+------------+---------+
4 rows in set (0.00 sec)

Create an Expenses Table

Create the expenses table. You'll use this table to record money paid out to vendors or suppliers (money out). As you can see, you've already implemented the principle of separation of concerns by creating different distinct tables to record activities in your database.

While both sales and expenses in your hypothetical store involve the movement of money, you are considering them as different entities to avoid having a design flaw in your MySQL database.

In the expenses table that you're about to create, you'll use the expense_id as the PRIMARY KEY to identify each expense. You'll then use the column vendor_id that refers to the same column in the vendors table to ensure each expense is associated with a vendor.

You'll use an expense_date column to record the transaction date after an expense occurs, and you'll record the actual money paid to the vendor in the amount column.

Create the expenses table.

mysql> CREATE TABLE expenses (
       expense_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       vendor_id BIGINT,
       expense_date DATE,
       amount DECIMAL(17, 2)
       ) ENGINE = InnoDB;

Insert some records into the expenses table.

mysql> INSERT INTO expenses (vendor_id, expense_date, amount) VALUES ('1', '2021-01-23', '80.20');
mysql> INSERT INTO expenses (vendor_id, expense_date, amount) VALUES ('2', '2021-01-23', '60.40');
mysql> INSERT INTO expenses (vendor_id, expense_date, amount) VALUES ('3', '2021-01-23', '120.10');
mysql> INSERT INTO expenses (vendor_id, expense_date, amount) VALUES ('3', '2021-01-23', '140.70');

Query the expenses table to make sure the records are in place.

mysql> SELECT
       expense_id,
       vendor_id,
       expense_date,
       amount
       FROM expenses;

The list of all the expenses should be displayed as shown below.

+------------+-----------+--------------+--------+
| expense_id | vendor_id | expense_date | amount |
+------------+-----------+--------------+--------+
|          1 |         1 | 2021-01-23   |  80.20 |
|          2 |         2 | 2021-01-23   |  60.40 |
|          3 |         3 | 2021-01-23   | 120.10 |
|          4 |         3 | 2021-01-23   | 140.70 |
+------------+-----------+--------------+--------+
4 rows in set (0.00 sec)

Query the Data Using MySQL Union Clause

You'll now use the MySQL UNION clause to combine the results of the sales and expenses tables to create a nice report showing how money moves in and out of your business.

Use the basic UNION syntax below to combine the results:

mysql> SELECT
       COLUMN 1,
       COLUMN 2,
       COLUMN N
       FROM TABLE 1

       UNION ALL

       SELECT
       COLUMN 1,
       COLUMN 2,
       COLUMN N
       FROM TABLE 2;

Please note, you can combine as many SELECT statements as you want, but you should keep the following things in mind:

The total number of columns in all SELECT statements must be equal
The column names used in the UNION clause must have the same name. In case the column names differ, use a common ALIAS.

To retrieve the records from the sales and expenses table using a UNION clause, execute:

mysql> SELECT
       sales_id AS entry_id,
       'SALES' AS entry_type,
       sales_date AS transaction_date,
       CONCAT(customers.first_name, ' ', customers.last_name) AS paid_by_or_paid_to,
       FORMAT(amount, 2) AS money_in,
       '' AS money_out
       FROM sales
       LEFT JOIN customers
       ON sales.customer_id = customers.customer_id

       UNION ALL

       SELECT
       expense_id AS entry_id,
       'EXPENSE' AS entry_type,
       expense_date AS transaction_date,
       vendors.vendor_name AS paid_by_or_paid_to,
       '' AS money_in,
       FORMAT(amount, 2) AS money_out
       FROM expenses
       LEFT JOIN vendors
       ON expenses.vendor_id = vendors.vendor_id;

You should get a nice report showing the cash flow in your database as shown below:

+----------+------------+------------------+------------------------+----------+-----------+
| entry_id | entry_type | transaction_date | paid_by_or_paid_to     | money_in | money_out |
+----------+------------+------------------+------------------------+----------+-----------+
|        1 | SALES      | 2021-01-23       | JANE DOE               | 8,550.60 |           |
|        2 | SALES      | 2021-01-23       | RICHARD ROE            | 3,940.50 |           |
|        3 | SALES      | 2021-01-23       | JOE SMITH              | 4,320.20 |           |
|        4 | SALES      | 2021-01-23       | JANE DOE               | 1,860.20 |           |
|        1 | EXPENSE    | 2021-01-23       | XYZ DIGITAL SUPPLIERS  |          | 80.20     |
|        2 | EXPENSE    | 2021-01-23       | ABC 24HRS DISTRIBUTORS |          | 60.40     |
|        3 | EXPENSE    | 2021-01-23       | JKL QUICK SERVICES     |          | 120.10    |
|        4 | EXPENSE    | 2021-01-23       | JKL QUICK SERVICES     |          | 140.70    |
+----------+------------+------------------+------------------------+----------+-----------+
8 rows in set (0.00 sec)

The UNION clause syntax explained:

sales_id AS entry_id and expense_id AS entry_id: Unique entries in the sales table are identified by the sales_id column. However, entries in the expenses table use expense_id as the primary key. To have a common column name, you've used the ALIAS entry_id in both SELECT statements.
'SALES' AS entry_type and 'EXPENSE' AS entry_type: You're simply creating a derived column entry_type to identify each record appearing in the combined result set to avoid confusion when the report is displayed.
CONCAT(customers.first_name, ' ', customers.last_name) AS paid_by_or_paid_to: You've used the MySQL CONCAT function to combine the value of the customer's first_name and last_name to make a single name for the customer. You're doing this to match this with the vendor_name that uses a single column. Again, you've used an ALIAS paid_by_or_paid_to to create a common column name.
money_in and money_out columns: In the sales table, cash is moving into your store, while in the expenses table, money is going out. In the sales table, the value of the money_out column should be blank '' and the value of the money_in column should come from the amount column. You've reversed these figures in the expenses part of the query.
LEFT JOIN: You've used MySQL joins in both SELECT statements to retrieve the customer's name from the customers table and the name of the vendor from the vendors table.

The UNION clause is a versatile statement that you can use to query data and generate different reports depending on your application's use-case.

Conclusion

In this guide, you've created a sample database and used the MySQL UNION clause to combine result sets from two different tables to generate a report. You may extend the coding in this guide to suit your needs.

Use MySQL Union Clause For Reporting

Introduction

Prerequisites

Create a Sample Point of Sale Database

Create a Customers Table

Create Vendors Table

Create a Sales Table

Create an Expenses Table

Query the Data Using MySQL Union Clause

Conclusion

Products

Features

Marketplace

Resources

Company