MariaDB Union Operator

In this MariaDB tutorial, we will learn about the “MariaDB UNION“, the UNION operator helps in adding the result set from multiple SELECT statements. Additionally, we are going to cover the following topics.

• MariaDB union
• MariaDB union all
• MariaDB union all performance
• MariaDB union SQL injection or select injection
• MariaDB union intersect
• MariaDB union subquery
• MariaDB union all order by
• MariaDB union minus
• MariaDB union group by
• MariaDB union all limit
• MariaDB union all group by
• MariaDB union count
• MariaDB union distinct
• MariaDB union select
• MariaDB union sum

MariaDB Union

The UNION operator adds the result of more than two SELECT statements in MariaDB. This means if we have two or more SELECT statements in our query, then combine the result of these SELECT statements using the UNION operator.

• Each select statement must contain the exact number of columns, if we combine the two select statements, these two select statements must contain the exact number of columns.
• The order of the columns must be same within the SELECT statements.
• The columns within the SELECT statment should be simliar data types.
• The result returened by the query that contains the UNION operator is going to have same column names that is specified in the first SELECT statements.
• The UNION operator adds the records vertically.

The syntax is given below for the UNION operator.

(SELECT column_1, column_2 ..column_N FROM table_name)
UNION
(SELECT column_1, column_2 ..column_N FROM table_name);

Where,

• column_1, column_2 ..column_N: These are columns of the table that we want to be shown in the result. This columns order and data types should be exact in another SELECT statement.
• UNION: It is the operator that merges the result of two or more SELECT statements.
• table_name: This is the table whose records we want to show within the result set.

For the example, we will use the sales table that already exists in the database, the description of the table is given below.

Let’s run the below query to know which country has sold the minimum and maximum item type in the unit.

(SELECT Country, MIN(`Units Sold`)  FROM sales
GROUP BY Country
ORDER BY MIN(`Units Sold`)
LIMIT 1)
UNION
(SELECT Country, MAX(`Units Sold`)  FROM sales
GROUP BY Country
ORDER BY MAX(`Units Sold`) DESC
LIMIT 1);

The output of the above code is given below.

The output shows that the country Seychelles has sold the minimum unit of 13 and Guinea-Bissau has sold the maximum unit of 9998.

We can also see that the records are added vertically.

Also, check: How to Create Trigger in MariaDB

MariaDB Union All

By default, the UNION operator doesn’t include the duplicate records from the result of multiple statements into a single result set. To add the duplicate records into a result set the ALL keyword is used with UNION as UNION ALL.

The syntax is given below.

(SELECT column_1, column_2 ..column_N FROM table_name)
UNION ALL
(SELECT column_1, column_2 ..column_N FROM table_name);

Where,

• column_1, column_2 ..column_N: These are columns of the table that we want to be shown in the result. This columns order and data types should be exact in another SELECT statement.
• UNION: It is the operator that merges the result of two or more SELECT statements including the duplicate records.
• table_name: This is the table whose records we want to show within the result set.

Let’s run the same code that we have used in the above sub-section.

(SELECT Country, MIN(`Units Sold`) AS min_units FROM sales
GROUP BY Country
ORDER BY `Units Sold`)
UNION ALL
(SELECT Country, MIN(`Units Sold`) AS min_units  FROM sales
GROUP BY Country
ORDER BY `Units Sold`);

The above query contains the two queries within parenthesis and is joined with the UNION ALL operator. Look at the first query with parenthesis, Where selecting the columns Country and performing an aggregate function which is MIN() on a column Unit Sold to find the minimum number of units sold by each country of table Sales.

Then grouped the rows according to the column Country using the command GROUP BY Country. At last, ordered the records by column Unit Sold.

The same thing happens in the second query within parenthesis after the operator UNION ALL in the above code.

The output of the above code is given below.

Look in the above output, the result set also contains duplicate values because of the operator UNION ALL.

Read: How to Add Column in MariaDB

MariaDB Union All Performance

