How to write a UNION in SQL without using UNIONThu, Sep 22, 2005 in Databases
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.
I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.