FULL OUTER JOIN vs UNION
В связи с P. P. S. вот в этом посте. Стало мне интересно, какой же запрос быстрее - тот, который от меня ожидал, вероятно, получить собеседователь (с UNION'ом) или тот, который я пытался предложить (с FULL OUTER JOIN). Вот оба:
SELECT dep_id,
SUM(CASE WHEN money>0 then money ELSE 0 END) AS plus,
SUM(CASE WHEN money<0 then -money ELSE 0 END) AS minus,
SUM(money) AS balance FROM
(SELECT dep_id, money, cdate FROM test_plus
UNION
SELECT dep_id, -money, cdate FROM test_minus) bal
GROUP BY dep_id
SELECT COALESCE(p.dep_id,m.dep_id) AS dep_id,
COALESCE(p.money,0) AS plus, COALESCE(m.money,0) AS minus,
COALESCE(p.money,0)-COALESCE(m.money,0) AS balance FROM
(SELECT dep_id, SUM(money) AS money FROM test_plus GROUP BY dep_id) p FULL OUTER JOIN
(SELECT dep_id, SUM(money) AS money FROM test_minus GROUP BY dep_id) m ON p.dep_id=m.dep_id
Не учитывая индексы, предполагаю, что FULL OUTER JOIN быстрее. По следующим соображениям. Обозначим размеры таблиц как m и n. Посчитаем продолжительность запроса с UNION'ом. Продолжительность SELECT'ов пропорциональна m и n, продолжительность агрегирования по их UNION-объединению - квадрату их суммы. Итого: m+n+(m+n)2. Теперь посчитаем продолжительность запроса с FULL OUTER JOIN. Продолжительность агрегирований по таблицам пропорциональна квадратам m и n соответственно, продолжительность их JOIN-объединения - произведению размеров таблиц после агрегирования, т. е. a*m*b*n, где 0
Далее: (m+n+(m+n)2)-(m2+n2+c*m*n)=m+n+2*m*n-c*m*n=m+n+(2-c)*m*n>0. Получаем, что теоретически запрос с UNION медленнее, чем запрос с FULL OUTER JOIN, причём отставание растёт пропорционально произведению размеров таблиц.
Проверим на большом примере. Запишем в обе таблицы по 10 000 строк со случайными значениями, причём dep_id - в диапазоне от 0 до 5000. Уникальных dep_id получилось 4333 и 4330; между собой они пересекаются частично. Оба запроса вытаскивают 4929 записей. Запрос с UNION отрабатывает за 470-560 мс, а с FULL OUTER JOIN - за 200-220 мс. Преимущество FULL OUTER JOIN налицо. Вот только оно работает в PostgreSQL, но не в MySQL (в MySQL его можно имитировать).