What is the difference between UNION and UNION ALL in MySQL? — MySQL Interview Question #26

In MySQL, both UNION and UNION ALL are used to combine the result sets of two or more SELECT statements. However, there is a significant difference between these two operators, which is explained below:

1. UNION

UNION is used to combine the result set of two or more SELECT statements, and it only returns unique rows from the final result set. It excludes duplicate rows from the result set by comparing each row in the result set. To use UNION operator, the following conditions must be satisfied:

- The number of columns in all SELECT statements must be the same.
- The data types of the corresponding columns in all SELECT statements must be compatible.

The syntax for using UNION operator is as follows:

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

For example, if we have two tables "employees" and "customers", and we want to combine the first name and last name of all employees and customers and return the unique results, we can use the following query:

SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM customers;

2. UNION ALL

UNION ALL also combines the result set of two or more SELECT statements, but it returns all rows from the final result set, including duplicates. It does not remove duplicate rows from the result set. To use UNION ALL operator, the same conditions as for UNION operator needs to be satisfied.

The syntax for using UNION ALL operator is as follows:

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

For example, if we want to combine the first name and last name of employees and customers and return all results (including duplicates), we can use the following query:

SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM customers;

In summary, UNION and UNION ALL are used to combine result sets of two or more SELECT statements, but the former returns unique rows while the latter returns all rows (including duplicates).