MySQL - Union Operator

In MySQL, UNION is a clause used to combine the results of two or more SELECT statements into a single result set.

Suppose you have two tables: "employees" and "contractors". Both tables have columns for "name", "age", and "salary". If you want to create a report that lists all the names, ages, and salaries from both tables, you can use UNION to combine the results of two SELECT statements:

SELECT name, age, salary FROM employees

UNION

SELECT name, age, salary FROM contractors;

In this example, we're selecting the "name", "age", and "salary" columns from both the "employees" and "contractors" tables. We're using UNION to combine the results of these two SELECT statements into a single result set.

The result of this query will be a table that lists all the names, ages, and salaries from both tables. Duplicate rows will be automatically removed from the result set.

It's important to note that the SELECT statements used in a UNION must have the same number of columns, and the columns must have compatible data types. If the SELECT statements have different numbers of columns or incompatible data types, the query will fail.

Another example of using UNION in MySQL:

Suppose you have two tables: "customers" and "suppliers". Both tables have a column for "company_name", "city", and "country". If you want to create a report that lists all the unique company names, cities, and countries from both tables, you can use UNION to combine the results of two SELECT statements:

SELECT company_name, city, country FROM customers

UNION

SELECT company_name, city, country FROM suppliers;

In this example, we're selecting the "company_name", "city", and "country" columns from both the "customers" and "suppliers" tables. We're using UNION to combine the results of these two SELECT statements into a single result set.

The result of this query will be a table that lists all the unique company names, cities, and countries from both tables. Duplicate rows will be automatically removed from the result set.

Also, it's important to note that UNION can be resource-intensive and can slow down performance if used on large tables. Therefore, it's recommended to use UNION only when necessary, and to optimize your queries to improve performance.