In MariaDB, the Union All operator is a useful tool for combining results from multiple SELECT queries into a single result set. Unlike the Union operator, Union All retains all rows, including duplicates, making it ideal for merging large datasets efficiently. Whether we are consolidating information from different tables or simplifying complex queries, mastering the Union All operator can speed up our data manipulation tasks in MariaDB. In this article, we’ll explore everything about the Union All Operator with syntax and their examples too.
What does UNION All do in MariaDB?
The UNION ALL operator is used to combine the results of two or more SELECT statements into a single result set. The UNION ALL Operator is different from the UNION Operator where the UNION ALL Operator does not remove duplicate rows from the result set. This means that all rows from each SELECT statement are included in the final result even if duplicate records are present.
let’s Setting Up an Environment for Union All Operator
To understand the UNION ALL Operator easily we need a table on which we will perform various operations and queries so here we will create a table called contains which contains employee_id, first_name, last_name, and department as columns.
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50)
);
INSERT INTO employees (first_name, last_name, department) VALUES
('John', 'Doe', 'Finance'),
('Jane', 'Smith', 'Marketing'),
('Alice', 'Johnson', 'Engineering'),
('Bob', 'Brown', 'Marketing'),
('Charlie', 'Davis', 'Engineering');
Output:
Explanation: The table has been created.
Examples of Union All Operator in MariaDB
Example 1: Basic Usage of Union All
Suppose we want to retrieve the names of employees from the Engineering and Sales departments. We can achieve this using the Union All operator as follows:
SELECT first_name FROM employees WHERE department = 'Engineering'
UNION ALL
SELECT first_name FROM employees WHERE department = 'Sales';
Output:
Explanation: This query will return all employee names from both the Engineering and Sales departments.
Example 2: Union All with Additional Columns
Union All can also be used when the SELECT statements involved have different columns. Let’s say we want to retrieve both the name and department of employees from the Engineering department with just the name of employees from the Sales department:
SELECT first_name, department FROM employees WHERE department = 'Engineering'
UNION ALL
SELECT first_name, NULL AS department FROM employees WHERE department = 'Sales';
Output:
Explanation: In this query, we are selecting the name and department for employees in Engineering and only the name for employees in Sales. We use NULL AS department to ensure both SELECT statements have the same number of columns.
Example 3: Union All with Ordering
We can also apply ordering to the combined result set. Suppose we want to retrieve the names of employees from both departments and sorted them by their name alphabetically:
(SELECT first_name FROM employees WHERE department = 'Engineering')
UNION ALL
(SELECT first_name FROM employees WHERE department = 'Sales')
ORDER BY first_name;
Output:
Explanation: This query combines the names of employees from both departments and orders them alphabetically by name.
Example 4: Union All with Aggregate Functions
Union All can be used with aggregate functions to perform calculations on the combined result set. Suppose we want to calculate the total number of employees in both departments:
(SELECT COUNT(*) AS total_employees FROM employees WHERE department = 'Engineering')
UNION ALL
(SELECT COUNT(*) FROM employees WHERE department = 'Sales');
Output:
Explanation: In this output, we have seen that there are 2 employees in the Engineering department and 1 employee in the Sales department.
Conclusion
The Union All operator in MariaDB is a useful operator for combining the results of multiple SELECT statements. It allows for the consolidation of data from different sources or conditions into a single result set and making it a useful tool in database querying and analysis. By understanding its usage and syntax, you can effectively leverage Union All to meet various data retrieval needs in your MariaDB database.