A UNION in MySQL is used to combine the result sets of two or more SELECT statements into a single result set. This can be useful when you need to retrieve data from multiple tables or apply different conditions to the same table and want to display the results as a single unified dataset. In this guide, we’ll explore how to use UNION in MySQL with examples.

Syntax of UNION

The basic syntax of a UNION statement in MySQL is as follows:

SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition2;

• SELECT: Specifies the columns you want to retrieve.
• FROM: Indicates the table(s) from which you want to retrieve data.
• WHERE: Defines any conditions that must be met for the rows to be included in the result set.
• UNION: Combines the result sets of the preceding SELECT statements into a single result set.

Example 1: Simple UNION

Let’s say you have two tables, employees and contractors, and you want to retrieve a list of all people who work for your company, whether they are employees or contractors. You can use a simple UNION to achieve this:

SELECT employee_id, first_name, last_name
FROM employees
UNION
SELECT contractor_id, first_name, last_name
FROM contractors;

In this example, we’re selecting the employee_id, first_name, and last_name columns from both the employees and contractors tables. The UNION operator combines the results into a single list of all workers.

Example 2: Sorting UNION Results

You can sort the combined result set by adding an ORDER BY clause to the end of the UNION statement. For instance, to sort the previous example alphabetically by last name:

(SELECT employee_id, first_name, last_name
FROM employees)
UNION
(SELECT contractor_id, first_name, last_name
FROM contractors)
ORDER BY last_name, first_name;

Example 3: Using UNION with Conditions

You can also apply conditions to each SELECT statement individually. Suppose you want to retrieve a list of employees and contractors who earn more than $50,000 per year:

(SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > 50000)
UNION
(SELECT contractor_id, first_name, last_name
FROM contractors
WHERE hourly_rate * 40 * 52 > 50000);

In this example, we apply the condition salary > 50000 for employees and hourly_rate * 40 * 52 > 50000 for contractors.

Example 4: UNION vs. UNION ALL

By default, UNION eliminates duplicate rows from the combined result set. If you want to include duplicate rows, you can use UNION ALL:

(SELECT department_id
FROM employees)
UNION ALL
(SELECT department_id
FROM contractors);

In this case, UNION ALL will include all rows, including duplicates, whereas UNION would remove duplicates.

Conclusion

In MySQL, the UNION statement is a powerful tool for combining data from multiple sources and creating unified result sets. Whether you need to retrieve data from different tables, apply various conditions, or sort results, UNION provides a flexible way to achieve your goals. Remember to carefully structure your SELECT statements and consider using UNION ALL when you want to preserve duplicate rows in the result set.