Mysql join примеры

Разберем пример. Имеем две таблицы: пользователи и отделы.

Запрос вернет объединенные данные, которые пересекаются по условию, указанному в INNER JOIN ON <..>.
В нашем случае условие <таблица_пользователей>.<идентификатор_отдела> должен совпадать с <таблица_отделов>.<идентификатор>

В результате отсутствуют:

— пользователь Александр (отдел 6 — не существует)
— отдел Финансы (нет пользователей)

рис. Inner join

Внутреннее объединение INNER JOIN (синоним JOIN, ключевое слово INNER можно опустить).

Выбираются только совпадающие данные из объединяемых таблиц.

Чтобы получить данные, которые подходят по условию частично, необходимо использовать

внешнее объединение — OUTER JOIN.

Такое объединение вернет данные из обеих таблиц (совпадающие по условию объединения) ПЛЮС дополнит выборку оставшимися данными из внешней таблицы, которые по условию не подходят, заполнив недостающие данные значением NULL.

рис. Left join

Существует два типа внешнего объединения OUTER JOIN — LEFT OUTER JOIN и RIGHT OUTER JOIN.

Работают они одинаково, разница заключается в том что LEFT — указывает что "внешней" таблицей будет находящаяся слева (в нашем примере это таблица users).
Ключевое слово OUTER можно опустить. Запись LEFT JOIN идентична LEFT OUTER JOIN.

Получаем полный список пользователей и сопоставленные департаменты.

Добавив условие

в выборке останется только 3#Александр, так как у него не назначен департамент.

рис. Left outer join с фильтрацией по полю

RIGHT OUTER JOIN вернет полный список департаментов (правая таблица) и сопоставленных пользователей.

Дополнительно можно отфильтровать данные, проверяя их на NULL.

В нашем примере указав WHERE u.id IS null, мы выберем департаменты, в которых не числятся пользователи. (3#Финансы)

Все примеры вы можете протестировать здесь:

SQLFiddle

Cross/Full Join

FULL JOIN возвращает `объединение` объединений LEFT и RIGHT таблиц, комбинируя результат двух запросов.

CROSS JOIN возвращает перекрестное (декартово) объединение двух таблиц. Результатом будет выборка всех записей первой таблицы объединенная с каждой строкой второй таблицы. Важным моментом является то, что для кросса не нужно указывать условие объединения.

Дублирование строк при использовании JOIN

При использовании объединения новички часто забывают что результирующая выборка может содержать дублирующиеся данные!
Если вам нужна одна запись, делайте объединение с подзапросом

Self Join

Выборка из одной и той же таблицы для нескольких условий.

Рассмотрим задачку от яндекса:

Есть таблица товаров.

Она содержит следующие значения.

Напишите запрос, выбирающий уникальные пары `id` товаров с одинаковыми `name`, например:

(1,2), (4,1), (2,4), (6,3)…

При решении задачи необходимо учесть, что пары (x,y) и (y,x) — одинаковы.

Решение:

Объединяем таблицы ya_goods по одинаковому полю `name`, группируем по уникальным idентификаторам и получаем результат.

(1,2)(1,4)(2,4)(3,6)

Множественное объединение multi join

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

Пример: набор вариантов (вес, объем) товаров.
Продукты в таблице products, Варианты — таблица product_options, Значения вариантов — таблица product2options
Необходимо: фильтровать продукты по дате, и имеющимся вариантам

Тестовые данные

Пример: выбрать товары,
добавленные после 17/01/2009 в следующих вариантах:
— вес=310, объем=300
— вес=35, объем=15
— вес=45, объем=25
— вес=200, объем=250

Просто перечислить условия вариантов в подзапросе/джоине через OR/AND не сработает,
необходимо осуществить объединение таблиц вариантов равное количеству этих самых вариантов (у нас — 2: объем и вес)

Результ выборки:

Этот пример на SQLFiddle

UPDATE и JOIN

Объединение можно использовать совместно с UPDATE.
Например, имеем таблицу houses (id, title, area). Нужно выбрать title, если в нем встречается `число м2`, заменить поле area, если оно меньше. Т.к. в mysql отстутсутствует поддержка регулярных выражений, нужно немного поколдовать с locate и substr.
В подзапросе выбираем интересующие нас данные, и в финальной стадии осуществляем обновление данных подходящий по критерию (p5 > area).

DELETE и JOIN

Рассмотрим пример с удалением дубликатов. Есть таблица tableWithDups (id, email). Нужно удалить строки с одинаковыми email:

Последние два примера не совместимы с ANSI SQL, но работают в mySQL.

За бортом статьи остались смежные объединениям (а также специфичные для определенных базданных темы):
SELF JOIN, FULL OUTER JOIN, CROSS JOIN (CROSS [OUTER] APPLY), операции над множествами UNION [ALL], INTERSECT, EXCEPT и т.д.

Информация по теме:
http://www.gplivna.eu/papers/sql_join_types.htm
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

@tags: sql, mysql, sql server, oracle, sqlite, postgresql

www.skillz.ru

6.4.1.1 Синтаксис оператора JOIN

MySQL поддерживает следующий синтаксис оператора JOIN при использовании в командах SELECT:

 table_reference, table_reference table_reference [CROSS] JOIN table_reference table_reference INNER JOIN table_reference join_condition table_reference STRAIGHT_JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference join_condition table_reference LEFT [OUTER] JOIN table_reference table_reference NATURAL [LEFT [OUTER]] JOIN table_reference { oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr } table_reference RIGHT [OUTER] JOIN table_reference join_condition table_reference RIGHT [OUTER] JOIN table_reference table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference 

где table_reference определено, как:

 table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)] 

