MariaDB UNION
To combine the output sets of two or more MariaDB SELECT statements, thus removing the duplicate rows between the SELECT statements’ results, the MariaDB UNION 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 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
SELECT trainer_id
FROM trainers; |
Output:
ID
1
2
3
4
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 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.