MariaDB UNION Keyword
The MariaDB UNION keyword is used to combine the result-set of two or more SELECT statements. While using UNION keyword, the following criteria must be satisfied:
- Number of columns in each SELECT statement should equal.
- Datatype of columns in each SELECT statement should match.
- Order of columns in each SELECT statement should match.
Syntax
The syntax for using UNION keyword in MariaDB is given below:
SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2
Example:
Consider database tables called Employee and Vendor with the following records:
Table 1: Employee table
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | London | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
Table 2: Vendor table
VendorID | Name | City | Age |
---|---|---|---|
1 | John | London | 29 |
2 | Ramesh | New Delhi | 28 |
3 | Suresh | New Delhi | 31 |
-
To fetch distinct cities from tables Employee and Vendor, the query is given below:
SELECT City FROM Employee UNION SELECT City FROM Vendor ORDER BY City;
This will produce the result as shown below:
City London New Delhi Paris -
Using with WHERE clause: To fetch distinct names and cities from tables Employee and Vendor where age of the person is less than 30, the following query can be used:
SELECT Name, City FROM Employee WHERE Age < 30 UNION SELECT Name, City FROM Vendor WHERE Age < 30 ORDER BY City;
This will produce the following result:
Name City John London Marry London Ramesh New Delhi Jo Paris -
Using Alias: Using alias is a good way to identify records of different tables. See the example below:
SELECT 'Employee' AS Type, Name, City FROM Employee WHERE Age < 30 UNION SELECT 'Vendor', Name, City FROM Vendor WHERE Age < 30 ORDER BY City;
This will produce the result as shown below:
Type Name City Employee John London Employee Marry London Vendor John London Vendor Ramesh New Delhi Employee Jo Paris
❮ MariaDB Keywords