MariaDB Union

Summary: in this tutorial, you will learn how to use the MariaDB union operator to combine result sets of two or more queries to a single result set.

Introduction to MariaDB union operator

The union operator combines the result sets of two or more select statements into a single result set.

The following illustrates the syntax of the union operator:

select-statement1
union [all | distinct]
select-statement2
union [all | distinct]
...
Code language: SQL (Structured Query Language) (sql)

[order by sort_expression]

[limit row_count, offset]

In this syntax:

• First, specify at least two select statements that you want to combine their result sets.
• Second, use the union operator to connect two select statements.

The select lists of the select statements must have the same number of columns. In addition, the corresponding columns of the first select statement must have the same data type as the columns of the second select statement.

The union operator uses the column names of the first select statement as the column names of the final result set.

all vs. distinct

When evaluating the above query, MariaDB evaluates each select statement individually and combines rows of each result set.

The distinct option instructs the union operator to remove duplicate rows from the final result set while the all option retains the duplicates.

The following pictures illustrate the union and union all of two result sets:

The distinct or all option is optional. By default, the union operator uses the all option if you don’t explicitly specify either all or distinct.

union and order by

The select-statements may have order by clauses. However, these order by clauses do not affect the order of rows in the final result set.

The query that contains union operators can have a global order by clause that appears at the end of the statement. This order by clause will determine the order of rows in the final result set.

MariaDB union examples

We’ll use the guests and vips tables from the sample database for the demonstration.

A) Simple MariaDB union example

The following statement uses the union operator to combine result sets of the guests and vips table:

select      
    vip_id id
    name,   
    'vip' as type
from vips   
union       
select      
    guest_id
    name,   
    'guest' 
from guests;
Code language: SQL (Structured Query Language) (sql)

In addition to the identity and name columns of the vips and guests tables, we also add the type column that specifies the type of contacts.

B) MariaDB union distinct example

The following example uses the union operator to return distinct names from the vips and guests tables:

select name from guests
union
select name from vips
order by name;
Code language: SQL (Structured Query Language) (sql)

The query above is equivalent to the following one that uses the distinct option explicitly:

select name from guests
union distinct
select name from vips
order by name;    
Code language: SQL (Structured Query Language) (sql)

C) MariaDB union all example

This example uses the union all to returns all names from the vips and guests tables, including duplicates:

select name from guests
union all
select name from vips
order by name;
Code language: SQL (Structured Query Language) (sql)

union vs. join

A join combines columns of two or more tables while a union combine rows of the result sets of two or more select statements.

Simply put, a join appends result sets horizontally while a union appends result sets vertically.

In this tutorial, you learned how to use the MariaDB union and union all operators to combine result sets of two or more select statements into a single result set.