SQL — запрос с INNER JOIN не работает должным образом
У меня есть таблица table_tudo и followItem , в таблице table_tudo у меня есть 1 столбец с именем tipo , который может иметь одно из трех значений tipoEp, tipoOv and tipoFm на этих двух Таблицы я хочу INNER JOIN столбец dateMD и itemName после этого я хочу распечатать на экране только штрихи, которые соответствуют этому условию table_tudo.dateMD > followItem.dateMD AND followItem.user_id = :user_id AND table_tudo.tipo = :tipoEp OR table_tudo.tipo = :tipoFm OR table_tudo.tipo = :tipoOv .
В конце моего запроса я использовал GROUP BY , потому что он показывал дублированные строки.
Я не могу использовать AND для INNER JOIN 2 столбцов из 2 таблиц, затем я попытался использовать OR , и это сработало, но теперь у меня есть другая проблема, условие <
Что не так с моим запросом? PS: переменные внутри bindParam являются глобальными переменными.
Я провел несколько тестов и понял, что строки из table_tudo.tipo = :tipoFm OR table_tudo.tipo = :tipoOv выводятся на экран только в том случае, если у меня есть эта строка в моем запросе table_tudo.tipo = :tipoEp .
Я считаю, что проблема в INNER JOIN , потому что я проверил эти запросы ниже, и это сработало:
4 ответа
Вы просите даты быть равными И больше, чем в то же время
Вам, вероятно, нужно использовать скобки с условиями OR, чтобы контролировать способ их интерпретации.
Вы полагаетесь на нестандартную группу MySQL, чтобы отфильтровать нежелательные строки, но это только приблизительные значения.
Ваш основной запрос был в основном на цели. Здесь он отформатирован так, чтобы немного облегчить чтение таблиц. Я добавил псевдонимы для имен таблиц, чтобы упростить тоже. СОЕДИНЕНИЕ между таблицами T и FI (псевдоним) — это только те, которые определяют записи . совпадающие по ID, пользователю и дате БОЛЬШЕ. Это часть 1.
СЛЕДУЮЩИЙ, это ограничение на классификатор «TIPO». Я изменил это на использование предложения IN, так что это правда, если TIPO ЛЮБОЙ из тех, кто в списке.
Вероятно, причина, по которой у вас возникла проблема с записями, которые не были возвращены должным образом, заключалась в том, что у вас были условия OR после всех ands без скобок. Так что, как только у него был матч, он присоединился ко всем, вероятно, в декартовом результате.
Для того, чтобы ваш GROUP by, вам нужно добавить список столбцов из каждой таблицы, чтобы ограничить количество дубликатов и указать, какие столбцы таблицы вам нужны.
В вашем запросе есть несколько проблем по сравнению с описанием цели:
2 условия в вашем соединении требуют AND , а не OR . Кроме того, большинство WHERE условий могут быть перемещены непосредственно в JOIN , что облегчает понимание логики
проблемы с приоритетом операторов в вашем предложении WHERE ; вам нужно заключить часть OR в круглые скобки, иначе она не будет выполнять то, что вы думаете . AND ( table_tudo . tipo = :tipoEp OR table_tudo . tipo <
PS: я сомневаюсь, что GROUP BY полезен, пытаясь удалить его и посмотреть, что произойдет.
Источник
Понимание джойнов сломано. Продолжение. Попытка альтернативной визуализации
Многие из вас читали предыдущую статью про то, как неправильная визуализация для объяснения работы JOIN-ов в некоторых случаях может запутать. Круги Венна не могут полноценно проиллюстрировать некоторые моменты, например, если значения в таблице повторяются.
При подготовке к записи шестого выпуска подкаста «Цинковый прод» (где мы договорились обсудить статью) кажется удалось нащупать один интересный вариант визуализации. Кроме того, в комментариях к изначальной статье тоже предлагали похожий вариант.
Все желающие приглашаются под кат
Итак, визуализация. Как мы выяснили в комментах к предыдущей статье, join — это скорее декартово произведение, чем пересечение. Если посмотреть, как иллюстрируют декартово произведение, то можно заметить, что зачастую это прямоугольная таблица, где по одной оси идет первое отношение, а по другой — второе. Таким образом элементы таблицы будут представлять собой все комбинации всего.
Сложно абстрактно это нарисовать, поэтому придется на примере.
Допустим, у нас есть две таблицы. В одной из них
Сразу disclaimer: я назвал поле словом «id» просто для краткости. Многие в прошлой статье возмущались, как это так — id повторяются, безобразие. Не стоит сильно переживать, ну
представьте, например, что это таблица с ежедневной статистикой, где для каждого дня и каждого юзера есть данные по посещению какого-нибудь сайта. В общем, не суть.
Итак, мы хотим узнать, что же получится при различных джойнах таблиц. Начнем с CROSS JOIN:
CROSS JOIN
CROSS JOIN — это все все возможные комбинации, которые можно получить из двух таблиц.
Визуализировать это можно так: по оси x — одна таблица, по оси y — другая, все клеточки внутри (выделены оранжевым) — это результат
INNER JOIN
INNER JOIN (или просто JOIN) — это тот же самый CROSS JOIN, у которого оставлены только те элементы, которые удовлетворяют условию, записанному в конструкции «ON». Обратите внимание на ситуацию, когда записи дублируются — результатов с единичками будет четыре штуки.
LEFT JOIN
LEFT OUTER JOIN (или просто LEFT JOIN) — это тоже самое, что и INNER JOIN, но дополнительно мы добавляем null для строк из первой таблицы, для которой ничего не нашлось во второй
RIGHT JOIN
RIGHT OUTER JOIN ( или RIGHT JOIN) — это тоже самое, что и LEFT JOIN, только наоборот. Т.е. это INNER JOIN + null для строк из второй таблицы, для которой ничего не нашлось в первой
→ Поиграть с запросами можно здесь
Выводы
Вроде бы получилась простая визуализация. Хотя в ней есть ограничения: здесь показан случай, когда в ON записано равенство, а не что-то хитрое (любое булево выражение). Кроме того не рассмотрен случай, когда среди значений таблицы есть null. Т.е. это всё равно некоторое упрощение, но вроде бы получилось лучше и точнее, чем круги Венна.
Подписывайтесь на наш подкаст «Цинковый прод», там мы обсуждаем базы данных, разработку софта и прочие интересные штуки.
Источник
Соединение таблиц – операция JOIN и ее виды
Говоря про соединение таблиц в SQL, обычно подразумевают один из видов операции JOIN. Не стоит путать с объединением таблиц через операцию UNION. В этой статье я постараюсь простыми словами рассказать именно про соединение, чтобы после ее прочтения Вы могли использовать джойны в работе и не допускать грубых ошибок.
Соединение – это операция, когда таблицы сравниваются между собой построчно и появляется возможность вывода столбцов из всех таблиц, участвующих в соединении.
Придумаем 2 таблицы, на которых будем тренироваться.
Таблица «Сотрудники», содержит поля:
- id – идентификатор сотрудника
- Имя
- Отдел – идентификатор отдела, в котором работает сотрудник
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | 1 |
| 2 | Федор | 2 |
| 3 | Алексей | NULL |
| 4 | Светлана | 2 |
Таблица «Отделы», содержит поля:
- id – идентификатор отдела
- Наименование
| id | Наименование |
|---|---|
| 1 | Кухня |
| 2 | Бар |
| 3 | Администрация |
Давайте уже быстрее что-нибудь покодим.
INNER JOIN
Самый простой вид соединения INNER JOIN – внутреннее соединение. Этот вид джойна выведет только те строки, если условие соединения выполняется (является истинным, т.е. TRUE). В запросах необязательно прописывать INNER – если написать только JOIN, то СУБД по умолчанию выполнить именно внутреннее соединение.
Давайте соединим таблицы из нашего примера, чтобы ответить на вопрос, в каких отделах работают сотрудники (читайте комментарии в запросе для понимания синтаксиса).
Получим следующий результат:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 4 | Светлана | Бар |
Из результатов пропал сотрудник Алексей (id = 3), потому что условие «Сотрудники.Отдел = Отделы.id» не будет истинно для этой сроки из таблицы «Сотрудники» с каждой строкой из таблицы «Отделы». По той же логике в результате нет отдела «Администрация». Попробую это визуализировать (зеленные линии – условие TRUE, иначе линия красная):
Если не углубляться в то, как внутреннее соединение работает под капотом СУБД, то происходит примерно следующее:
- Каждая строка из одной таблицы сравнивается с каждой строкой из другой таблицы
- Строка возвращается, если условие сравнения является истинным
Если для одной или нескольких срок из левой таблицы (в рассмотренном примере левой таблицей является «Сотрудники», а правой «Отделы») истинным условием соединения будут являться одна или несколько срок из правой таблицы, то строки умножат друг друга (повторятся). В нашем примере так произошло для отдела с поэтому строка из таблицы «Отделы» повторилась дважды для Федора и Светланы.
Перемножение таблиц проще ощутить на таком примере, где условие соединения будет всегда возвращать TRUE, например 1=1:
В результате получится 12 строк (4 сотрудника * 3 отдела), где для каждого сотрудника подтянется каждый отдел.
Также хочу сразу отметить, что в соединении может участвовать сколько угодно таблиц, можно таблицу соединить даже саму с собой (в аналитических задачах это не редкость). Какая из таблиц будет правой или левой не имеется значения для INNER JOIN (для внешних соединений типа LEFT JOIN или RIGHT JOIN это важно. Читайте далее). Пример соединения 4-х таблиц:
Как видите, все просто, прописываем новый джойн после завершения условий предыдущего соединения. Обратите внимание, что для Table_3 указано несколько условий соединения с двумя разными таблицами, а также Table_1 соединяется сама с собой по условию с использованием сложения.
Строки, которые выведутся запросом, должны совпасть по всем условиям. Например:
- Строка из Table_1 соединилась со строкой из Table_2 по условию первого JOIN. Давайте назовем ее «объединенной строкой» из двух таблиц;
- Объединенная строка успешно соединилась с Table_3 по условию второго JOIN и теперь состоит из трех таблиц;
- Для объединенной строки не нашлось строки из Table_1 по условию третьего JOIN, поэтому она не выводится вообще.
На этом про внутреннее соединение и логику соединения таблиц в SQL – всё. Если остались неясности, то спрашивайте в комментариях.
Далее рассмотрим отличия остальных видов джойнов.
LEFT JOIN и RIGHT JOIN
Левое и правое соединения еще называют внешними. Главное их отличие от внутреннего соединения в том, что строка из левой (для LEFT JOIN) или из правой таблицы (для RIGHT JOIN) попадет в результаты в любом случае. Давайте до конца определимся с тем, какая таблица левая, а какая правая.
Левая таблица та, которая идет перед написанием ключевых слов [LEFT | RIGHT| INNER] JOIN, правая таблица – после них:
Теперь изменим наш SQL-запрос из самого первого примера так, чтобы ответить на вопрос «В каких отделах работают сотрудники, а также показать тех, кто не распределен ни в один отдел?»:
Результат запроса будет следующим:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 3 | Алексей | NULL |
| 4 | Светлана | Бар |
Как видите, запрос вернул все строки из левой таблицы «Сотрудники», дополнив их значениями из правой таблицы «Отделы». А вот строка для отдела «Администрация» не показана, т.к. для нее не нашлось совпадений слева.
Это мы рассмотрели пример для левого внешнего соединения. Для RIGHT JOIN будет все тоже самое, только вернутся все строки из таблицы «Отделы»:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 4 | Светлана | Бар |
| NULL | NULL | Администрация |
Алексей «потерялся», Администрация «нашлась».
Вопрос для Вас. Что надо изменить в последнем приведенном SQL-запросе, чтобы результат остался тем же, но вместо LEFT JOIN, использовался RIGHT JOIN?
Ответ. Нужно поменять таблицы местами:
В одном запросе можно применять и внутренние соединения, и внешние одновременно, главное соблюдать порядок таблиц, чтобы не потерять часть записей (строк).
FULL JOIN
Еще один вид соединения, который осталось рассмотреть – полное внешнее соединение.
Этот вид джойна вернет все строки из всех таблиц, участвующих в соединении, соединив между собой те, которые подошли под условие ON.
Давайте посмотрим всех сотрудников и все отделы из наших тестовых таблиц:
| id | Имя | Отдел |
|---|---|---|
| 1 | Юлия | Кухня |
| 2 | Федор | Бар |
| 3 | Алексей | NULL |
| 4 | Светлана | Бар |
| NULL | NULL | Администрация |
Теперь мы видим все, даже Алексея без отдела и Администрацию без сотрудников.
Вместо заключения
Помните о порядке выполнения соединений и порядке таблиц, если используете несколько соединений и используете внешние соединения. Можно выполнять LEFT JOIN для сохранения всех строк из самой первой таблицы, а последним внутренним соединением потерять часть данных. На маленьких таблицах косяк заметить легко, на огромных очень тяжело, поэтому будьте внимательны.
Рассмотрим последний пример и введем еще одну таблицу «Банки», в которой обслуживаются наши придуманные сотрудники:
| id | Наименование |
|---|---|
| 1 | Банк №1 |
| 2 | Лучший банк |
| 3 | Банк Лидер |
В таблицу «Сотрудники» добавим столбец «Банк»:
| id | Имя | Отдел | Банк |
|---|---|---|---|
| 1 | Юлия | 1 | 2 |
| 2 | Федор | 2 | 2 |
| 3 | Алексей | NULL | 3 |
| 4 | Светлана | 2 | 4 |
Теперь выполним такой запрос:
В результате потеряли информацию о Светлане, т.к. для нее не нашлось банка с (такое происходит из-за неправильной проектировки БД):
| id | Имя | Отдел | Банк |
|---|---|---|---|
| 1 | Юлия | Кухня | Лучший банк |
| 2 | Федор | Бар | Лучший банк |
| 3 | Алексей | NULL | Банк Лидер |
Хочу обратить внимание на то, что любое сравнение с неизвестным значением никогда не будет истинным (даже NULL = NULL). Эту грубую ошибку часто допускают начинающие специалисты. Подробнее читайте в статье про значение NULL в SQL.
Пройдите мой тест на знание основ SQL. В нем есть задания на соединения таблиц, которые помогут закрепить материал.
Дополнить Ваше понимание соединений в SQL могут схемы, изображенные с помощью кругов Эйлера. В интернете много примеров в виде картинок.
Если какие нюансы джойнов остались не раскрытыми, или что-то описано не совсем понятно, что-то надо дополнить, то пишите в комментариях. Буду только рад вопросам и предложениям.
Привожу простыню запросов, чтобы Вы могли попрактиковаться на легких примерах, рассмотренных в статье:
Источник