и join_condition определено, как:

 ON conditional_expr | USING (column_list) 

Никогда не следует указывать в части ON

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

Необходимо учитывать, что в версиях до 3.23.17 оператор INNER JOIN не принимает параметр join_condition!

Наличие последней из приведенных выше конструкций выражения LEFT OUTER JOIN обусловлено только требованиями совместимости с ODBC:

• Вместо ссылки на таблицу может использоваться псевдоним, который присваивается при помощи выражений tbl_name AS alias_name или tbl_name alias_name:

   mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2  WHERE t1.name = t2.name; 

• Условный оператор ON представляет собой условие в любой форме из числа тех, которые можно использовать в выражении WHERE.
• Если запись для правой таблицы в частях ON или USING в LEFT JOIN не найдена, то для данной таблицы используется строка, в которой все столбцы установлены в NULL. Эту возможность можно применять для нахождения результатов в таблице, не имеющей эквивалента в другой таблице:

   mysql> SELECT table1.* FROM table1  LEFT JOIN table2 ON table1.id=table2.id  WHERE table2.id IS NULL; 

  Этот пример находит все строки в таблице table1 с величиной id, которая не присутствует в таблице table2 (т.е. все строки в table1, для которых нет соответствующих строк в table2). Конечно, это предполагает, что table2.id объявлен как NOT NULL. See section 5.2.6 Как MySQL оптимизирует LEFT JOIN и RIGHT JOIN.

  
  
• USING (column_list) служит для указания списка столбцов, которые должны существовать в обеих таблицах. Такое выражение USING, как:

   A LEFT JOIN B USING (C1,C2,C3,...) 

  семантически идентично выражению ON, например:

   A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,... 

• Выражение NATURAL [LEFT] JOIN для двух таблиц определяется так, чтобы оно являлось семантическим эквивалентом INNER JOIN или LEFT JOIN с выражением USING, в котором указаны все столбцы, имеющиеся в обеих таблицах.
• INNER JOIN и , (запятая) являются семантическими эквивалентами. Оба осуществляют полное объединение используемых таблиц. Способ связывания таблиц обычно задается в условии WHERE.
• RIGHT JOIN работает аналогично LEFT JOIN. Для сохранения переносимости кода между различными базами данных рекомендуется вместо RIGHT JOIN использовать LEFT JOIN.
  
  
• STRAIGHT_JOIN идентично JOIN, за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех (немногих) случаев, когда оптимизатор объединения располагает таблицы в неправильном порядке.
• Начиная с версии MySQL 3.23.12, можно давать MySQL указания о том, какой индекс должен использоваться при извлечении информации из таблицы. Эта возможность полезна, если оператор EXPLAIN (выводящий информацию о структуре и порядке выполнения запроса SELECT), показывает, что MySQL использует ошибочный индекс. Задавая значение индекса в USE INDEX (key_list), можно заставить MySQL применять для поиска записи только один из указанных индексов. Альтернативное выражение IGNORE INDEX (key_list) запрещает использование в MySQL данного конкретного индекса. Выражения USE/IGNORE KEY являются синонимами для USE/IGNORE INDEX.

Несколько примеров:

 mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id  LEFT JOIN table3 ON table2.id=table3.id; mysql> SELECT * FROM table1 USE INDEX (key1,key2)  WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 IGNORE INDEX (key3)  WHERE key1=1 AND key2=2 AND key3=3; 

See section 5.2.6 Как MySQL оптимизирует LEFT JOIN

и RIGHT JOIN.

www.mysql.ru

INNER JOIN

возвращает пересечение двух множеств

SELECT t1.name, t2.city FROM Table1 t1 INNER JOIN Table2 t2 ON t1.key2 = t2.key2;

