- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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 Articles
- How can we use Python Ternary Operator Without else?
- How can we use MySQL SUM() function?
- 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 REVERSE() function on column’s data along with WHERE clause?
- Can we use str_replace in MySQL?
- How can we use a diamond operator with anonymous classes in Java 9?
- How can we use MySQL function STR_TO_DATE(Column, ‘%input_format’)?
- 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 to use union and order by clause in MySQL?
- How MySQL evaluates if we use EXISTS operator with the subquery that returns NULL?
