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.
