Почему условие левого соединения Mysql не соблюдается?
Запрос возвращает неожиданные результаты: почему?
Таблица заказов:
+----------------+
| Field |
+----------------+
| orderNumber |
| customerNumber |
+----------------+
Таблица деталей заказа:
+-----------------+
| Field |
+-----------------+
| orderNumber |
| productCode |
+-----------------+
Рассмотрим следующий запрос:
SELECT
o.orderNumber,
o.customerNumber,
d.orderNumber,
d.productCode
FROM
orders o
LEFT JOIN orderdetails d
ON ((o.orderNumber = d.orderNumber) AND (o.orderNumber = 10123)) ;
Вот результат:
Я ожидаю увидеть строки, которые имеют orderNumber только 10123
Почему мы видим другие номера заказов? Я бы подумал, что условие соединения (т.е. o.orderNumber = 10123) должно было исключить эти другие строки? Для меня это действительно причудливо и волшебно, с неожиданными результатами: любые разъяснения о том, что происходит, будут очень признательны.
Обновление — добавление условия в предложение WHERE
Это правда, я мог бы добавить o.orderNumber = 10123 в предложение WHERE, что решило бы проблему, но меня беспокоит то, что при включении в предложение ON оно по-прежнему должно работать должным образом, не так ли? Почему? Поскольку условие вернет false, учитывая, что o.orderNumber НЕ 10123, и поэтому его следует исключить из набора результатов?
Обновление: воспроизводимый пример:
Вот база данных mysql: https://sp.mysqltutorial.org/wp-content/uploads/2018/03/mysqlsampledatabase.zip
Вот онлайн-версия, где вы можете вводить команды: https://www.mysqltutorial.org/tryit/
Спасибо!
Ваш синтаксис немного странный. Не включайте (o.orderNumber = 10123) в предложение ON. Переместите его в ГДЕ после соединения.
Я бы переместил o.orderNumber = 10123 из JOIN в WHERE, так как это не имеет отношения к столу, к которому вы присоединяетесь.
Вы используете (o.orderNumber = 10123) в самом JOIN, а не как WHERE o.orderNumber = 10123.
Объясните, почему вы ожидаете того, чего ожидаете, или вы просто просите еще одну презентацию LEFT JOIN, и мы не знаем, где ваши недоразумения/неправильные представления. Обоснуйте то, что вы ожидаете, со ссылкой на авторитетную документацию или вы не исследовали и не объяснили должным образом. Это кодовый вопрос, поэтому, пожалуйста, приведите минимальный воспроизводимый пример. Это включает в себя предыдущий плюс-код и вывод в виде текста, который мы можем вырезать, вставить, запустить и отличить от вашего сообщения, а также того, что вы вырезали, вставили и запустили. PS LEFT JOIN ON возвращает строки INNER JOIN ON UNION ВСЕХ несопоставленных левых строк таблицы, расширенных значениями NULL. Знайте, какое ВНУТРЕННЕЕ СОЕДИНЕНИЕ.
Ответы 4
Если вы хотите отфильтровать набор результатов по заданному номеру заказа, вы должны указать это условие в предложении WHERE:
SELECT ...
FROM orders o
LEFT JOIN orderdetails d ON d.orderNumber = o.orderNumber
WHERE o.orderNumber = 10123;
Это приносит все заказы для данного номера вместе с соответствующими деталями заказа. Если нет сведений о заказе, вы все равно получите строку (строки) заказа для данного номера. Если порядок для этого числа отсутствует, результирующий набор пуст.
...либо так, либо просто измените LEFT JOIN на INNER JOIN !
@Ergis: да, но это отфильтровывает заказы, в которых нет подробностей.
Я уже знаю. Я просто сказал альтернатива, это его выбор!
@GMB спасибо за ваш ответ - меня смущает то, что условие в предложении «ON» должно по-прежнему исключать строки, которые не являются 10123, не так ли? Это связано с тем, что предложение вернет значение FALSE. Mysql затем пропустит эту строку? Мне интересно, почему мы должны фильтровать с помощью предложения WHERE, когда одно и то же условие можно отфильтровать с помощью предложения ON. разъяснения были бы очень признательны. хрс
@BKSpurgeon: он исключает строки, которых нет 10123 в левом соединении, а не во внешнем запросе. Для этого вам нужен пункт where. Или используйте inner join, и вам не нужно об этом беспокоиться.
Короче говоря, вы запрашиваете все записи из таблицы orders. Ваше предложение соединения будет получать данные из orderdetails только тогда, когда у вас есть соответствие orderNumber и когда orderNumber равно 10123, но вы все равно получите все записи из orders.
Если вы хотите получить записи из orders только тогда, когда orderNumber равно 10123, вы должны использовать условие для этого в таблице orders. Обычный способ сделать это — использовать предложение WHERE.
При использовании LEFT JOIN вы получите совпадающие записи (на основе условия) из объединенной таблицы, но вы все равно получите все записи из ЛЕВОЙ таблицы.
Это должно работать.
SELECT
o.orderNumber,
o.customerNumber,
d.orderNumber,
d.productCode
FROM
orders o
LEFT JOIN orderdetails d
ON (o.orderNumber = d.orderNumber)
WHERE o.orderNumber = 10123;
Это улучшение. (Но это все еще неясно. Например, что такое «выборка»? Вы используете его, когда некоторые входные данные отображаются как подстроки выходных строк. Никто не узнает об этом из «выборки». Остальное так же нечетко. Чтобы быть ясным и полным, нужно использовать достаточно слова, чтобы на самом деле сказать, что они означают, а не просто говорить неясные вещи, смутно соответствующие некоторым из них. См. Также версию на момент этого комментария другого ответа, который я прокомментировал. Не ясно. Неполный.)
«Предложение LEFT JOIN позволяет вам запрашивать данные из нескольких таблиц. Оно возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если в правой таблице не найдено совпадающих строк, используется NULL». (https://www.sqlservertutorial.net/sql-server-basics/sql-server-left-join/). Таким образом, действительно нечего ожидать от условий «ON». Для LEFT JOIN они касаются только 2-й таблицы. Я бы порекомендовал автору лучше узнать виды JOIN
Это не ясное объяснение левого соединения. Например, вопреки кавычке ни одна из возвращаемых строк не является входной строкой. Например, возвращенные строки содержат входные строки как подстроки, но вы не говорите, какие именно и как именно. Также этот ответ не связывает то, что вы цитируете, с путаницей или ожиданиями спрашивающего. Кроме того, ваше письмо неясно и расплывчато. Также см. мой комментарий к вопросу.
Если вы хотите, чтобы JOIN отфильтровывал результаты, не соответствующие второй таблице, используйте внутреннее соединение:
SELECT o.orderNumber, o.customerNumber, d.orderNumber, d.productCode
FROM orders o JOIN
orderdetails d
ON o.orderNumber = d.orderNumber
WHERE o.orderNumber = 10123;
Внешние соединения используются, когда вы хотите включить строки, которые не совпадают.
Фильтрация по orderNumber может быть либо в предложении ON, либо в предложении WHERE — они эквивалентны для внутреннего соединения. Однако я думаю, что более «традиционно» размещать такую фильтрацию на одной таблице в предложении WHERE.
есть несколько типов соединений... вам нужно понять их использование, прежде чем использовать их...