Mutex tables in SQL

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 LEFT JOIN.

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.

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and many open-source tools for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB.