How can we use MySQL UNION operator on a dataset?

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

grammar

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

Here, Expression 1, Expression 2,...Expression_n are the columns we want to retrieve.

Table is the table from which we want to retrieve records.

WHERE condition, It is optional and must be met to select records.

DISTINCT, Removing duplicates from the result set is also optional, but including the DISTINCT modifier has no effect on the result set of the UNION operator, because the UNION operator already removes duplicates by default.

Example

In this example, we have two tables, Student_detail and Student_info, with 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 the UNION operator will return all "studentid" values ​​in both 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)

The above is the detailed content of How can we use MySQL UNION operator on a dataset?. For more information, please follow other related articles on the PHP Chinese website!