The UNION operator in MariaDB is used to combine the result-set of two or more SELECT queries. Each SELECT query within the UNION must have the same number of columns, the columns must have similar data types, and they must also be in the same order.
The basic syntax is:
```
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
```
This will return all distinct values.
If you want to allow duplicate values, use `UNION ALL`.
Here’s an example:
```
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
```
This will return all distinct cities from “customers” and “suppliers”.
```
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
```
This will return all cities (including duplicates) from “customers” and “suppliers”.
A few things to note when using UNION:
1. The column names from the first SELECT statement are used as the column names for the results returned.
2. The columns in all the SELECT statements should have a similar data type, and the column order should be the same.
3. By default, UNION operator sorts the result-set in ascending order, and removes duplicate rows between the various SELECT statements. If you want to include duplicate results use UNION ALL instead.
4. You can also use the ORDER BY clause to sort the result-set by one or more columns, but the ORDER BY clause must appear after the last SELECT statement.
5. The SELECT statements within the UNION must have the same number of columns. The columns must also have similar data types.
Example using ORDER BY:
```
SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;
```
