Concatenate multiple columns from the same table

Sometimes we want to concatenate multiple table columns, and see them as a single column in our resultset. This may be the case, for example, if we have columns like email1, email2, email3.

Concatenating columns with UNION

To concatenate columns, we select them with different queries, and merge these queries with UNION. The following example shows how to concatenate three different columns:

(SELECT id, email1 AS email FROM customer)
UNION
(SELECT id, email2 AS email FROM customer)
UNION
(SELECT id, email3 AS email FROM customer)
ORDER BY id, email;

As you can see, it’s important that all the queries return the same columns. Since the name of the second column is different for each query, we specified an alias to avoid that the DBMS decides a name for us.

ORDER BY is optional of course, but it’s often useful.

What if a user has the same email repeated in all the three columns? In the example above, duplicate rows are eliminated from the resultset. We can also avoid this by replacing UNION with UNION ALL. Or we can specify UNION DISTINCT, so the next person who reads the query will understand immediately that we decided to include duplicates.

If we need to add a condition, we can add identical WHERE clauses to each subquery:

(
    SELECT id, email1 AS email
        FROM customer
        WHERE email LIKE 'a%';
) UNION (
    SELECT id, email2 AS email
        FROM customer
        WHERE email LIKE 'a%';
) UNION (
    SELECT id, email3 AS email
        FROM customer
        WHERE email LIKE 'a%';
) ORDER BY id, email;

The WHERE clauses may also be different of course, it this makes sense.

Performance

For big resultsets, UNION ALL can be much faster than UNION. If duplicate rows are not possible, or if it’s not important to remove them, we should use UNION ALL.

If the subqueries contain WHERE clauses, make sure they use an index to keep the overall query fast.