Эмуляция FULL OUTER JOIN в MySQL?

Question

[Артур Когут]

Здравствуйте.

Есть базы `paragraph`(`paragraph`, `position`) и `figure`(`figure`, `figcaption`, `position`), например, со следующими данными:

Paragraph | 1
Paragraph | 3

Figure | Figcaption | 2.

Нужно слить их воедино по колонке `position`, чтобы в итоге получить следующее:

1 | Paragraph | (NULL) | (NULL)
2 | (NULL) | Figure | Figcaption
3 | Paragraph | (NULL) | (NULL).

LEFT JOIN UNION RIGHT JOIN не выполняет свою функцию, ибо записи не группируются по `position`, эта колонка вовсе дублируется для каждой из таблиц.

WHERE не учитывается, если указывать его в подзапросах.
А вот это запрос без общих JOIN, быстрее и выводит все то самое, но еще и корректно учитывает WHERE.


Так лучше?

Answer 1

[WebSpider]

SELECT * FROM
(SELECT DISTINCT position FROM paragraph
UNION
SELECT DISTINCT position FROM figure
) t1
LEFT JOIN paragraph USING (position)
LEFT JOIN figure USING (position)

Comments

[WebSpider]

Примерный запрос, чтобы был понятен принцип

[Артур Когут]

Все отлично работает, только вот, как добавить условие в подзапросы? Я добавляю условия, но после JOIN, все-равно условия сбрасываются.

[Артур Когут]

Я использую WHERE COALESCE(paragraph.section, figure.section) = 5, после JOIN. Но подозреваю, что это не очень правильно.

Помогите сделать правильно, а? :)

[WebSpider]

Что значит "условия сбрасываются"?

> подозреваю, что это не очень правильно.
почему?

[Артур Когут]

@WebSpider: если написать where в подзапросе, после JOIN, WHERE уже не действителен. Например, WHERE section = 1, а в итоге, мне выводит все секции.

> почему?
Если честно, я вообще профан в СУБД. Но планирую выучить. Мне кажется, что 2 запроса, 2 JOIN еще и по функции прогонять это не слишком продуктивно. Для вас это нормальный код?

Можно ведь обойтись и без JOIN, просто UNION LEFT JOIN + UNION RIGHT JOIN и нужные поля склеивать по COALESCE. Этот вариант продуктивнее?

[WebSpider]

Вот пример: sqlfiddle.com/#!2/394d65/2
Условие WHERE отлично работает после JOIN

> Можно ведь обойтись и без JOIN, просто UNION LEFT JOIN + UNION RIGHT JOIN и нужные поля склеивать по COALESCE. Этот вариант продуктивнее?
Не совсем понятно что вы имеете ввиду. UNION не заменяет JOIN, это разные вещи

[WebSpider]

> Для вас это нормальный код?
Зависит от условй. Если данных в таблице относительно немного или запрос выполняется нечасто, то код вполне нормальный. По крайней мере рабочий. Если нужно оптимизировать, тут просто изменением запроса проблему не решить

[WebSpider]

> Например, WHERE section = 1, а в итоге, мне выводит все секции.
Если условие в подзапросе с UNION, то нужно писать его в обе таблицы, разумеется, если нужно, чтобы только section = 1 попало в результирующий набор

[Артур Когут]

@WebSpider: если указывать section в подзапросах, where в итоге все-равно не учитывает. Скриншот в ответе ниже. Работает, только если указать WHERE во всех запросах.

[Артур Когут]

@WebSpider: >> UNION не заменяет JOIN, это разные вещи, я написал ваш запрос без JOIN (смотрите в обновлениях вопроса).

[WebSpider]

Секундочку! Получается, что в каждой таблице имеется несколько строк с одинаковым position, а выбрать надо по одной из каждой?

[Артур Когут]

@WebSpider: нет, position всегда уникальный, но всегда присутствует, как и присутствует `section`. Просто нужно выбрать из 2 таблиц с похожим содержанием контент, и группировать его по `position`, где `section` у обоих определенное число.

[WebSpider]

Можете скинуть ссылку на дамп таблицы?

[Артур Когут]

@WebSpider: да, конечно. arturkohut.com/temp/arturkohutcom.sql . В принципе, оба запроса дают нужный результат. Вариант без JOIN вроде бы выполняет меньше действий и думаю, будет работать быстрее. Но снова-таки, я дилетант.

И если сможете, прокомментируйте общую структуру базы. Здесь я описал, почему я не храню статью, как HTML код searchengines.guru/showthread.php?t=870731 .

[WebSpider]

Вы сказали, что position уникальный. Я же вижу 3 строки с position=1 в figure-section-article и 3 строки с position=2 в paragraph-section-article. М?

[Артур Когут]

@WebSpider: section+position всегда уникальный. Выборка происходит всегда только для 1 section, соответственно, position всегда уникальны и их можно расположить по порядку.

[Артур Когут]

@WebSpider: там даже UNIQUE индекс есть.

[Артур Когут]

@WebSpider: и снова-таки, мы сейчас работаем с таблицами paragraph... и figure... именно их нужно объединять. Для таблицы секций, пара article+position тоже всегда уникальна, а выборка производится всегда для 1 article.

[Артур Когут]

@WebSpider: имеем 3 запроса. Выборка статей SELECT
`id`,
`uri`,
UNIX_TIMESTAMP(`date-modified`) as `date-modified`,
`title`
FROM `article`
ORDER BY `date-modified` DESC
LIMIT $start_limit, $end_limit;

[Артур Когут]

@WebSpider: выборка секцией для 1 статьи (в цикле):

