MariaDB UNION Operator

In MariaDB, UNION is a built-in set operator that returns the union of two result sets.

MariaDB UNION Syntax

Here is the syntax of the MariaDB UNION operator:

rs1 UNION rs2
rs1 UNION ALL rs2

Parameters

rs1

Optional. A result set.

rs2

Optional. Another result set.

Both result sets must have the same columns.

Return value

rs1 UNION rs2 returns the union of two result sets. That is, UNION return all rows that of the first result setand the second result set.

MariaDB UNION Examples

First, let’s create two tables test_number_1 and test_number_2 to demonstrate the example:

DROP TABLE IF EXISTS test_number_1;
CREATE TABLE test_number_1 (
    num INT NOT NULL
);

DROP TABLE IF EXISTS test_number_2;
CREATE TABLE test_number_2 (
    num INT NOT NULL
);

Then, let’s insert a few rows:

INSERT INTO test_number_1
VALUES (0), (1), (1), (2), (3), (4), (4);
INSERT INTO test_number_2
VALUES (3), (4), (4), (5), (6), (7), (8);

Then, let’s look at the rows in the test_number_1 table:

SELECT * FROM test_number_1;

Output:

+-----+
| num |
+-----+
|   0 |
|   1 |
|   1 |
|   2 |
|   3 |
|   4 |
|   4 |
+-----+

Let’s look at the rows in the test_number_2 table:

SELECT * FROM test_number_2;

Output:

+-----+
| num |
+-----+
|   3 |
|   4 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
+-----+

If you want to test_number_1 get test_number_2 all the numbers in and , use the following statement:

SELECT * FROM test_number_1
UNION
SELECT * FROM test_number_2;

Output:

+-----+
| num |
+-----+
|   0 |
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
+-----+

Note that UNION returns distinct r by default, use UNION ALL if you want to get all rows.

SELECT * FROM test_number_1
UNION ALL
SELECT * FROM test_number_2;

Output:

+-----+
| num |
+-----+
|   0 |
|   1 |
|   1 |
|   2 |
|   3 |
|   4 |
|   4 |
|   3 |
|   4 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
+-----+

Conclusion

In MariaDB, UNION is a built-in set operator that returns the union of two result sets.