How to write a UNION in SQL without using UNION

MySQL does not support UNION prior to version 4.0.0, but it is possible to write a UNION with a mutex table and LEFT OUTER JOIN. As an example, UNION these tables together:

create table colors (
    title varchar(20)
);
create table flavors(
    title varchar(20)
);
insert into colors values ('blue'), ('green');
insert into flavors values ('vanilla'), ('chocolate');

The trick is to LEFT OUTER join such that the values from one table do not get included in the same row with values from other tables, then COALESCE the values to select the (only) non-NULL value from each row. This is possible by using the mutex table as the leftmost table, then LEFT OUTER joining onto the tables which hold the data you really want:

select coalesce(c.title, f.title)
from mutex
left outer join colors as c on i = 0
left outer join flavors as f on i = 1
where i in (0, 1);

The result is not as efficient as a true UNION, but it works. This technique simulates UNION ALL; to simulate UNION, use SELECT DISTINCT instead.


About The Author

Baron is the founder and CEO of VividCortex. He is the author of High Performance MySQL and many open-source tools for performance analysis, monitoring, and system administration. Baron contributes to various database communities such as Oracle, PostgreSQL, Redis and MongoDB.


Comments