Example of Union Operator in MariaDB

To understand the Union Operator in detail we need some tables on which we will perform various operations related to the Union Operator. Here we have created a two tables called orders and returns table.

orders table looks like:

returns table looks like:

Example 1: Combining Orders and Returns

Let’s Combine the order information (order_id, customer_id, product_id, order_date) from the “orders” table with the return information (return_id, customer_id, product_id, return_date) from the “returns” table, by removing the duplicates.

Query:

SELECT order_id, customer_id, product_id, order_date FROM orders
UNION
SELECT return_id, customer_id, product_id, return_date FROM returns;

Output:

Explanation: In this select query that returns the combined data into a single result set. It gives us info on both successful orders and returns. Each row will show details like the orde­r_id/return_id, customer_id, product_id, and the related date­s.

Example 2: Combining Orders and Returns for a Specific Customer

Let’s Retrieve all records from the “orders” table and the “returns” table where the customer ID is 1, combining the results while removing duplicates.

Query:

SELECT * FROM orders WHERE customer_id = 1
UNION
SELECT * FROM returns WHERE customer_id = 1;

Output:

Explanation: This query retrieves data for customer_id 1. It fetches data from two tables: orde­rs and returns. It shows all orders and returns associated to that customer.

Example 3: Combining Orders and Returns for a Specific Product

Let’s Retrieve all records from the “orders” table and the “returns” table where the product ID is 104, combining the results while removing duplicates.

Query:

SELECT * FROM orders WHERE product_id = 104
UNION
SELECT * FROM returns WHERE product_id = 104;

Output:

Explanation: This query fetches data about product_id 101. It ge­ts information from orders and returns tables. It shows all orders and re­turnes for this specific product.

Example 4: Combining Orders and Returns Sorted by Date

Let’s Combine all records from the “orders” table and the “returns” table, removing duplicates, and then order the results based on the “order_date” column.

Query:

(SELECT * FROM orders)
UNION
(SELECT * FROM returns)
ORDER BY order_date;

Output:

Explanation: This query joins data about orders and re­turns. The results get sorted by the order date so we se­e the orders and re­turns in time order.

MariaDB is an Open-Source Database system and MariaDB offers similar security features to MySQL, including access control, user authentication, and encryption. UNION operator is a fundamental part of MariaDB, a well-known database syste­m. The UNION operator merge­s results from different SELECT que­ries. In this article, We will understand the Union Operator in MariaDB along with the­ syntax, its practical examples, the difference between the join and Union, and so on.