Отражением INNER JOIN является OUTER JOIN. Нам предоставлено три типа OUTER JOIN – FULL, LEFT и RIGHT. Слово OUTER писать не обязательно.

FULL JOIN

Объединяет два множества

FULL JOIN вернет ВСЕ записи из таблиц table и table2, без повторяющихся данных. Где данных нет, будет подставленно NULL.

LEFT JOIN

Возвращает данные из левой таблицы, а также данные из правой, которые пересекаются с левой.

Если данных из правой таблицы будет не хватать, то подставится NULL значение.

RIGHT JOIN – как вы наверное поняли, вернет все значения из правой таблицы и пересекающиеся данные из левой.

Исключения

Если нам понадобятся данные из первой таблицы для которых нет данных в правой, то нам просто надо добавить условие WHERE

SELECT t1.name, t2.city FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.key2 = t2.key2 WHERE t2.key2 IS NULL;

Множественные JOIN

С помощью JOIN можно соединить не только 2 таблицы, а сколько надо. Если вам надо соединить 2 таблицы, то потребуется 2 команды JOIN. Но не стоит впадать в крайность и объединять кучу таблиц, все это ощутимо скажется на производительности, поэтому иногда лучше выполнить несколько подзапросов. Пример соединения трех таблиц:

SELECT t1.Name,   t2.City,   t3.Profession  FROM Table1 t1   INNER JOIN Table2 t2 ON t1.key2 = t2.key2   INNER JOIN Table3 t3 ON t1.key3 = t3.key3;

Что же, надеюсь перечисленные простые примеры JOIN помогли вам разобраться с этим оператором.

labdes.ru

• Вместо ссылки на таблицу может использоваться псевдоним, который присваивается при помощи выражений tbl_name AS alias_name или tbl_name alias_name:

  mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2  WHERE t1.name = t2.name; 




• Условный оператор ON представляет собой условие в любой форме из числа тех, которые можно использовать в выражении WHERE.

• Если запись для правой таблицы в частях ON или USING в LEFT JOIN не найдена, то для данной таблицы используется строка, в которой все столбцы установлены в NULL. Эту возможность можно применять для нахождения результатов в таблице, не имеющей эквивалента в другой таблице:

  mysql> SELECT table1.* FROM table1  LEFT JOIN table2 ON table1.id=table2.id  WHERE table2.id IS NULL; 

  Этот пример находит все строки в таблице table1 с величиной id, которая не присутствует в таблице table2 (т.е. все строки в table1, для которых нет соответствующих строк в table2). Конечно, это предполагает, что table2.id объявлен как NOT NULL. See Раздел 5.2.6, «Как MySQL оптимизирует LEFT JOIN Рё RIGHT JOINВ».

• USING (column_list) служит для указания списка столбцов, которые должны существовать в обеих таблицах. Такое выражение USING, как:

  A LEFT JOIN B USING (C1,C2,C3,...) 

  семантически идентично выражению ON, например:

  A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,... 

• Выражение NATURAL [LEFT] JOIN для двух таблиц определяется так, чтобы оно являлось семантическим эквивалентом INNER JOIN или LEFT JOIN с выражением USING, в котором указаны все столбцы, имеющиеся в обеих таблицах.

• INNER JOIN и , (запятая) являются семантическими эквивалентами. Оба осуществляют полное объединение используемых таблиц. Способ связывания таблиц обычно задается в условии WHERE.

• RIGHT JOIN работает аналогично LEFT JOIN. Для сохранения переносимости кода между различными базами данных рекомендуется вместо RIGHT JOIN использовать LEFT JOIN.

• STRAIGHT_JOIN идентично JOIN, за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех (немногих) случаев, когда оптимизатор объединения располагает таблицы в неправильном порядке.

• Начиная с версии MySQL 3.23.12, можно давать MySQL указания о том, какой индекс должен использоваться при извлечении информации из таблицы. Эта возможность полезна, если оператор EXPLAIN показывает, что MySQL из всех возможных индексов использует ошибочный. Задавая значение индекса в USE INDEX (key_list), можно заставить MySQL применять для поиска записи только один из возможных индексов. Альтернативное выражение IGNORE INDEX (key_list) запрещает использование в MySQL данного конкретного индекса. Выражения USE/IGNORE KEY являются синонимами для USE/IGNORE INDEX.

  В MySQL 4.0.9 можно также указывать FORCE INDEX. Это работает также, как и USE INDEX (key_list) но в дополнение дает понять серверу что полное сканирование таблицы будет ОЧЕНЬ дорогостоящей операцией. Другими словами, в этом случае сканирование таблицы будет использовано только тогда, когда не будет найдено другого способа использовать один из данных индексов для поиска записей в таблице.

