Mutex tables in SQL
Posted in Databases on Sep 22, 2005
A “mutex” table is a clever trick that allows joining tables while keeping them independent of each other in a query. This property allows interesting queries that are not otherwise possible. It is especially useful in earlier versions of MySQL, where it can be used to simulate some unsupported queries such as UNION and derived tables in the FROM clause.
My standard mutex table is as follows:
create table mutex( i int not null primary key ); insert into mutex(i) values (0), (1);
I typically use the mutex table as the leftmost table of
LEFT OUTER JOIN queries. For examples, see my articles about simulating
UNION, FULL OUTER JOIN on MySQL, and simulating derived tables in the FROM clause. I add as many values for
i as needed, usually one per table in the
If you insert values 0 through 9, the table can do double duty as an integers table, too. Alternatively, an existing integers table can be used as a mutex table.