MySQL Union

The MySQL UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows by default, but you can also use UNION ALL to include duplicates. The UNION operator is typically used when you want to merge data from multiple tables or query different parts of a table with similar structures.

Here’s the basic syntax for using UNION:

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

• column1, column2, …: The columns you want to select from the tables.
• table1, table2: The tables or subqueries from which you want to retrieve data.

Example using UNION:

SELECT product_name, product_price
FROM products
UNION
SELECT service_name, service_price
FROM services;

In this example, two SELECT statements retrieve data from the products and services tables, and the results are combined into a single result set. Duplicate rows are removed by default.

If you want to include duplicate rows, you can use UNION ALL:

SELECT product_name, product_price
FROM products
UNION ALL
SELECT product_name, product_price
FROM featured_products;

In this case, the result set includes all rows from both queries, even if there are duplicates.

It’s important to note that when using UNION, the number and data types of columns selected in all SELECT statements must be the same, or you will encounter an error. The columns are matched based on their position in the result set.

The UNION operator is a powerful tool for combining data from different sources or applying complex filtering and sorting to separate result sets.