Xaprb

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.

You might also like:

1. How to write a UNION in SQL without using UNION
2. The integers table
3. How to write INSERT IF NOT EXISTS queries in standard SQL
4. How to simulate FULL OUTER JOIN in MySQL
5. How to write subqueries without using subqueries in SQL