phpclub.ru

Понятие слова Join

Языки разработки баз данных, неважно, что именно это за язык, за основу берут стандартные слова из англоязычных словарей (именно поэтому при условии, что вы знаете английский язык, вам буде намного проще работать с таблицами). Для реализации подключения таблиц в выборку взято такое же слово — Join. В языке программирования баз данных Используется My SQL. Перевод этого служебного слова в точности такой же, как и в самом языке — «объединение».

Интерпретация конструкции MySQL – Join, причем любой из них, будет точно такой же. Если расшифровать назначение конструкции, а именно схему ее работы, то получим следующие значение: конструкции позволят собрать нужные поля из разных таблиц или вложенных запросов в одну выборку.

Виды конструкций для объединения

Если программисту необходимо собрать выборку из нескольких таблиц и он знает, какие ключевые поля в них и какие именно данные необходимы для отчета, то можно воспользоваться одной из основных конструкций объединения, для достижения желаемого результата. Основных конструкций (для объединения таблиц) четыре:

1. Inner join.
2. Cross Join.
3. Left join.
4. Right Join.

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

Создание и заполнение таблиц для дальнейшего использования

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

В первой таблице будут описаны некоторые предметы, с которыми человек постоянно встречается на протяжении своей жизни.

Во второй таблице опишем некоторые свойства объектов из первой таблицы, чтобы можно было в дальнейшем с ними работать.

В целом двух таблиц будет достаточно, чтобы на примере показать их работу. Вот теперь можно приступать к практическому рассмотрению наших конструкций.

Использование Inner Join

При использовании конструкции MySQL – Join Ineer стоит учитывать некоторые ее особенности. Данная конструкция позволит выбрать из обеих таблиц только те записи, которые есть и в первой и во второй таблице. Как это работает? В первой таблице у нас есть главный ключ – ID, который указывает на порядковый номер записей в таблице.

При создании второй таблицы, этот же ключ используется как порядковый номер, пример можно посмотреть на рисунках. При выборе данных оператор Select определит в результате только те записи, порядковые номера которых совпадают — значит, они есть и в первой и во второй таблице.

При использовании конструкции нужно понимать, какие именно данные необходимо получить. Самая распространенная ошибка, особенно у начинающего программиста баз данных, это нерациональное и неправильное использование конструкции Inner Join. Как пример, MySQL Inner Join, можно рассмотреть скрипт, который вернет нам из ранее описанных и заполненных таблиц информацию о предметах и их свойствах. Но и здесь может быть несколько способов использования конструкции. В этом плане My SQL очень гибкий язык. Итак, можно рассмотреть примеры использования MySQL Inner Join.

Объединение таблиц без указания, каких-либо параметров. В этом случае мы получим результат такого плана:

Если укажем через служебное слово Using, что обязательно нужно учитывать главные ключи записей в таблицах, то результат выборки кардинально изменится. В этом случае мы получим выборку, которая вернет только те строки, которые имеют одинаковые главные ключи.

Возможен еще и третий вариант использования конструкции, когда в запросе через слово «on» указываются поля, по которым должно происходить объединение таблиц. В таком случае выборка вернет следующие данные:

Особенности использования Left Join

Если рассматривать еще один способ объединения таблиц с помощью конструкции MySQL – Join, можно заметить разницу в данных, которые выводятся. Таким механизмом является конструкция Left.

Использование конструкции Left Join MySQL имеет некоторые особенности и, как и Inner, требует четкого понимания результата, который необходимо получить.

В данном случае сначала будут выбраны все записи из первой таблицы, а в дальнейшем к ним будут присоединены записи из второй таблицы свойств. При этом, если в первой таблице есть запись, например, «табурет», а во второй таблице нет ни одного свойства для нее, то оператор Left выведет напротив этой записи значение null, что говорит программисту о том, что признаков по этому виду предмета нет.

Использование этой конструкции позволят определять, по каким полям или, например, товарам в магазине не выставлена цена, гарантийный срок и так далее.

Пример использования Left

Для рассмотрения на практике оператора конструкции Left Join MySQL используем ранее описанные таблицы. Скажем, необходимо выбрать весь список товаров, которые есть в магазине, и проверить по каким из них не проставлены признаки или свойства. В таком случае выборка выведет на экран все товары, а по тем, у которых нет свойства, будут проставлены пустые значения.

Использование Where в конструкции Join

Как параметр, объединение может включать не только указания полей, по которым необходимо произвести подключение таблиц, но может и включать оператор условия Where.

