Press "Enter" to skip to content

The MySQL UNION operator

The MySQL UNION operator​

The UNION operator in MySQL is used to combine the result sets of two or more SELECT statements into a single result set. The UNION operator removes any duplicate rows between the result sets and orders the result set by the first SELECT statement.

The basic syntax of the UNION operator is as follows:

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

In this syntax, column1, column2, ... are the columns that you want to retrieve data from, table1 and table2 are the tables that contain the data, and the UNION operator combines the result sets of the two SELECT statements into a single result set.

Here is an example of using the UNION operator to combine the result sets of two SELECT statements:

SELECT first_name, last_name, hire_date
FROM employees
WHERE department_id = 1
UNION
SELECT first_name, last_name, hire_date
FROM employees
WHERE department_id = 2;

In this example, the employees table contains data about employees, including first_name, last_name, hire_date, and department_id. The query retrieves all the rows from the employees table where the department_id is either 1 or 2, and combines the two result sets into a single result set.

If you want to include duplicate rows in the result set, you can use the UNION ALL operator instead of the UNION operator. The UNION ALL operator does not remove duplicate rows between the result sets.

Here is an example of using the UNION ALL operator to combine the result sets of two SELECT statements:

SELECT first_name, last_name, hire_date
FROM employees
WHERE department_id = 1
UNION ALL
SELECT first_name, last_name, hire_date
FROM employees
WHERE department_id = 2;

In this example, the query retrieves all the rows from the employees table where the department_id is either 1 or 2, and includes any duplicate rows in the result set.

In conclusion, the UNION operator in MySQL is a powerful tool for combining the result sets of two or more SELECT statements into a single result set. It allows developers to easily retrieve data from multiple tables or with different conditions, making it a valuable tool for data analysis and management.

Table structure Used for Example explanation

mysql> desc address1;
+——-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+—————-+
| sno | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| email | varchar(40) | NO | | NULL | |
+——-+————-+——+—–+———+—————-+
3 rows in set (0.00 sec)

mysql> desc address2;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| sno | int(11) | NO | | NULL | |
| name | varchar(30) | NO | | NULL | |
| email | varchar(40) | NO | | NULL | |
+——-+————-+——+—–+———+——-+

Example for MySQL UNION operator

select name from address1
-> union
-> select name from address2;

Output of Above Example

+——+
| name |
+——+
| web |
| Cat |
| nut |
| abc |
+——+
4 rows in set (0.00 sec)

Next

Mysql Refe