Working with Union in MySQL

Introduction

In this tutorial, I am going to explain working with MySQL UNION operator with examples. Without wasting time, let’s start.

UNION In MySQL

UNION is used for combining two or more separate commands to return a single output. The the union operator is used to combine the result set of two or more SELECT statements. UNION is useful because we have some information in multiple tables, and we want to fetch rows from all of them at once. We want to select several sets of rows from the same table, but some conditions that characterize each set are not easy to write as a single WHERE clause. UNION allows retrieval of each set with a simpler WHERE clause in its own SELECT statement. The rows retrieved by each are combined and produced as the final command result. The unions are useful if you wish to join the results from more than one query, but be aware that the number of the returned columns by each select query should be the same.

Syntax 

SELECT column1, column2, ...
FROM table_name1
UNION [ALL | DISTINCT]
SELECT column1, column2, ...
FROM table_name2
[UNION [ALL | DISTINCT]
SELECT column1, column2, ...
FROM table_name3
...];

Example 

SELECT id,name FROM my_table

UNION

SELECT id,name FROM my_table;

Without wasting time, let’s see the example

CREATE DATABASE UNION_OPERATORS;
USE UNION_OPERATORS;

/ Create tables /
CREATE TABLE U1 (
    U_id INT NOT NULL
);

CREATE TABLE U2 (
    U_id INT NOT NULL
);

/ Insert some values into them /
INSERT INTO U1 VALUES (101), (201), (301);
INSERT INTO U2 VALUES (51), (201), (301);

/ Now, retrieve the result from the SELECT query /
SELECT U_id
FROM U1
UNION
SELECT U_id
FROM U2;

Now, try to understand the concept of UNION with a Venn diagram.

UNION ALL

The UNION ALL operator selects all rows from each table and combines it into a single table. The UNION and UNION ALL are different in that UNION ALL will not drop duplicate rows and UNION eliminates duplicate rows. It just pulls all rows from all tables fitting our command specifics and combines them into a table.

SELECT U_id
FROM U1
UNION ALL
SELECT U_id
FROM U2;

UNION Joins

Union Join allows the results of two commands to be combined into one output result. Union Join is done by having 2 or more commands together by the UNION operator.

Syntax

SELECT <fields>
FROM <table_name>
WHERE <conditions>
UNION
SELECT <fields>
FROM <table_name>
WHERE <conditions>;

Example

This example shows the combination of two queries with one result set.

SELECT*FROM emp_info
WHERE emp_status='CONFIRMED'
UNION
SELECT*FRO Memp_info
WHERE emp_status='Notice'

UNION vs JOIN

The basic difference between the UNION and JOIN is that UNION combines the result set horizontally, whereas the JOIN statement combines the result sets vertically. The below picture will illustrate the difference between the UNION statement and JOIN.

Now, take some examples from a dummy database which I have attached to this article, in which we have some tables and data inside it.

SELECT * FROM bookorder;

SELECT*FROM library;

SELECT booknumber
FROM bookorder
UNION
SELECT booknumber
FROM library;

 

MySQL UNION and ORDER BY

We can use the ORDER BY clause with the UNION operator.

SELECT booknumber
FROM bookorder
UNION
SELECT booknumber
FROM library
ORDERBY booknumberdesc;

Reference

https://www.mysqltutorial.org/

Conclusion

In this article, I have discussed the concept of UNION in MySQL with various examples. I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.

Thanks for reading this article!