SELECT
`id`,
`title`
FROM `section-article`
WHERE `on-summary` = 'true'
AND `article` = $id
ORDER BY `position`;

[Артур Когут]

@WebSpider: и выборка наследников секции (параграфы, фигуры, списки):

SELECT
`paragraph`,
`resource`,
`figcaption`
FROM
((SELECT `position`
FROM `paragraph-section-article`
WHERE `section` = $id)
UNION
(SELECT `position`
FROM `figure-section-article`
WHERE `section` = $id)
) `paragraph+figure`
LEFT JOIN `paragraph-section-article` USING (`position`)
LEFT JOIN `figure-section-article` USING (`position`)
WHERE COALESCE(`paragraph-section-article`.`section`, `figure-section-article`.`section`) = $id
ORDER BY `position`;

[Артур Когут]

@WebSpider: мы сейчас на последнем цикле запросов.

[Артур Когут]

@WebSpider: как видите, я пока использую ваш запрос. Но WHERE мне надо прописывать 3 раза, причем еще и с COALESCE.

[WebSpider]

Тогда уж вот так:

SELECT
  `paragraph`,
  `resource`,
  `figcaption`
FROM
(
  SELECT `position`
  FROM `paragraph-section-article`
  WHERE `section` = 5
  UNION
  SELECT `position`
  FROM `figure-section-article`
  WHERE `section` = 5
) `paragraph+figure`
LEFT JOIN (SELECT * FROM `paragraph-section-article` WHERE section = 5) t1 USING (`position`)
LEFT JOIN (SELECT * FROM `figure-section-article` WHERE section = 5) t2 USING (`position`)
ORDER BY `position`;

[WebSpider]

но с точки зрения производительности, это, конечно, неоптимально

[WebSpider]

в плане скорости оптимальным вариантом скорее всего будет за 2 запроса выбрать отдельно из figure-section-article и paragraph-section-article по ключу, а потом обединять уже за пределами базы

[Артур Когут]

@WebSpider: то-есть, сливать два результата в 1 массив, сортировать по position и тогда снова запускать цикл? А если к figure, paragraph еще добавить lists, polls?

[Артур Когут]

@WebSpider: и о какой разнице в производительности идет речь? В среднем, статья будет состоят из 10 секций и по 5 наследников к секции. Я планирую, записывать результаты выборки в memcached, до следующего UPDATE, которые будут крайне в исключительных случаях.

[WebSpider]

В общем, если я правильно понял, в итоге должно получиться что-то типа этого:

SELECT * FROM (SELECT 
  `position`,
  null `paragraph`,
  `resource`,
  `figcaption`
FROM `figure-section-article` WHERE section = 5
UNION
SELECT 
  `position`,
  `paragraph`,
  null `resource`,
  null `figcaption`
FROM `paragraph-section-article` WHERE section = 5) t
ORDER BY `position`

[WebSpider]

В таком случае действительно JOIN не нужен, а нужен UNION. Но т.к. вопрос был про JOIN, я и отвечал про JOIN :)

[Артур Когут]

@WebSpider: мой запрос с JOIN в подзапросах менее оптимальный?

[WebSpider]

@Galer: зависит от того используются ли в нём ключи. Если да, то большой разницы нет. По идее должны работать быстро оба варианта

[Артур Когут]

@WebSpider: ну а какой самый компактный запрос вы бы могли предложить, уже понимая суть?

[WebSpider]

...а ещё можно объединить эти две таблицы в одну, тогда и запрос намного упростится. Т.е. какой смысл делить на несколько таблиц, если в выборке они всё равно объединяются?

[Артур Когут]

@WebSpider: индексы используются. Движок InnoDB.

[Артур Когут]

@WebSpider: >> В общем, если я правильно понял, в итоге должно получиться что-то типа этого.

Да, именно это. Это лучше всех предыдущих вариантов?

[WebSpider]

@Galer: они все примерно одинаковы по скорости, но лучший на мой взгляд вариант — это объединить всё в одну таблицу, как я написал выше

[Артур Когут]

@WebSpider: я все-таки newbe, и мне наверное, лучше придерживаться нормализации. Технически, их возможно объединить. Но по сути, а разве стоит ради 1 фигуры в статьи, выбирать 3 лишние колонки, а главное, и резервировать для их место? Разве при вставке абзаца, обильное количество индексов для соединенной таблицы (здесь и абзацы, здесь и фигуры, здесь и списки (планирую)), не будет медленнее?

[Артур Когут]

@WebSpider: просто мне кажется, база должна быть базой. И целостность информации здесь главное. Поэтому, придерживаюсь минимум 3 нормальной формы. Если я буду запросы выборки и циклов кешировать, как обычный HTML код, в ОЗУ. Ведь сложная архитектура не так скажется на производительности. Да и надеюсь, что сложная, а не глупая. Если вы читали тему, ссылку на которую я давал выше.

[Артур Когут]

@WebSpider: хотя я ставлю двусмысленные вопросы. Здесь производительность, здесь целостность. Это растерянность. Ничего в этом не смыслю. Спасибо за советы, в любом случае.

[WebSpider]

@Galer: тут как всегда палка о двух концах. С одной стороны вы правы, что некоторые колонки будут пустыми, но зато выборка будет быстрее. Насколько быстрее и сколько места займут пустые колонки — вопрос в какой-то степени риторический, т.е. пока на него ответить невозможно, надо смотреть на реальных данных. Ссылку к сожалению не читал в виду нехватки свободного времени. Тем не менее могу сказать, что бы вы ни выбрали, всё это будет работать. В дальнейшем если обнаружатся какие-то серьёзные проблемы (в чём лично я сомневаюсь), можно будет изменить структуру и т.д.