MySQL UNION

In this tutorial, we are going to see how to use the MySQL UNION operator to combine two or more result sets from multiple SELECT statements into a single result set.
 

UNION operator

MySQL UNION operator allows you to combine two or more query result sets into a single result set.
 

Syntax:

SELECT column1, column2, ... column_n
FROM table1
[WHERE conditions]
UNION [DISTINCT | ALL]
SELECT column1, column2, ... column_n
FROM table2
[WHERE conditions];

 

 

Example:

Let’s look at the following example. We have two tables: table1 and table2

SELECT ID
FROM table1
UNION
SELECT ID
FROM table2;

 

 
By default, UNION operator removes duplicate rows, even if you do not explicitly specify the DISTINCT operator.

Since values 2 and 3 are duplicated, UNION removed them and kept only the unique values.

If you explicitly use UNION ALL, the duplicated rows, remain in the result. Since UNION ALL does not need to handle duplicates, it is faster than UNION DISTINCT.

SELECT ID
FROM table1
UNION ALL
SELECT ID
FROM table2;

 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More