Как сделать SQL UNION если в табличках не полные данные?

Question

[Сергей Еремин]

Ни разу не программист, но лет пятнадцать назад похожий запрос однажды построил... Помню, что как-то применил UNION и фиктивные поля типа O AS FIELD... А еще помню, что потратил на него недели три времени (а может и больше). А сейчас мозги совсем заржавели и не соображу даже с чего начать. А надо примерно следующее:

Есть ТАБЛИЦА "ТАРА":

id1 |  НАЗВАНИЕ_ТАРЫ
----+-------------------------
01  |  Ящик
02  |  Корзинка
03  |  Грузовик

Есть ТАБЛИЦА "ГРУЗ":

id2 |  НАЗВАНИЕ_ГРУЗА
----+------------------------
01  |  Огурцы
02  |  Молоко
03  |  Туалетная бумага

Есть "ТАБЛИЦА СВЯЗЕЙ":

link1 |  link2    | КОЛИЧЕСТВО
------+-----------+--------------------
01    |  01       |  15 
01    |  02       |  2
02    |  01       |  4
02    |  03       |  2
03    |  01       |  100
03    |  02       |  60
03    |  03       |  500

И хочется получить:

НАЗВАНИЕ_ТАРЫ  |  НАЗВАНИЕ_ГРУЗА    |  КОЛИЧЕСТВО
---------------+--------------------+----------------
Ящик           |  Огурцы            |  15
Ящик           |  Молоко            |  2
Ящик           |  Туалетная бумага  |  0
Корзинка       |  Огурцы            |  4
Корзинка       |  Молоко            |  0
Корзинка       |  Туалетная бумага  |  2
Грузовик       |  Огурцы            |  100
Грузовик       |  Молоко            |  60
Грузовик       |  Туалетная бумага  |  500

То есть надо получить 0 для поля КОЛИЧЕСТВО в случаеях, когда объекты не связаны через ТАБЛИЦА СВЯЗЕЙ. Как?

Answer 1

[sunrails]

table_box - тара
table_product - груз
table_link - связь

SELECT t1.name, t2.name, IFNULL(t3.qty, 0) as qty FROM table_box as t1 
INNER JOIN table_product as t2 
LEFT JOIN table_link as t3 ON (t3.link1 = t1.id1 AND t3.link2 = t2.id2);

Comments

[Сергей Еремин]

Спасибо. Идею уловил. Хотя почему IFNULL(t3.qty, 0) возвращает ноль когда t3.qty = NONE не ясно. Раньше я делал два запроса t1+ t3 и t2 + t3 с нужными WHERE, и после делал UNION... А ваш вариант получается проще, так как достаточно все условия ко всем таблицам записать в один WHERE.

Вопрос; памяти такое внешнее объединение сильно кушает? Или сначала выполняется WHERE а после LEFT OUTER JOIN?

Заранее спасибо!

[nozzy]

INNER JOIN table_product as t2 - без условия объединения с t1, вернет декартово произведение t1 и t2, вопрос зачем?

[sunrails]

Сергей Еремин: WHERE выполняется в конце. По производительности не скажу сейчас, погоняйте тесты и расскажите :)
nozzy: Зачем INNER JOIN возвращает в данном случае декартово произведение вы можете почитать в оф. документации.

[sunrails]

Сергей Еремин: Замените t1.name, t2.name, IFNULL(t3.qty, 0) на * и все станет понятно :)

[Сергей Еремин]

Вопрос: SWL сначала через WHERE отберет нужные записи из t1 и t2 а после все сольет? Таблицы t1 и t2 могут быть большими, и если будет наоборот (сначала сольет а после отберет записи) то будет плохо... :(

[nozzy]

sunrails:
INNER JOIN используется с условием ON, соответственно возвращаются строки удовлетворяющие условию указанному в ON.
Я понимаю что вы хотите сделать, но называется это - CROSS JOIN.

[Сергей Еремин]

nozzy: Если делать CROSS JOIN то записи из t2 (table_product), для которых нет соответствующих связей с t3 (table_link), не попадут в объединенную табличку. А раз не попадут, то и не ясно чему присвоить нуль... Так что по-любому t2 надо объединять с t3 через OUTER JOIN... Если в запросе есть еще таблицы для объединения (а они есть), то будет "пожиратель памяти".

Я как-то в пятнадцатилетнюю давность делал похожее на Access через UNION. Там в качестве и t1, и t2 выступала одна и та же таблица (и именно поэтому без UNION было никак). Причем эта таблица была могучая! Строился рейтинг изменения читательских предпочтений в публикаций в отраслевом СМИ, а таблица было log-ом. Посетителей было много, а период довольно большим неделя/месяц/квартал... Т.к. я не программист, то писал запрос, помню, очень долго. Сейчас кажется, что месяца два!! И естественно все забыл.

Тут я чувствую, что надо нечто похожее сделать. Сначала сделать две выборки SELECT с разными JOIN а после их слить...

[nozzy]

Сергей Еремин:
я имел ввиду CROSS JOIN между t1 и t2, будет полное декартово произведение:

Ящик | Огурцы
Ящик | Молоко
Ящик | Туалетная бумага
Корзинка | Огурцы
Корзинка | Молоко
Корзинка | Туалетная бумага
и тд
потом соединить с table_link и если - Ящик | Туалетная бумага - нет в table_link, количество будет null, как то так:
Ящик | Туалетная бумага | null

[sunrails]

nozzy: "In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other)." отсюда dev.mysql.com/doc/refman/5.7/en/join.html