The UNION all performance is better than the query containing only UNION operator in MariaDB, In other words, The query containing UNION ALL is faster than the query containing only UNION.

Here we will measure the performance of queries in execution time or how much time does query takes to execute.

To measure the execution time of each query in MariaDB, first, initialize the profiling variable to value 1 using the below query.

SET profiling =1;

After initializing the above variable, run any query and use the below code to check the execution time or duration of that query.

SHOW PROFILES;

Let’s take an example to know the performance of UNION ALL and UNION operator.

(SELECT Region,Country, `Total Profit` FROM sales
WHERE country = 'United States of America')
UNION
(SELECT Region,Country, `Total Profit` FROM sales
WHERE country = 'United Kingdom')

After running the above code, run the below query to know the execution time.

SHOW PROFILES;

The output of the above code is given below.

Note down the duration of the query by looking at the above output.

Now run the same query with UNION ALL using the below code.

(SELECT Region,Country, `Total Profit` FROM sales
WHERE country = 'United States of America')
UNION ALL
(SELECT Region,Country, `Total Profit` FROM sales
WHERE country = 'United Kingdom')

Check the duration of the above query using the below code.

SHOW PROFILES;

The output of the above code is given below.

From the above output, we concluded that the second query that contains the UNION ALL operator is faster in performance with the UNION operator. As the UNION ALL take less time than UNION.

Read: MariaDB Vs SQL Server – Detailed Comparison

MariaDB Union SQL Injection or Select Injection

The SQL injection is the attack that is used by an attacker to manipulate the queries that a program or application sends to the database.

• When the application is vulnerable to SQL injection, then an attacker enters the random command into the database to reveal the SQL statement in error logs. By taking the advantage of the error logs about the SQL statement, the attacker gets access to the application database.
• After knowing that a program or application is vulnerable to SQL injection, the UNION SQL injection attack is done against the database to get a single result set from multiple tables.

We already know about the syntax of SQL queries with the UNION operator from the above sub-section.

Read: How to Create Table in MariaDB

MariaDB Union Intersect

The INTERSECT operator in MariaDB returns the records that exist in the result set of both select STATEMENTS. Suppose we have two SELECT statements separately, then intersection can be performed on the result set generated by that two SELECT statements. The INTERSECT will show the records that exist in both results sets.

Here we will use the UNION with INTERSECT operator.

Let’s run the below example to see the maximum profit in both countries the United States of America and the United Kindom.

(SELECT Region,Country, MAX(`Total Profit`) AS Max_Profit FROM sales
WHERE country = 'United States of America')
UNION
(SELECT Region,Country, MAX(`Total Profit`) AS Max_Profit FROM sales
WHERE country = 'United Kingdom')

INTERSECT

(SELECT Region,Country, MAX(`Total Profit`) AS Max_Profit FROM sales
WHERE country = 'United States of America')
UNION
(SELECT Region,Country, MAX(`Total Profit`) AS Max_Profit FROM sales
WHERE country = 'United Kingdom')

The above query contains the two union queries within parenthesis and is joined with the INTERSECT operator.

Look at the first union query with parenthesis, Where selecting the columns Region, Country and performing an aggregate function which is MAX() on a column Total Profit to find the maximum total profit earned by each country.

We are finding the total profit earned by the country United States of America and the United Kingdom which is specified by the condition in WHERE clause.

The same thing happens in the second union query after the operator INTERSECT.

The output of the above code is given below.

From the above output, The maximum profit of the United States of America and the United Kingdom are 1,167,433.750000 and 616,822.560000 respectively.

Read: How to Grant User Access to a MariaDB Database

MariaDB Union Subquery

The SUBQUERY in MariaDB is the query within the query, this subquery is also called a nested query. Here we will use the query containing the UNION operator as a subquery.

we are going to use the same table that is shown in the above sub-section “MariaDB Union”.

Let’s run the below code as an example.

