Database management is an important concept of handling and organizing data effectively. One powerful Operator in MySQL for combining results from multiple queries is the UNION operator. This operator allows you to merge the results of two or more SELECT statements into a single result set, eliminating duplicate rows by default.

In this article, we’ll explore an in-depth understanding of the UNION operator, along with practical examples to show its usage in managing and querying relational databases.

MySQL UNION Operator

The UNION operator in MySQL is used to combine the results of two or more SELECT statements into a single result set. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types. The columns in the result set are named after the columns in the first SELECT statement.

Syntax:

The syntax for using UNION Operator in MySQL is as follows:

SELECT column1, column2, …

FROM table1

WHERE condition

UNION

SELECT column1, column2, …

FROM table2

WHERE condition;

Parameters

• column1, column2, ...: Columns selected from each SELECT statement must be the same in number and compatible in data type.
• table1, table2, ...: The tables or views from which data is selected.
• condition: Optional conditions to filter rows in each SELECT statement.
• The UNION operator combines the results of the two SELECT statements and removes duplicates by default.

MySQL UNION Operator Examples

Let’s look at some examples of the UNION Operator in MySQL. Learning the UNION Operator with examples will help in understanding the concept better.

First, let’s create a table:

Demo MySQL Tables

We create the first table “students” in this example.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

INSERT INTO students (name, age, grade) VALUES
('Prakash', 15, '10th'),
('Mahesh', 16, '11th'),
('Suresh', 15, '10th');

SELECT * FROM students;

Output:

+----+---------+------+-------+
| id | name    | age  | grade |
+----+---------+------+-------+
|  1 | Prakash |   15 | 10th  |
|  2 | Mahesh  |   16 | 11th  |
|  3 | Suresh  |   15 | 10th  |
+----+---------+------+-------+

To create the second table “teachers“, write the following SQL queries:

CREATE TABLE teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    subject VARCHAR(50),
    years_of_experience INT
);

INSERT INTO teachers (name, subject, years_of_experience) VALUES
('Gaurav', 'Mathematics', 8),
('Yuvraj', 'Science', 10),
('Shruti', 'History', 12);

SELECT * FROM teachers;

Output:

+----+--------+-------------+---------------------+
| id | name   | subject     | years_of_experience |
+----+--------+-------------+---------------------+
|  1 | Gaurav | Mathematics |                   8 |
|  2 | Yuvraj | Science     |                  10 |
|  3 | Shruti | History     |                  12 |
+----+--------+-------------+---------------------+

Example 1: Combing Names from Students and Teachers Table

In this example, we are using the UNION operator to combine the name column from the students table labeled as ‘Student‘ and the name column from the teachers table labeled as ‘Teacher‘.

SELECT name, 'Student' AS type
FROM students
UNION
SELECT name, 'Teacher' AS type
FROM teachers;

Output:

+---------+---------+
| name    | type    |
+---------+---------+
| Prakash | Student |
| Mahesh  | Student |
| Suresh  | Student |
| Gaurav  | Teacher |
| Yuvraj  | Teacher |
| Shruti  | Teacher |
+---------+---------+

Example 2: Combining Names with Conditions

In this example, we are using the UNION operator to combine names from the students and teachers tables based on specific conditions: selecting students with age greater than 15 labeled as ‘Student‘, and selecting teachers with years of experience greater than 8 labeled as ‘Teacher‘.

SELECT name, 'Student' AS type
FROM students
WHERE age > 15
UNION
SELECT name, 'Teacher' AS type
FROM teachers
WHERE years_of_experience > 8;

Output:

+--------+---------+
| name   | type    |
+--------+---------+
| Mahesh | Student |
| Yuvraj | Teacher |
| Shruti | Teacher |
+--------+---------+

Conclusion

In conclusion, the MySQL UNION operator is a useful operator for combining the results of multiple SELECT statements into a single cohesive dataset. By understanding its syntax and practical applications, you can efficiently merge and analyze data from various sources within your database. This operator helps in consolidating similar data structures and eases complex queries, enhancing data management and retrieval.

FAQs on MySQL UNION Operator

How does UNION differ from UNION ALL?

UNION removes duplicate rows from the combined result set, while UNION ALL includes all rows, even duplicates.

Do the SELECT statements in a UNION need to have the same number of columns?

Yes, each SELECT statement must have the same number of columns, and the columns must be of compatible data types.

Can you use UNION with ORDER BY?

Yes, you can use ORDER BY to sort the final result set, but it should be applied at the end of the UNION operation, not within individual SELECT statements.

What happens if the columns selected in each SELECT statement have different data types?

The UNION operator requires that the columns in each SELECT statement be of compatible data types. Incompatible types may result in errors or automatic type conversion.