Для примера рассмотрим скрипт, который должен вернуть нам только те записи, по которым не проставлен признак. В таком случае в конструкцию Join необходимо добавить оператор условия и указать, что именно нужно вернуть как результат.

При использовании в MySQL Join – Where нужно четко понимать, что будут показаны только те записи, к которым относится указанное условие, и выборка тогда будет выглядеть следующим образом:

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

Использование Join для изменения данных в таблицах

Конструкция Join, по сути, универсальна. Она позволяет не только делать разнообразные выборки, но и подключать в запросы от одной до нескольких таблиц, вводить дополнительные условия в выборку. Конструкцию можно также использовать и для других операций с данными. Так, Join можно использовать для изменения данных в таблице. Вернее сказать, для уточнения условий в таблице или же в тех случаях, если нужно обновить данные в нескольких таблицах по одним и тем же условиям.

Для примера рассмотрим такую задачу. Дано три таблицы, в которых имеются некоторые данные. Нужно провести изменение данных в обеих таблицах, используя один запрос. Как раз для решения такого рода заданий можно применять в команде Update конструкцию Join. Сам тип конструкции Join зависит, так же, как и в случае с выборкой данных, от результата, который хочет получить программист.

Рассмотрим самый простой пример. Нужно обновить одним запросом данные по одним и тем же условиям. Такого рода запросы строятся для оптимизации работы с базой данных. Зачем писать разные запросы для каждой из таблиц, если можно провести все манипуляции с данными одним запросом? Пример MySQL Update Join в нашем случае будет таким:

Построение сложных запросов

Довольно часто при работе с базой данных необходимо строить запросы не только с объединением нескольких таблиц, но и с использованием подзапросов. Такие задачи довольно сложны для понимания начинающего программиста баз данных. Сложность состоит в том, что приходится продумывать каждый шаг, определять, какие данные из какой таблицы или запроса необходимо получить и как в дальнейшем с ними нужно будет работать.

Для более конкретного понимания можно рассмотреть (в MySQL Join) примеры сложных запросов. Если вы новичок и только начинаете работать с базами данных, то такой тренинг пойдет лишь на пользу. Идеальным вариантом, будут MySQL Left Join примеры.

Данный запрос вернет нам 58 записей о договорах продажи, по которым заполнен или существует баланс денежных средств на выбранную дату. В данном случае это текущая дата. Также в выборку добавлено условие, что в названии договора должны быть символы — «123». Выводимая на экран информация (данные), будет иметь сортировку – упорядоченность по номеру договора.

Следующий пример выведет на экран данные обо всех платежах, в которых будет указан номер договора.

Использование подзапросов

Как уже говорилось ранее, при работе с базами данных можно объединить не только таблицы, но и таблицу с запросом. Такая конструкция используется в основном для ускорения работы запроса и его оптимизации.

К примеру, если необходимо из таблицы, которая имеет несколько сот полей и, скажем, тысячу записей, выбрать всего два поля, то стоит использовать запрос, который вернет только необходимые поля, и объединить его с основной выборкой данных. Как пример MySQL Join Select можно рассмотреть запрос такого типа:

Это не все способы использования стандартных конструкций MySQL, а только стандартные. Как использовать конструкцию Join и в каких ее видах, решает сам программист, но стоит помнить и учитывать то, какой результат надо получить при выполнении запроса.

fb.ru

Поводом для написания данной статьи послужили некоторые дебаты в одной из групп linkedin, связанной с MySQL, а также общение с коллегами и хабролюдьми 🙂

В данной статье хотел написать что такое вообще JOINы в MySQL и как можно оптимизировать запросы с ними.

Что такое JOINы в MySQL

В MySQL термин JOIN используется гораздо шире, чем можно было бы предположить. Здесь JOINом может называться не только запрос объединяющий результаты из нескольких таблиц, но и запрос к одной таблице, например, SELECT по одной таблице — это тоже джоин.

Все потому, что алгоритм выполнения джоинов в MySQL реализован с использованием вложенных циклов. Т.е. каждый последующий JOIN это дополнительный вложенный цикл. Чтобы выполнить запрос и вернуть все записи удовлетворяющие условию MySQL выполняет цикл и пробегает по записям первой таблицы параллельно проверяя соответствия условиям описанных в теле запроса, когда находятся записи, удовлетворяющие условиям — во вложенном цикле по второй таблице ищутся записи соответствующие первым и удовлетворяющие условиям проверки и т.д.

Прмер обычного запроса с INNER JOIN

SELECT
  *
FROM
  Table1
INNER JOIN
  Table2 ON P1(Table1,Table2)
INNER JOIN
  Table3 ON P2(Table2,Table3)
WHERE
   P(Table1,Table2,Table3).

* This source code was highlighted with Source Code Highlighter.

