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.
[...] To bad the above code does not work in MySql. One solution is to use a mutex table: [...]
John Ha’s Weblog » Blog Archive » INSERT IF NOT EXISTS in MySql
22 Jan 07 at 4:50 am
[...] Wer – aus welchen Gründen auch immer – das Subselect vermeiden möchte, kann zu einem Trick greifen: Eine sogenannte „mutex table“. [...]
Toller Text » Blog Archiv » MySQL-Erkenntnisse die Erste
21 Aug 07 at 1:57 pm
I normally use such tables to enter a date, for example, in an attendance tool. Only if there is the date in the mutex table, the attendance record can be displayed. Otherwise, blank, even if we have the data.
Bimal Poudel
7 Dec 08 at 7:29 am
Great idea, never thought of this before. I shall use this tip in the future.
Many thanks!
Max
10 Nov 09 at 10:44 am