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 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;
WHERE clauses may also be different of course, it this makes sense.
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
If the subqueries contain
WHERE clauses, make sure they use an index to keep the overall query fast.