The UNION ALL is a SQL operator used in database queries to combine the result sets of two or more SELECT statements. Unlike the UNION operator, which removes duplicate rows from the combined result set, UNION ALL includes all rows, including duplicates. This means that if a row appears in more than one SELECT statement, it will be included in the final result set multiple times.
Syntax
The basic syntax of a UNION ALL statement looks like this:
SELECT column1, column2, ... FROM table1 WHERE condition UNION ALL SELECT column1, column2, ... FROM table2 WHERE condition;
Here’s a breakdown of the syntax:
SELECT column1, column2, …: This is the list of columns you want to retrieve from the tables involved in the query.
FROM table1: Specifies the first table from which you want to retrieve data.
WHERE condition: Optional condition to filter the rows from the first table.
UNION ALL: The operator that combines the result set of the first SELECT statement with the result set of the second SELECT statement. As mentioned earlier, it includes all rows, even if there are duplicates.
SELECT column1, column2, …: Similar to the first SELECT statement, this specifies the columns you want to retrieve from the second table.
FROM table2: Specifies the second table from which you want to retrieve data.
WHERE condition: Optional condition to filter the rows from the second table.
It’s essential to note that the number of columns, their data types, and their order in the SELECT statements must be the same for a UNION ALL operation to be valid.
Example
Here’s a simple example to illustrate the use of UNION ALL:
SELECT employee_id, employee_name FROM employees WHERE department = 'Sales' UNION ALL SELECT employee_id, employee_name FROM employees WHERE department = 'Marketing';
In this example, the query retrieves the employee_id and employee_name columns from the “employees” table for employees in the ‘Sales’ department and the ‘Marketing’ department, and it combines the results using UNION ALL. If an employee works in both departments, they will appear in the final result set multiple times.
It’s important to note that UNION ALL is generally faster than UNION because it doesn’t perform the extra step of eliminating duplicates. However, if you need to eliminate duplicate rows and have a distinct result set, you should use UNION.
In summary, MySQL UNION ALL is a valuable tool for combining results from multiple SELECT queries while retaining duplicate rows in the final result set. This can be useful in scenarios where duplicates are expected or acceptable, and you want a more efficient query execution compared to UNION.