MySQL - UNION
MySQL
In this tutorial we will learn about union and union all in MySQL.
We use the UNION operator when we want to combine the results of multiple SELECT query.
Following are the points to note when using UNION in a query.
- Each SELECT query must have the same number of columns when using UNION.
- The columns in the SELECT query must also be in the same order.
- The data types of the columns in the SELECT query must also match.
Syntax of UNION
mysql> SELECT column_name FROM table_a
UNION
SELECT column_name FROM table_b;
Where, column_name must be of same type and same order in both the SELECT statements.
In this tutorial we will be using the office and employeeaddress table.
Table: office
mysql> SELECT * FROM office;
+----------+--------------+---------------------+--------------+-----------+-------+---------+---------+
| officeid | officename | addressline1 | addressline2 | city | state | country | pincode |
+----------+--------------+---------------------+--------------+-----------+-------+---------+---------+
| o01 | Web Projects | #123 Awesome Street | Super Main | Bangalore | KA | INDIA | 560000 |
| o02 | Apps | #321 Super Street | Action Road | Kolkata | WB | INDIA | 700000 |
| o03 | Game | #12 Super Street | Master Block | Chennai | TN | INDIA | 600000 |
+----------+--------------+---------------------+--------------+-----------+-------+---------+---------+
3 rows in set (0.00 sec)
Table: employeeaddress
mysql> SELECT * FROM employeeaddress;
+----+------------+-------------+--------------------------------+-----------------+-----------+-------+---------+---------+
| id | employeeid | addresstype | addressline1 | addressline2 | city | state | country | pincode |
+----+------------+-------------+--------------------------------+-----------------+-----------+-------+---------+---------+
| 1 | e01 | PRIMARY | #12 street | Action block | Bangalore | KA | INDIA | 560000 |
| 2 | e03 | PRIMARY | Super Housing Complex Block 22 | Major Street | Chennai | TN | INDIA | 600000 |
| 3 | e03 | SECONDARY | House #301 ABC Colony | Jupiter Block | Kolkata | WB | INDIA | 700000 |
| 4 | e02 | PRIMARY | Pizza Street | Foodies Block | Kolkata | WB | INDIA | 700000 |
| 5 | e04 | PRIMARY | Fruits Apartment | Fresh Block | Bangalore | KA | INDIA | 560000 |
| 6 | e05 | PRIMARY | Apartment 10 | Fast Food Block | Chennai | TN | INDIA | 600000 |
+----+------------+-------------+--------------------------------+-----------------+-----------+-------+---------+---------+
6 rows in set (0.00 sec)
UNION example
When using UNION in our query we will get distinct values from the tables.
In the following example we are listing all the city, state and country name that are distinct in both the employeeadress and office tables.
mysql>SELECT city, state, country FROM employeeaddress
UNION
SELECT city, state, country FROM office;
Output:
+-----------+-------+---------+
| city | state | country |
+-----------+-------+---------+
| Bangalore | KA | INDIA |
| Chennai | TN | INDIA |
| Kolkata | WB | INDIA |
+-----------+-------+---------+
3 rows in set (0.00 sec)
In the employeeaddress table we can see repetition of city name like Bangalore, Chennai and Kolkata, state name like KA, TN and WB.
But using the UNION in the query returns the city and state name only once as it is selecting only the unique i.e., distinct values.
UNION ALL
We use the UNION ALL when we want to get all the values and not the distinct values.
Syntax of UNION ALL
mysql> SELECT column_name FROM table_a
UNION ALL
SELECT column_name FROM table_b;
Where, column_name must be of same type and same order in both the SELECT statements.
In the following example we are retrieving all the values using the UNION ALL.
mysql>SELECT city, state, country FROM employeeaddress
UNION ALL
SELECT city, state, country FROM office;
Output:
+-----------+-------+---------+
| city | state | country |
+-----------+-------+---------+
| Bangalore | KA | INDIA |
| Chennai | TN | INDIA |
| Kolkata | WB | INDIA |
| Kolkata | WB | INDIA |
| Bangalore | KA | INDIA |
| Chennai | TN | INDIA |
| Bangalore | KA | INDIA |
| Kolkata | WB | INDIA |
| Chennai | TN | INDIA |
+-----------+-------+---------+
9 rows in set (0.00 sec)
ADVERTISEMENT