A common solution to work around this is using a UNION ALL to union two result sets from a LEFT JOIN and a RIGHT JOIN of two tables, adding join_column IS NULL condition to the latter.

Though it works, it's quite inefficient on large tables when used with ORDER BY … LIMIT queries, as it uses a filesort.

Let's create two sample tables and OUTER JOIN them together.

The tables are quite simple: each of them contains a million of multiples of 13 and 17, respectively, thus making each of LEFT JOIN, RIGHT JOIN and INNER JOIN of these tables non-empty:Table creation details

SELECT t_13.value AS val13, t_17.value AS val17
FROM t_13
LEFT JOIN
t_17
ON t_13.value = t_17.value
UNION ALL
SELECT t_13.value AS val13, t_17.value AS val17
FROM t_13
RIGHT JOIN
t_17
ON t_13.value = t_17.value
WHERE t_13.value IS NULL
ORDER BY
COALESCE(val13, val17)
LIMIT 30

val13

val17

13

17

26

34

39

51

52

65

68

78

85

91

102

104

117

119

130

136

143

153

156

169

170

182

187

195

204

208

221

221

234

30 rows fetched in 0.0018s (17.9216s)

, and works for almost 18 seconds.

We will try to emulate it using a MERGE JOIN algorithm.

As of 5.2.0, MySQL lacks a native support of MERGE JOIN. But basically it look like this:

Allocate a variable called pointer

Set the pointer to the least of minimal value's of both tables

Select all rows from both tables with value equal to the pointer, cross joining them. If the value equal to the pointer is not present in either table, a single NULL row is selected from that table instead

Set the pointer to the least of the minimal value's greater than the current value of pointer from both tables

Repeat steps 3 and 4 until step 4 yields some values from either table

Unfortunately, due to the bug #20111, MySQL will not allow to implement step 4 efficiently using subqueries.

That's why we'll need to create UDF's to select a value next to the given one: