How to use MySQL UNION operator?
If we want to combine the result sets of multiple select queries into a single result set then we can use the MySQL UNION operator.
Before using UNION we need to consider the following points.
- The data type for each matching column in the select query should be the same.
- The order of the columns selected should be the same in each query.
The syntax for UNION.
SELECT column_name1, column_name2… FROM schema.table1
UNION
SELECT column_name1, column_name2… FROM schema.table2;
UNION
SELECT column_name1, column_name2… FROM schema.table3;
To understand UNION Operator we will create the following two tables for users and will add data as shown in the images.
Create table query for facebook_users:
CREATE TABLE `facebook_users` (
`user_id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) NOT NULL,
`user_email` varchar(45) DEFAULT NULL,
`user_number` varchar(15) DEFAULT NULL,
`user_status` varchar(10) NOT NULL DEFAULT ‘ACTIVE’,
`password` varchar(45) NOT NULL,
`created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`birthdate` datetime DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `facebook_user_id_UNIQUE` (`user_id`)
);
Create table query for insta_users:
CREATE TABLE `insta_users` (
`user_id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) NOT NULL,
`user_email` varchar(45) DEFAULT NULL,
`user_number` varchar(15) DEFAULT NULL,
`status` varchar(10) NOT NULL DEFAULT ‘ACTIVE’,
`password` varchar(45) NOT NULL,
`created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`birthdate` datetime DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `insta_user_id_UNIQUE` (`user_id`)
);
Example for UNION
We will select all the columns from both tables by using the UNION operator.
SELECT user_id,user_name,user_email,user_number,user_status,password,created_on,birthdate FROM facebook_users
UNION
SELECT user_id,user_name,user_email,user_number,status as user_status,password,created_on,birthdate FROM insta_users;
We will select a few columns from both tables using UNION.
SELECT user_name,user_email,user_number,user_status FROM facebook_users
UNION
SELECT user_name,user_email,user_number,status as user_status FROM insta_users;
- As we can see from the above example since we are selecting from both tables but records returned are less than the previous union query.
- This is because all the columns we are selecting from both tables have precisely the same values for user names as ‘PCY’ and ‘KMS’.
- In the previous query, we have included the user_id column which has different IDs for these 2 users hence we got separate records.
- Hence we can say that UNION selects only distinct values. This is a significant point to note while using the UNION operator.
- If we still want different records even if all the selected column values are the same we can use UNION ALL operator.
Example for UNION ALL
We will use the same query as above and just will replace UNION with UNION ALL as shown below.
SELECT user_name,user_email,user_number,user_status FROM facebook_users
UNION ALL
SELECT user_name,user_email,user_number,status as user_status FROM insta_users;
