Joins vs Присоединиться как запросы – они эквивалентны?

Я ожидаю, что этот вопрос задан несколько раз, с разными завихрениями. Я хочу попытаться получить общее и всестороннее понимание этой темы. (относится ли оно к программированию SO? ..)

Допустим, у меня есть стол для sports и стол для matches . matches , среди других полей есть столбец sport_id , и это соотношение 1: много.

Допустим, я хочу перечислить виды спорта, которые имеют матчи в день X. Я мог бы сделать это тремя способами, о которых я могу думать.

Вложенные запросы – легко рассуждать?

 SELECT * FROM sports WHERE id IN (SELECT sport_id FROM matches WHERE <DATE CHECK>) 

От / где – легко писать?

 SELECT sports.* FROM sports, matches WHERE sports.id = matches.sport_id AND <DATE CHECK> 

Объединяется – я не слишком хорошо знаком, поэтому прошу прощения

 SELECT * FROM sports JOIN matches ON sports.id = matches.sport_id WHERE <DATE CHECK> 

Могут быть другие методы, основанные на вариациях Присоединения, которые могут быть лучше всего подходят здесь, возможно, внутри.

Я хочу знать, как я могу сравнить эти 3 на основе

  1. Эквивалентный ответ (возвращены те же строки?)
  2. Производительность по БД
  3. Все ли они 1 запрос / сетевой вызов или?
  4. Являются ли какие-либо из этих ответов зависимыми от двигателя db?
  5. Как я могу выбрать среди них?
  6. Является синтаксическим сахаром # 2 для № 3? это №1? Или они оптимизированы для # 3 в некоторых / во всех случаях?

    Вторая и третья формы полностью эквивалентны (за исключением дополнительной запятой в третьей версии). FROM sports, matchesнеявное соединение, FROM sports JOIN matches – это явное соединение. Неявные объединения – это более ранняя форма, явные объединения более современные и обычно предпочитаемые экспертами базы данных.

    Версия с WHERE IN почти одинакова, но есть некоторые отличия. Во-первых, SELECT * будет возвращать столбцы из обеих таблиц в объединении, но будет возвращать столбцы только из sports в запросе WHERE IN . Во-вторых, если строка в sports матчах соответствует нескольким строкам в matches , объединения возвращают строку для каждой пары совпадений (она выполняет кросс-произведение), тогда как WHERE IN просто вернет строку из sports один раз независимо от того, сколько совпадений есть ,

    Различия в производительности зависят от реализации. Не должно быть никакой разницы между явным и неявным соединением, это просто синтаксический сахар. Однако базы данных не всегда оптимизируют запросы WHERE IN одинаково. Например, когда я использовал EXPLAIN с MySQL, запросы WHERE IN часто выполняют полное сканирование по внешней таблице, сопоставляя столбец с индексом таблицы в подзапросе, хотя подзапрос может возвращать только небольшое количество строк , Я думаю, что некоторые люди сказали мне, что последние версии MySQL лучше в этом.

    Все они будут всего лишь одним сетевым звонком. Все запросы – это всего лишь один вызов сервера базы данных.

    Кстати, есть еще одна форма, которую вы не указали, используя WHERE EXISTS с коррелированным подзапросом.

     SELECT * FROM sports s WHERE EXISTS (SELECT 1 FROM matches m WHERE s.id = m.sport_id AND <DATE CHECK>) 

    Различия в производительности между этим и JOIN снова будут зависимыми от реализации.

    Вот что я думаю о ваших вопросах

    1. Эквивалентный ответ (возвращены те же строки?)

    • для первого QUERY, где вы пользовались IN Oprator, мой ответ NO (вы получаете одинаковое количество строк, но только столбцы из настольных sports ), а второй и третий – почти такие же

    2.Перформация по БД

    Сначала В oprator медленнее, затем присоединяется beacause. IN оценивается (и выбор из b повторного запуска) для каждой строки в a, тогда как JOIN оптимизирован для использования индексов и других опрятных трюков для подкачки …

    Синтаксис ANSI JOIN

     SELECT fname, lname, department FROM names INNER JOIN departments ON names.employeeid = departments.employeeid 

    Бывший синтаксис Microsoft JOIN

     SELECT fname, lname, department FROM names, departments WHERE names.employeeid = departments.employeeid 

    Если правильно написано, любой формат даст одинаковые результаты. Но это большой, если. Более старый синтаксис Microsoft присоединяется к ошибкам, потому что синтаксис немного менее очевиден. С другой стороны, синтаксис ANSI очень явный, и есть мало шансов, что вы допустили ошибку.

    3. Все ли они 1 запрос / сетевой вызов или?

    -Trial 1 результат для IN

    -Trial 2 для Microsoft JOIN ,

    -Trial 3 результат для ANSI JOIN введите описание изображения здесь 4. Какой из этих ответов зависит от двигателя db?

    (Извините, у меня нет ответа на этот вопрос)

    5. Как я могу выбрать среди них?

    Я предлагаю вам использовать shuold ANSI JOIN

    Синтаксический сахар 6.Is # 2 для № 3? это №1? Или они оптимизированы для # 3 в некоторых / во всех случаях?

    -Я думаю, что НЕТ, как я уже упоминал выше. # 3 syntex больше тесто в соответствии с моим прошлым опытом. Я столкнулся с медленным выполнением запроса из программы ERP. Просмотрев код, в котором использовался синтаксис Microsoft JOIN, я заметил, что вместо создания LEFT JOIN разработчик случайно создал CROSS JOIN. В этом конкретном примере менее 10 000 строк должны были быть результатом LEFT JOIN, но поскольку использовался CROSS JOIN, вместо этого было возвращено более 11 миллионов строк. Затем разработчик использовал SELECT DISTINCT, чтобы избавиться от всех ненужных строк, созданных CROSS JOIN. Как вы можете догадаться, это сделало очень длинный запрос. Я уведомил об этом службу поддержки поставщика, и они исправили свой код.

    Мораль этой истории заключается в том, что вы, вероятно, должны использовать синтаксис ANSI, а не старый синтаксис Microsoft. Помимо уменьшения вероятности совершения глупых ошибок, этот код более переносим между базой данных, и, в конце концов, я полагаю, что Microsoft в конечном итоге перестанет поддерживать старый формат, сделав синтаксис ANSI единственным вариантом