How can we use MySQL UNION operator on datasets?

Basically, MySQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION operator must have the same number of fields in the result sets within similar data types. Its syntax would be as follows −

Syntax

SELECT expression1, expression2, … expression_n
FROM table
[WHERE conditions]
UNION [DISTINCT]
SELECT expression1, expression2, … expression_n
FROM table
[WHERE conditions]

Here, expression1, expression2, … expression_n are the columns that we wish to retrieve.

Table, are the tables from which we want to retrieve the records.

WHERE condition, it is optional that must be met for the records to be selected.

DISTINCT, it is also optional that removes duplicates from the result set, but the inclusion of the DISTINCT modifier has no impact on the result set of the UNION operator because by default UNION operator already removes duplicates.

Example

In this example, we are two tables namely Student_detail and Student_info having the following data −

mysql> Select * from Student_detail;
+-----------+---------+------------+------------+
| studentid | Name    | Address    | Subject    |
+-----------+---------+------------+------------+
|    101    | YashPal | Amritsar   | History    |
|    105    | Gaurav  | Chandigarh | Literature |
|    130    | Ram     | Jhansi     | Computers  |
|    132    | Shyam   | Chandigarh | Economics  |
|    133    | Mohan   | Delhi      | Computers  |
|    150    | Rajesh  | Jaipur     | Yoga       | 
|    160    | Pradeep | Kochi      | Hindi      |
+-----------+---------+------------+------------+
7 rows in set (0.00 sec)

mysql> Select * from Student_info;
+-----------+-----------+------------+-------------+
| studentid | Name      | Address    | Subject     |
+-----------+-----------+------------+-------------+
|    101    | YashPal   | Amritsar   | History     |
|    105    | Gaurav    | Chandigarh | Literature  |
|    130    | Ram       | Jhansi     | Computers   |
|    132    | Shyam     | Chandigarh | Economics   |
|    133    | Mohan     | Delhi      | Computers   |
|    165    | Abhimanyu | Calcutta   | Electronics |
+-----------+-----------+------------+-------------+
6 rows in set (0.00 sec)

Now, the following query using UNION operator returns all the ‘studentid’ values both the tables.

mysql> Select Studentid FROM student_detail UNION SELECT Studentid FROM student_info;
+-----------+
| Studentid |
+-----------+
|    101    |
|    105    |
|    130    |
|    132    |
|    133    |
|    150    |
|    160    |
|    165    |
+-----------+
8 rows in set (0.00 sec)

Ankitha Reddy

Published on 22-Feb-2018 08:24:45

• Related Questions & Answers
• How can we use Python Ternary Operator Without else?
• How can we use MySQL SUM() function?
• Can we use str_replace in MySQL?
• How can we use MySQL ALTER TABLE command for adding comments on columns?
• How can we use prepared statements in MySQL?
• How can we use nested transactions in MySQL?
• How can we use MySQL SELECT without FROM clause?
• How can we create and use a MySQL trigger?
• How can we use SIGNAL statement with MySQL triggers?
• How can we use nested transactions allowed in MySQL?
• How can we use a diamond operator with anonymous classes in Java 9?
• How to use union and order by clause in MySQL?
• How can we use MySQL INSTR() function with WHERE clause?
• How can we use FIND_IN_SET() function with MySQL WHERE clause?
• How can we use ASCII() function with MySQL WHERE clause?