где Р — условия склейки таблиц и фильтры в WHERE условии.

Можно представить такой псевдокод выполнения такого запроса.

FOR each row t1 in Table1 {
 IF(P(t1)) {
  FOR each row t2 in Table2 {
   IF(P(t2)) {
    FOR each row t3 in Table3 {
     IF P(t3) {
       t:=t1||t2||t3; OUTPUT t;
     }
    }
   }
  }
 }
}

* This source code was highlighted with Source Code Highlighter.

где конструкция t1||t2||t3 означает конкатенацию столбцов из разных таблиц.

Если в запросе встречаются OUTER JOINs, например, LEFT OUTER JOIN

SELECT
   *
FROM
   Table1
LEFT JOIN
(
   Table2 LEFT JOIN Table3 ON P2(Table2,Table3)
)
   ON P1(Table1,Table2)
WHERE
   P(Table1,Table2,Tabke3)

* This source code was highlighted with Source Code Highlighter.

то алгоритм выполнения этого запроса MySQL будет выглядеть как-то так

FOR each row t1 in T1 {
 BOOL f1:=FALSE;
 FOR each row t2 in T2 such that P1(t1,t2) {
  BOOL f2:=FALSE;
  FOR each row t3 in T3 such that P2(t2,t3) {
   IF P(t1,t2,t3) {
    t:=t1||t2||t3; OUTPUT t;
   }
   f2=TRUE;
   f1=TRUE;
  }
  IF (!f2) {
   IF P(t1,t2,NULL) {
    t:=t1||t2||NULL; OUTPUT t;
   }
   f1=TRUE;
  }
 }
 IF (!f1) {
  IF P(t1,NULL,NULL) {
   t:=t1||NULL||NULL; OUTPUT t;
  }
 }
}

* This source code was highlighted with Source Code Highlighter.

Более подробно почитать об этом можно здесь — dev.mysql.com/doc/refman/5.1/en/nested-joins.html

Итак, как мы видим, JOINы это просто группа вложенных циклов. Так почему же в MySQL и UNION и SELECT и запросы с SUBQUERY тоже джоины?

MySQL оптимизатор старается приводить запросы к тому виду к которому ему удобней обрабатывать и выполнять запросы по стандартной схеме.

С SELECT все понятно — просто цикл без вложенных циклов. Все UNION выполняются как отдельные запросы и результаты складываются во временную таблицу, и потом MySQL работает уже с этой таблицей, т.е. проходясь циклом по записям в ней. С Subquery та же история.

Приводя все к одному шаблону, например, МySQL переписывает все RIGHT JOIN запросы на LEFT JOIN эквиваленты.

Но стратегия выполнения запросов через вложенные циклы накладывает некоторые ограничения, например, в связи с такой схемой MySQL не поддерживает выполнение FULL OUTER JOIN запросов.

Но результат такого запроса можно получить с помощью UNION двух запросов на LEFT JOIN и на RIGHT JOIN
Пример самого запроса можно посмотреть по ссылке на вики.

План выполнения JOIN запросов

В отличии от других СУРБД MySQL не генерирует байткод для выполнения запроса, вместо этого MySQL генерирует список инструкций в древовидной форме, которых придерживается engine выполнения запроса выполняя запрос.
Это дерево имеет следующий вид и имеет название «left-deep tree»

В отличии от сбалансированных деревьев (Bushy plan), которые применяются в других СУБД (например Oracle)

habr.com

Большинство начинающих веб программистов начинает свое изучение MySQL с простейших операторов SELECT, UPDATE и DELETE. Данными операторами вполне можно описать весь необходимый функционал простого сайта, но, как можно догадаться, на этом возможности языка SQL далеко не заканчиваются. В процессе разработки обязательно потребуется объединение данных из нескольких таблиц. И для этих целей существует оператор JOIN. Данный оператор является основным оператором стандарта SQL92 и поддерживается большинством СУБД.

Общий синтаксис JOIN выглядит следующим образом:

Однако, сразу следует отметить, что СУБД MySQL поддерживает только два наиболее популярных выражения: INNER JOIN и LEFT JOIN.

Рассмотрим как работает каждый из операторов, для этого создадим две таблицы: TableA и TableB.

id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja

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

1. INNER JOIN – внутреннее соединение. Объединяет две таблицы, где каждая строка обоих таблиц в точности соответствует условию. Если для строки одной таблицы не найдено соответствия в другой таблице, строка не включается в набор.

SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name

id name id name -- ---- -- ---- 1 Pirate 2 Pirate 3 Ninja 4 Ninja

 

Выбор по первичному ключу и индексу положительно сказывается на скорости выборки.