SELECT Region,Country, `Item Type`, `Sales Channel` FROM ((SELECT * FROM sales
WHERE Country LIKE 'a%'
LIMIT 10
)
UNION
(SELECT * FROM sales
WHERE Country LIKE 's%'
LIMIT 10
)) Sales_Data;

Here we are selecting the columns Region, Country, Item Type and Sales Channel from subquery SELECT * FROM sales WHERE Country LIKE 'a%' LIMIT 10. This subquery selects all columns that are represented using the asterisk symbol (*) of Country whose name starts with the character ‘a’ which is specified using Country LIKE 'a%' in WHERE clause.

At last, we are using the keyword LIMIT 10 to show only the ten records as a result. The LIMIT is used here because the table contains thousands of records so it is not easy to show all records in one window.

The output of the above code is given below.

In the above output, The result is generated using the first SELECT statement that contains the subquery, and the result of the second statement that doesn’t contain any subquery is added vertically to the result of the first SELECT statement.

Read: How to Create Database in MariaDB

MariaDB Union All Order By

The ORDER BY clause can be used with the UNION for each SELECT statement to order the records in a specific way in MariaDB.

The syntax is given below.

(SELECT column_1, column_2 ..column_N FROM table_name
ORDER BY column_name
UNION
(SELECT column_1, column_2 ..column_N FROM table_name
ORDER BY column_name);

Where,

• column_1, column_2 ..column_N: These are columns of the table that we want to be shown in the result. This columns order and data types should be exact in another SELECT statement.
• ORDER BY column_name : It is statement to order the records in specific order according to the specified column_name.
• UNION: It is the operator that merges the result of two or more SELECT statements including the duplicate records.
• table_name: This is the table whose records we want to show within the result set.

Let’s run the below query to see the how ORDER BY clause works with UNION ALL.

(SELECT Country, `Item Type`, `Order Date` FROM sales
ORDER BY Country, `Item Type`, `Order Date`
LIMIT 10
)
UNION ALL
(SELECT Country, `Item Type`, `Order Date` FROM sales
ORDER BY `Order Date`, Country, `Item Type` LIMIT 10
);

Here we have combined the result of two SELECT statements using the operator UNION ALL. In the first SELECT statement within parenthesis, we are selecting the columns Country, Item Type and Order Date from table Sales.

Then ordered the records in specific way firs by Country then Item Type and Order Date, At last, the LIMIT 10 keyword is used to show only 10 records from the result of the first SELECT statement.

The same thing happened with the second SELECT statement within parenthesis after the operator UNION ALL, there is only change with order of columns first by Order Date then Country and Item Type.

The output of the above code is given below.

Read: How To Check MariaDB Version 

MariaDB Union Minus

The MINUS operator has changed to EXCEPT in MariaDB, the EXCEPT operator returns the rows from the LEFT SELECT statement only and not from the result of the RIGHT SELECT statement.

Let’s run the below query to know the total profit of the country United States of America among all countries and except United Kindom.

(SELECT Region,Country, `Total Profit` FROM sales
WHERE country = 'United States of America')
UNION
(SELECT Region,Country, `Total Profit` FROM sales
WHERE country = 'United Kingdom')

EXCEPT

(SELECT Region,Country,`Total Profit`  FROM sales
WHERE country = 'United Kingdom')

Look at the above code in the query before the operator EXCEPT, the two SELECT statements are used with the UNION operator. The two SELECT statement shows the columns Region, Country and Total Profit of country United Sates of America and United Kindom.

After the operator EXCEPT, one single SELECT statement is used, the result set produced by this statement will not be included in the final result set.

The output of the above code is given below.

Read: MariaDB Update Statement 

MariaDB Union Group By

The GROUP BY clause can be used with the UNION for each SELECT statement to group the records based on specific columns in MariaDB.

The syntax is given below.

(SELECT column_1, column_2 ..column_N FROM table_name
GROUP BY column_name
UNION
(SELECT column_1, column_2 ..column_N FROM table_name
GROUP BY column_name);

Where,

• column_1, column_2 ..column_N: These are columns of the table that we want to be shown in the result. This columns order and data types should be exact in another SELECT statement.
• GROUP BY column_name : It is statement to group the records according to the specified column_name.
• UNION: It is the operator that merges the result of two or more SELECT statements including the duplicate records.
• table_name: This is the table whose records we want to show within the result set.

Let’s run the below query to see how GROUP BY works with the UNION operator.

(SELECT Country, `Item Type`, `Order Date` FROM sales
GROUP BY `Order Date`
LIMIT 10
)
UNION
(SELECT Country, `Item Type`, `Order Date` FROM sales
GROUP BY `Country`);

The output of the above code is given below.

Read: MariaDB regexp + Examples

MariaDB Union All Limit

The LIMIT keyword can be used with the MariaDB UNION operator to show the limited number of records.

The syntax is given below.

(SELECT column_1, column_2 ..column_N FROM table_name)
UNION ALL
(SELECT column_1, column_2 ..column_N FROM table_name)
LIMIT value;

Where,

• column_1, column_2 ..column_N: These are columns of the table that we want to be shown in the result. This columns order and data types should be exact in another SELECT statement.
• UNION ALL: It is the operator that merges the result of two or more SELECT statements including the duplicate values.
• table_name: This is the table whose records we want to show within the result set.
• LIMIT value: It is used to limit the records in result set, value is the number of recores that we want in reslut set.

Let’s take an example using the LIMIT with UNION operator.

(SELECT Region,Country, `Total Profit` FROM sales)
UNION ALL
(SELECT Region,Country, `Total Profit` FROM sales)
LIMIT 10;

Read: MariaDB Window functions

MariaDB Union Count

In MariaDB, The COUNT function can be used with the UNION operator to count the number of values or items in a column of the table.

Let’s take an example using the COUNT function with the UNION operator.

(SELECT COUNT(`Item Type`), `Item Type`,Country FROM sales
WHERE Country = 'United States of America'
GROUP BY `Item Type`)
UNION
(SELECT COUNT(`Item Type`), `Item Type`,Country FROM sales
WHERE Country = 'Libya'
GROUP BY `Item Type`)

Here in the above code, we are counting the Item Type using the function COUNT(Item Type) for country United States of America and Libya. Then group the records according to columns Item Type using the GROUP BY Item Type.

The output of the code is given below.

The people from both countries order the Item Type minimum two times and maximum 4 times.

Read: MariaDB Row_Number Tutorial

MariaDB Union Sum

The SUM function sums the column values into single values, here the SUM function will be used with the UNION operator with SELECT statement.

Let’s take an example using the SUM function with the UNION operator.

(SELECT Country, SUM(`Total Profit`) FROM sales 
WHERE Country = 'Afghanistan')
UNION
(SELECT Country, SUM(`Total Profit`) FROM sales 
WHERE Country = 'Libya');

Here in the code, the first SELECT statement find the sum of columns Total Profit of the country Afghanistan of the table Sales. The second SELECT statement after the UNION operator find the sum of columns Total Profit of the country Libya.

The output of the above code is given below.

From the output, we can see the sum of the total profit of the country Afghanistan and Libya is 2,730,814.200000 and 4,759,733.960000 respectively.

Read: MariaDB DateTime Tutorial + Examples

MariaDB Union Distinct

The DISTINCT function is used to filter the unique values from a column. Generally, DISTINCT will remove the duplicate values and shows only the unique values of columns. So we will use the DISTINCT with UNION operator in this section.

The syntax is given below.

(SELECT column_1, column_2 ..column_N FROM table_name)
UNION DISTINCT
(SELECT column_1, column_2 ..column_N FROM table_name)

Where,

• column_1, column_2 ..column_N: These are columns of the table that we want to be shown in the result. This columns order and data types should be exact in another SELECT statement.
• UNION DISTINCT: It is the operator that merges the result of two or more SELECT statements by removing the duplicate records.
• table_name: This is the table whose records we want to show within the result set.

First, we will take an example without the DISTINCT function using the below query.

(SELECT `Item Type`, Country  FROM sales
WHERE Country = 'United States of America' )
UNION ALL 
(SELECT `Item Type`, Country  FROM sales
WHERE Country = 'United Kingdom' );

The above code is going to show the Item Type sold by country United States of America and United Kingdom. The UNION ALL is used to include the duplicate records or values also.

The output of the code is given below.

From the output, we can see that each value in column Item Type has its duplicate values for country United States of America and United Kindom.

Run the second example with the DISTINCT function to remove the duplicate values.

(SELECT `Item Type`, Country  FROM sales
WHERE Country = 'United States of America' )
UNION DISTINCT 
(SELECT `Item Type`, Country  FROM sales
WHERE Country = 'United Kingdom' );

The output of the second example is given below.

Look in the output of the second example, the duplicate records or values are not included in the above result set because we have used the DISTINCT function with the UNION operator.

But in the first sub-section of this tutorial, we have learned that only using the UNION operator, the result set of the multiple SELECT statements can be joined together vertically without duplicate values.

Let’s have one more example only with the UNION operator.

(SELECT `Item Type`, Country  FROM sales
WHERE Country = 'United States of America' )
UNION
(SELECT `Item Type`, Country  FROM sales
WHERE Country = 'United Kingdom' );

The output is given below.

As we can see from the output, we have achieved the same result set as in the above example before this example. So if we need the distinct values in our result set, we can use either UNION or UNION ALL.

MariaDB Union Select

The UNION can also operate on the SELECT statement containing UNION, which means two SELECT statements will be the result of UNION, then we will find the UNION of these results.

Let’s take an example using the below query.

(SELECT `Item Type`, Country  FROM sales
WHERE Country = 'United States of America' )
UNION
(SELECT `Item Type`, Country  FROM sales
WHERE Country = 'United Kingdom' )

UNION

(SELECT `Item Type`, Country  FROM sales
WHERE Country = 'Canada' )
UNION
(SELECT `Item Type`, Country  FROM sales
WHERE Country = 'Libya' );

In the above code, we have provided the UNION of two SELECT statements to the UNION operator as a result set. Consider this result set as the SELECT statement to the UNION operator. This is also called the UNION OF UNION.

These statements will show the item type of columns Item Type of countries United States of America, United Kindom, Canada and Libya.

The output of the code is given below.

In the above output, we can see that the columns Item Type shows the time order by country.

MariaDB Union All Group By

The main difference between UNION and UNION ALL is UNION will not include duplicate records whereas UNION ALL will include duplicate records.

Here we will use the same example that we have used in the above subsection “MariaDB Union group by”. In that example instead of UNION, we will use here UNION ALL.

(SELECT Country, `Item Type`, `Order Date` FROM sales
GROUP BY `Order Date`
LIMIT 10
)
UNION ALL

(SELECT Country, `Item Type`, `Order Date` FROM sales
GROUP BY `Country`);

Also, take a look at some more MariaDB tutorials.

• MariaDB Order By Clause
• How to Remove User in MariaDB
• How to Create View in MariaDB
• How to import CSV files in MariaDB
• MariaDB Timestamp + Examples

So, in this tutorial, we have learned about the “MariaDB Union” and covered the following topics.

• MariaDB union
• MariaDB union all
• MariaDB union all performance
• MariaDB union SQL injection or select injection
• MariaDB union intersect
• MariaDB union subquery
• MariaDB union all order by
• MariaDB union minus
• MariaDB union group by
• MariaDB union
• MariaDB union all
• MariaDB union all performance
• MariaDB union SQL injection or select injection
• MariaDB union intersect
• MariaDB union subquery
• MariaDB union all order by
• MariaDB union minus
• MariaDB union group by
• MariaDB union all limit
• MariaDB union all group by
• MariaDB union count
• MariaDB union distinct
• MariaDB union select
• MariaDB union sum