UNION ALL operator in MariaDB

by

MariaDB UNION ALL
To combine the output sets of two or more MariaDB SELECT statements, without removing the duplicate rows between the SELECT statements’ results, the MariaDB UNION ALL operator is used. Each SELECT statement however must have the same number of expressions, and each corresponding expression should be of the same data type.

Syntax: 

SELECT expr_1, expr_2, ... expr_n  
FROM table1  
WHERE conditions  
UNION ALL
SELECT expr_1, expr_2, ... expr_n  
FROM table2  
WHERE conditions;

Example 1: Fetching single field from two tables.
Players Table:

ID	NAME	        SPORTS
1	Sachin	        Cricket
2	Dhoni	        Cricket
3	Sunil	        Football
4	Srikanth	Badminton
5	Mary	        Boxing

Trainers Table:

TRAINER_ID	TRAINER_NAME	TRAINER_SPORTS
5	        Bond	        Football
6	        Smith	        Badminton
7	        Brand	        Boxing

Query: 

SELECT id
FROM players
UNION ALL
SELECT trainer_id  
FROM trainers;

Output: 

ID
1
2
3
4
5
5
6
7

Explanation:
The PLAYERS and the TRAINERS are the already existing tables. A union of ID and TRAINER_ID would appear in the result set.

Example 2: Fetching multiple fields from two tables with conditions and using ORDER BY clause.
Players Table:

ID	NAME	        SPORTS
1	Sachin	        Cricket
2	Dhoni	        Cricket
3	Sunil	        Football
4	Srikanth	Badminton
5	Mary	        Boxing

Trainers Table:

TRAINER_ID	TRAINER_NAME	TRAINER_SPORTS
101	Bond	Football
102	Smith	Badminton
103	Brand	Table Tennis

Query: 

SELECT id, name
FROM players 
UNION ALL
SELECT trainer_id, trainer_name
FROM trainers
WHERE trainer_id >= 102 
ORDER BY id;

Output: 

ID	NAME
1	Sachin
2	Dhoni
3	Sunil
4	Srikanth
5	Mary
102	Smith
103	Brand

Explanation:
The PLAYERS and the TRAINERS are the already existing tables. A union of ID and TRAINER_ID and NAME and TRAINER_NAME would appear in the result set.