MySQL - Getting Started MySQL - Database MySQL - Data Types MySQL - CREATE Table MySQL - DROP Table MySQL - ALTER Table MySQL - INSERT INTO Table MySQL - SELECT FROM Table MySQL - Select using WHERE clause MySQL - WHERE clause with AND, OR and NOT operators MySQL - UPDATE Table MySQL - DELETE FROM Table MySQL - Alias MySQL - ORDER BY and LIMIT MySQL - INNER JOIN MySQL - LEFT JOIN MySQL - RIGHT JOIN MySQL - UNION MySQL - Aggregate Functions - COUNT, MIN, MAX, AVG and SUM MySQL - GROUP BY MySQL - HAVING MySQL - Subquery MySQL - EXISTS MySQL - ANY and ALL MySQL - INSERT INTO table using SELECT statement MySQL - CASE statement MySQL - IF Control Flow Function

MySQL - UNION

MySQL

← Prev Next →

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)

← Prev Next →