

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- 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 prepared statements in MySQL?
- How can we use nested transactions in MySQL?
- How can we use MySQL ALTER TABLE command for adding comments on columns?
- 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 MySQL SUM() function with HAVING clause?
- How can we use a MySQL subquery with INSERT statement?