2. OUTER JOIN – внешнее объединение.

Присоединение таблицы с необязательным присутствием записи в таблице. Также как и в случае с inner join, условие по индексированным полям и первичному ключу ускоряет все виды outer join’ов.

2.1 LEFT OUTER JOIN или LEFT JOIN-левое внешнее объединения. Левосторонние объединения позволяют извлекать данные из левой таблицы, дополняя их по возможности данными из правой таблицы, поля правой таблицы заполняются значениями NULL.

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name

id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null

Если дополнить предыдущий запрос условием на проверку несуществования, то можно получить список записей, которые не имеют пары в таблице TableB:

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null

id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null

2.2 RIGHT OUTER JOIN или RIGHT JOIN — Правостороннее внешнее объединение

Этот вид объединений практически ничем не отличается от левостороннего объединения, за тем исключением, что данные берутся из второй таблицы, которая находится справа от конструкции JOIN, и сравниваются с данными, которые находятся в таблице, указанной перед конструкцией.

2.3 FULL OUTER JOIN – комбинация правого и левого объединений. К левой таблице присоединяются все записи из правой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL и плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL.

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name

id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null 

id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader

 

3. CROSS JOIN – перекрестное объединение (декартово произведение), выводятся все возможные сочетания из обеих таблиц. Для этого типа оператора JOIN условия не указывается.

SELECT * FROM TableA CROSS JOIN TableB

id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Rutabaga 3 Ninja 3 Rutabaga 4 Spaghetti 4 Rutabaga 1 Pirate 1 Pirate 2 Monkey 2 Pirate 3 Ninja 3 Pirate 4 Spaghetti 4 Pirate 1 Pirate 1 Darth Vader 2 Monkey 2 Darth Vader 3 Ninja 3 Darth Vader 4 Spaghetti 4 Darth Vader 1 Pirate 1 Ninja 2 Monkey 2 Ninja 3 Ninja 3 Ninja 4 Spaghetti 4 Ninja

Данная конструкция, по причине своей не нужности, не поддерживается почти ни в одной БД

blog.kogtev.com

В результате обсуждения JOIN’ов здесь я попытался в своем блоге собрать описание всех видов Join в MySQL. Здесь я привожу полученный результат на ваш строгий, но справедливый суд Ниже приведен синтаксис всех возможных форматов записи оператора JOIN при использовании в командах SELECT (см. http://dev.mysql.com/doc/refman/5.0/en/join.html). Все примеры и описания рассмотрены на примере MySQL и вероятнее всего будут отличаться для других типов СУБД!

1. table_reference [INNER] JOIN table_reference
2. table_reference [CROSS] JOIN table_reference
3. table_reference STRAIGHT_JOIN table_reference
4. table_reference [INNER] JOIN table_reference join_condition
5. table_reference [CROSS] JOIN table_reference join_condition
6. table_reference STRAIGHT_JOIN table_reference ON conditional_expr
7. table_reference LEFT [OUTER] JOIN table_reference join_condition
8. table_reference RIGHT [OUTER] JOIN table_reference join_condition
9. table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
10. table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

При описании различных вариантов использования JOIN будут использоваться две тестовые таблицы – sport и music. Первая содержит список людей, посещающих спортивные секции, вторая для людей, посещающих занятия по музыке.

1. table_reference [INNER] JOIN table_reference

Тип соединения "внутреннее". Внутренний тип соединения используется по умолчанию, когда тип явно не задан (то есть, опущено ключевое слово INNER). Если не приведено ни одного условия совпадения, то будет возвращено декартово произведение, в котором каждая строка одной таблицы будет сопоставлена с каждой строкой другой таблицы. То есть будут получены всевозможные сочетания из обеих таблиц. Такое соединение в разделе FROM может быть заменено списком таблиц через запятую.

эквивалентно:

результат:

Покажите мне всевозможные сочетания пар, как если бы все ребята занимались И музыкой И спортом.
В данном случае, например, спортсмен Джек (Jack) ходил бы на занятия музыкой вместе с Майклом (Mike), Сэмом (Sam) и с Гарри (Garry). И аналогично для остальных ребят.

2. table_reference [CROSS] JOIN table_reference

Абсолютно то же самое, что table_reference [INNER] JOIN table_reference.

результат:

3. table_reference STRAIGHT_JOIN table_reference

То же самое, что table_reference [INNER] JOIN table_reference за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех (немногих) случаев, когда оптимизатор соединения таблиц в MySQL располагает таблицы в неправильном порядке. То есть специальный оператор, по сути, заставляет соединять таблицы в том порядке, в котором это указано в SQL-запросе. В некоторых случаях это может позволить оптимизировать скорость выполнения запроса.

результат:

Покажите мне всевозможные сочетания пар, как если бы все ребята занимались И музыкой И спортом И соедините для меня данные именно в том порядке, как я прошу — сначала реестр спортсменов (таблица sport), а рядом реестр музыкантов!
И снова, как и для table_reference [INNER] JOIN table_reference спортсмен Джек (Jack) ходит на занятия музыкой вместе с Майклом (Mike), Сэмом (Sam) и с Гарри (Garry). И аналогично для всех остальных.

4. table_reference [INNER] JOIN table_reference join_condition

INNER JOIN – внутреннее соединение. Объединяет две таблицы, где каждая строка обеих таблиц в точности соответствует условию. Если для строки одной таблицы не найдено соответствия в другой таблице, строка не включается в набор. Тип соединения "внутреннее". Внутренний тип соединения используется по умолчанию, когда тип явно не задан (т.е. если вы напишите просто JOIN).

эквивалентно:

результат:

Покажите мне всех ребят, которые ходят И на занятия музыкой И на занятия спортом.
Из таких ребят оказался только один – Майкл (Mike). Только он присутствует и в таблице music и в таблице sport.

5. table_reference [CROSS] JOIN table_reference join_condition

Абсолютно то же самое, что table_reference [INNER] JOIN table_reference join_condition.

результат:

6. table_reference STRAIGHT_JOIN table_reference ON conditional_expr

То же самое, что table_reference table_reference [INNER] JOIN table_reference join_condition за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех (немногих) случаев, когда оптимизатор соединения таблиц в MySQL располагает таблицы в неправильном порядке. То есть специальный оператор STRAIGHT_JOIN, по сути, заставляет соединять таблицы в том порядке, в котором это указано в SQL-запросе. В некоторых случаях это может позволить оптимизировать скорость выполнения запроса.

результат:

Покажите мне всех ребят, которые ходят И на занятия музыкой И на занятия спортом и соедините для меня данные именно в том порядке, как я прошу — сначала список спортсменов (таблица sport), а рядом список музыкантов!
Снова, как и в примере для INNER JOIN, из таких ребят оказался только один – Майкл (Mike).

7. table_reference LEFT [OUTER] JOIN table_reference join_condition

LEFT (OUTER) — Тип соединения "левое (внешнее)". Левое соединение таблиц А и В включает в себя все строки из левой таблицы А и те строки из правой таблицы В, для которых обнаружено совпадение. Для строк из таблицы А, для которых не найдено соответствия в таблице В, в столбцы, извлекаемые из таблицы В, заносятся значения NULL.

результат:

Покажите мне всех спортсменов, которые ходят И на занятия спортом И на занятия музыкой, а тех, что занимаются только спортом и не занимаются музыкой пометьте в разделе music как NULL.

8. table_reference RIGHT [OUTER] JOIN table_reference join_condition

Тип соединения "правое (внешнее)". Правое соединение таблиц А и В включает в себя все строки из правой таблицы В и те строки из левой таблицы А, для которых обнаружено совпадение. Для строк из таблицы В, для которых не найдено соответствия в таблице А, в столбцы, извлекаемые из таблицы А заносятся значения NULL.

результат:

Покажите мне всех музыкантов, которые ходят И на занятия музыкой И на спортом, а тех, что занимаются только музыкой и не занимаются спортом пометьте в разделе sport как NULL.

9. table_reference NATURAL [LEFT [OUTER]] JOIN table_reference

Естественное соединение производится по всем столбцам таблиц А и В, имеющим одинаковые имена. В результирующую таблицу одинаковые столбцы вставляются только один раз. Выражение NATURAL [LEFT] JOIN для двух таблиц является эквивалентом INNER JOIN или LEFT JOIN с выражением USING, в котором указаны все столбцы, имеющиеся в обеих таблицах.

эквивалентно:

результат:

Дайте мне данные всех колонок из реестра спортсменов (именно спортсменов!), для которых в реестре музыкантов есть колонки с такими же названиями.

10. table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Естественное соединение производится по всем столбцам таблиц А и В, имеющим одинаковые имена. В результатирующую таблицу одинаковые столбцы вставляются только один раз.

результат:

Дайте мне данные всех колонок из реестра музыкантов (именно музыкантов!), для которых в реестре спортсменов есть колонки с такими же названиями.

Полезные ссылки

1. Описание синтаксиса JOIN на dev.mysql.com (английский)
2. Описание синтаксиса JOIN на mysql.ru (русский)
3. Синтаксис соединенных таблиц в руководстве "Введение в системы управления базами данных" на citforum.ru

Отредактированно FiMko (24.03.2012 22:32:43)

sqlinfo.ru